SQL Server Performance

Optimizing count(*)

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jinchuriki, Feb 24, 2008.

  1. jinchuriki New Member

    Hi guys,
    I'm facing a performance issue with select count(*) table_name when performing pagination to my results. My actualy query joins 4 tables with proper PK and FK contrainsts and indices applied.
    select top 100 * from table1
    left outer join table2 on tid=rid
    left outer join table3 on tid=aid
    inner join shipment on sid=sid
    where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
    The query above takes 200 ms to 600 ms to execute.


    select count(*) from table1
    left outer join table2 on tid=rid
    left outer join table3 on tid=aid
    inner join shipment on sid=sid
    where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
    The query above takes 2000ms to 4000 ms to run.
    The total records that fulfill the where clause is approximately 5000 to 6000 records. I checked the execution plan but found that the server is actually utilizing the indices with operations like index scan and index seek.
    Are there any other things that i can do to improve the counting of total records?
    Thanks.
  2. martins New Member

    Hi,
    One or two things to note:
    1. Are the execution plans for the above queries exactly the same? A Difference in execution plans will yield a possible difference in performance.
    2. In your first query, you are only returning the top 100. If you change this to return everything, what is the performance like then? You have to compare apples with apples here, and returning 100 records vs counting all will definitely not have the same execution time.
  3. Adriaan New Member

    The TOP 100 clause makes comparing the response times irrelevant.
    Could you complete the ON clauses with table names here -
    select count(*) from table1
    left outer join table2 on tid=rid
    left outer join table3 on tid=aid
    inner join shipment on sid=sid
    If the outer joins are necessary to link table1 to shipment, and you are filtering on shipment, then you can replace them with inner joins.
    If the outer joins are irrelevant for the logic of the COUNT(*), then they take up a lot of resources unnecessarily, so remove them from the query.
  4. johng New Member

    We have decided to use this method to accomplish what I think you are. We use a CTE to build the entire list, then count that CTE for total rows that satisfy the original query. We CROSS JOIN that to the original result set so in effect that total is in every column - we just carve out the first one to put on the page.
    I'd be interested in any and all comments.
    The following code has NOT been tested - (I just tried to quickly cut your code in)
    WITH CTE_recordList AS
    (
    SELECT ROW_NUMBER() OVER ( Order by datetime DESC ) AS RowNumber,
    * from table1
    left outer join table2 on tid=rid
    left outer join table3 on tid=aid
    inner join shipment on sid=sid
    where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
    )
    , CTE_total AS
    ( SELECT count(*) as totalRows
    from CTE_recordList
    )
    SELECT RL.*, T.*
    FROM CTE_recordList RL
    CROSS JOIN CTE_total T
    where [RowNumber] >= 1
    and [RowNumber] <=100
    ORDER BY RowNumber

  5. patel_mayur New Member

    [quote user="johng"]
    CTE_total AS
    ( SELECT count(*) as totalRows
    from CTE_recordList
    )
    [/quote]
    How about Max(RowNumber) instead count(*) ?
  6. ranjitjain New Member

    Hi Johng,
    You could use OVER() with count(*) to achieve the same, check this
    WITH CTE_recordList AS
    (
    SELECT ROW_NUMBER() OVER ( Order by datetime DESC ) AS RowNumber,
    *,count(*) OVER() as totalRows from table1
    left outer join table2 on tid=rid
    left outer join table3 on tid=aid
    inner join shipment on sid=sid
    where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
    )
    SELECT RL.* FROM CTE_recordList RL
    where [RowNumber] >= 1
    and [RowNumber] <=100
    ORDER BY RowNumber
  7. Madhivanan Moderator

    Also change your where clause to
    where datetime>=convert(datetime,'20070810 00:00:00') and datetime<convert(datetime,'20070921 00:00:00')
    for accuracy if time also matters
  8. johng New Member

    Patel and Ranjitjain,
    Thanks for the suggestions. They both look like they save a some processing - I will test.
    johng
  9. Adriaan New Member

    Check the joins, see if they are really necessary for the query, and if they are, then whether the two outer joins can be replaced with inner joins.

Share This Page