Optimizing count(*)

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

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.