|
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?
Navigation:
[Reply to this message]
|