Explanation wanted.....

[Maxie]

I've got two versions of a nested query that do the same
thing. However, one gives me an answer instantaneously and
the other takes 11 seconds. Could someone please walk me
through in detail what it is that makes the slow one take
so much longer? (I'm trying to learn these nuances so that
I can design better queries in the future.)

FAST QUERY
------------
SELECT thedate, ticker, close
FROM pub_price_history
WHERE thedate in (SELECT max(thedate) FROM pub_price_hist);

SLOW QUERY
-----------
SELECT ticker, thedate, close
FROM pub_price_hist
WHERE thedate in (SELECT DISTINCT TOP 1 thedate from
pub_price_hist ORDER BY thedate DESC);

As you can see, the only difference is how the subquery is
structured.

Thanks in advance.

Maxie

 

[HSalim]

Maxie,
In short, you are using three clauses - distinct, Top and OrderBy, none of
which have to be performed
for the max, where all it has to do is to scan that column (or the index if
one exists) and take the max value as it goes along. one operation.

Try just the subquery, with the various clauses - Top, distinct and orderby
Then try this after adding an index on Thedate (if you have one already, try
removing it)

needless to say, all on a test database...

HS