Posted by Roy Harvey (SQL Server MVP) on 10/25/07 15:40

My guess - and I can only guess from the given information - is that
there is an index on dbo.table(column1), and an index on
dbo.table(column2), but no index on dbo.table(column1,column2). In
the first example the optimizer chooses to use one of the indexes,
then has to scan to resolve the other test. In the second version of
the query the optimizer is using both indexes and then matching up the
results.

If this combination of columns is queried often you might consider
changing one of the two indexes to add the other column as the second
column of the index. So you might end up with the two indexes as
(column1), and (column2,column1). You would not need to indexes with
the same first column.

Roy Harvey
Beacon Falls, CT

On Wed, 24 Oct 2007 22:13:11 -0700, DBMonitor
<spamawayau@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?

[Back to original message]