SQL efficiency

[WannaFly]

MSSQL 2000, if it matters.

Which query would be more "efficient"

SELECT plate,MAX([date]) from A where plate='ABC'

or

SELECT top 1 plate, [date] from A where plate='ABC' order by [date] desc


I'd assume the first would be but im not very knowledgeable on the subject, thanks.

edit: doh I just realized they return two different results. uhh..i added the where clauses

 

[kamper]

I'd be inclined to think the first would be better. There you can use an O(n) "sorting" algorithm by simply ignoring any records that have a date smaller than the largest found so far. In the second example it might try to sort everything which I'm guessing would be O(nlog(n)) if you don't have appropriate indexes on the date column and then just take the record off the top. On the other hand, it might realize what's going on and optimize the second query so that they do the same thing anyways.

But that's just me taking a wild stab. I know very little about the implementation of databases. If it were me I'd fill up a sample table with a few thousand records (few hundred thousand maybe?) and run each query a number of times to see if there is a marked difference. Chances are you'll understand the reasons why better in the end if you experiment by yourself.

 

[AntiEverything]

Throw them both into Query Analyzer and view the execution plan. I'd have thought the MAX would be faster, but it looks like the query parser is smart enough to do roughly the same processing for either query.

 

[kamper]

Would you look at that? Apparently microsoft has already solved the problem for you!!

What does the execution plan show you AntiEverything? Does it lay out the algorithmic steps it will take to execute the query? Does it tell you under what circumstances it will use such an algorithm? I assume that placing these statements in a stored procedure would be different than supplying them on the fly.