SQL Server Performance

Strategy Ideas for Paging and Searching by name

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by cspinelive, Jun 9, 2009.

  1. cspinelive New Member

    I'm looking for general strategies to get the very best possible performance when you need to return only a certain "page's" worth of data AND for searching by name. For example, I want the very best way to possible for a user to search the list by name and only return back the 10 names for the "page" of the result set that they are on AND return the total result count so that I can calculate the total number of pages in the entire result set. I'm open to all ideas. For example, how about creating a supporting table with only the id and first and last names. Maybe I could search through that table instead so as not to affect the performance on the main table that the rest of the site is using. I've got around 2 million rows worth of user data.
    We've added indexes and done all the tuning that we know how, but I still get timouts occasionally. Let's assume that this table isn't being updated at all while the query is running. Would a normal select statement cause a lock that would prevent others from running the same select statement?
  2. satya Moderator

    Welcome to the forums.
    SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications. ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.
  3. Sandy New Member

    I can suggest you as the same as Satya specified here. Paging is the concept where you can get the data in efficient and speedy manner. Just you need to pass 2 extra parameters (1st - Number of Records, 2nd - Page Number) to your application with the same procedure name.
    [quote user="cspinelive"]We've added indexes and done all the tuning that we know how, but I still get timouts occasionally. Let's assume that this table isn't being updated at all while the query is running. Would a normal select statement cause a lock that would prevent others from running the same select statement? [/quote]
    Yes, you can use NOLOCKS which is not a Best Practice of using Lock hints in the query which may leads to Lock escalation in future. But It will give you better performance.
    Thanks,
    Sandy.
  4. Zippy New Member

    Paging should reduce the amount of data your infrastructure has to handles and you front end should have less work to do it does not have to do the paging be careful when asking SQL to page your results. Depending on the sort order and the columns being returned SQL can sometimes scan and build the entire result set in order to work out which rows to return. Use Profiler and SET STATISTICS IO ON with your queries before and after including the paging logic to make sure SQL is actually doing less work.

Share This Page