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

On Oct 25, 3:13 pm, DBMonitor <spamawa...@yahoo.com.au> wrote:
> 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?

BTW: Cubes are not an option in this senario.

[Back to original message]