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.
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.
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.
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
[quote user="johng"] CTE_total AS ( SELECT count(*) as totalRows from CTE_recordList ) [/quote] How about Max(RowNumber) instead count(*) ?
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
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
Patel and Ranjitjain, Thanks for the suggestions. They both look like they save a some processing - I will test. johng
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.