SQL Server Performance

Help with optimizing query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by hrobjartz, May 29, 2006.

  1. hrobjartz New Member

    Hi, i was wondering whether some SQL expert would be willing to take a look at this query for me. It is a search query that is frequently used on our site so it needs to perform great.

    The query handles PAGING on the website, so everytime i need to get the total count of rows based on the search parameters, and then i have to select the rows in the current PageIndex (based on specified pageSize).

    To get the total rows in the first query select them into a variable, like this:

    @totalPropertiesCount = COUNT(id)

    First of, i have a problem with getting the total COUNT of rows when using the following (in query number 2):

    SELECT TOP 200
    ROW_NUMBER() OVER (ORDER BY id ASC) AS row...

    So for the time being i need select the content TWICE, once to get the total count, and then again to get rows based on the current pageIndex (so obviously the query needs some tuning).

    Let me know if you want me to paste the whole query into the thread.

    Thanks and regards,
    Bjorn.
  2. gaurav_bindlish New Member

  3. Madhivanan Moderator

  4. hrobjartz New Member

    Thanks a lot for the quick reply guys!
    Those links were a great help!

    Bjorn.

Share This Page