Posted by DBMonitor on 10/25/07 05:13

I have a table on a database that contains 18million records. I need
to design a system that queries this table to produce fast counts.

I have got counts for multiple criteria down to only a few seconds.
Most take under a second however I have a few queries that seam to
take longer which I am working on reducing the time.

I have found some strange behavour in the way SQL Server works.

Take the following two queries which produce exactly the same result:

---------------------------------
select count(*)
from dbo.table
where column1='value1'
and column2='value2'
--------------------------------
and

--------------------------------
select count(*)
from (select id from table where column1 = 'value1') as value1
join (select id from table where column2 = 'value2') as value2
on value1.id = value2.id
---------------------------------

I would assume that the first query should run faster then the second
query. When I look at the query plans, they are almost identical cost
wise. The first takes about 53% of the cost and the second takes 47%.

Yet, the first query takes about 25 seconds to run and the second
takes only 5 seconds.

Does anyone know of a reason why there would be such a difference in
query speed?

[Back to original message]