ansaurus

Question

Selecting "latest" row (up to a date) from a table (Sql Server 2008)

Answer 1

+1 A:

The query optimizer has a lot of freedom, and it can execute both MAX or TOP 1 in various ways. Exactly what it does depends on the source query, the available indexes, and the statistics for your table, among other things. Tomorrow it might choose a different appreach, as the size of your table or its distribution change.

So I don't think there's one optimal solution. Wait for actual performance issues and optimize them one by one.

Andomar 2009-08-12 09:01:31

Thanks for your response. I appreciate the premature optimisation concern. Part of what I m doing is tuning the performance. The fact that actual performance may vary is why I'm asking here rather than just measuring - i.e. I'm interested in whether there is anything fundamentally different between the approaches that will thwart the query optimiser - or if there is a better approach. Your comments suggest that, in this instance, they should probably be seen as equivalent...?

Phil Nash 2009-08-12 11:50:35

I'm racking my brains trying to figure out what the various ways might be: If date is not indexed, it will scan, if date is indexed but doesn't cover, it will seek with a lookup, if date is indexed and it covers, it will simply seek - What other approaches might it take?

Mike DeFehr 2009-08-12 13:09:30

For example it would scan if it thinks there are many rows with the same date, even though there is an index.

Andomar 2009-08-12 16:24:29

The only component that *might* scan in this scenario is the outer query of the MAX - but this is only if the unique data assumption does not hold or there is something wrong with your statistics (either way you have other problems) - the subquery can seek (if it has an index) regardless of the number of matching rows, and the TOP 1 will always *technically* scan (even with a unique index) but it will always stop after it reads 1 - so, not really, no...

Mike DeFehr 2009-08-12 17:02:50

If Sql Server thinks it will match many rows, it tries to avoid the lookup associated with an index seek. That is often the right approach: a table scan for 100 hits is often faster than 100 index seeks + 100 table lookups. But sometimes it's wrong, like one case I saw, where someone used an index hint to reduce execution time from 2 hours to 2 seconds. Never say never :)

Andomar 2009-08-12 20:47:09

That is absolutely correct, however, it has very little to do with this question because with the TOP 1 technique,SQL server knows it's only getting one row, so it goes to the end of the index, grabs the first row it finds, does its lookup and calls it a day regardless of the uniqueness of date - the possibility of a scan is only introduced with the MAX technique, thus making it suboptimal. You are zeroing in on the notion that *if* the statictics are way out of date *and* the OP's assumption is invalid then there could be alternate plans - doesn't answer the question

Mike DeFehr 2009-08-13 12:51:16

My answer was a general "don't optimize prematurely". I apologize for zeroing in in response to your comments.

Andomar 2009-08-13 14:19:37

Answer 2

+2 A:

Your results may vary depending on table design, but generally speaking, the TOP 1 / Order by technique is 2 times better when there is no index on date because SQL server has to do a scan for each query - first to find the max date, then to look up the rest of the values based on it. When there is an index on date (whether it covers the query or not) the plan is the same.

The most important thing to consider here is indexing. If this query is to be executed a lot, you'll want to make sure you index the date field.

Both from the standpoint of the TOP 1 being optimal in certain circumstances and the issue you already touched on: the MAX could return more than 1 row (don't assume it won't someday, by the way, unless there is a unique index on date), I definitely prefer the TOP 1 technique - it is the technique I use for all such queries.

Mike DeFehr 2009-08-12 12:58:31

horribly out of date statistics could theoretically cause the outer query in the MAX technique to scan if the optimizer somehow gets fooled into thinking it is not selective enough for a seek, but I doubt it - either way, another reason to use TOP 1 - it wouldn't fall into that trap

Mike DeFehr 2009-08-12 13:17:54

So what you're saying is that TOP 1 gives all the advantages, but no disadvantages - wheras MAX might give some disadvantages in some circumstances - but with other things as they should be there's not much in it? That makes a lot of sense. Thanks for the comments

Phil Nash 2009-08-12 14:56:27

Correct - that's what I'm saying - however - far more important than how you write the query is what indexes you have - this is definitely true here and is very often true elsewhere. Another thing I'll stress primarily for the benefit of others, is that the two queries are not logically equivalent - they will only yield the same results if there is a unique index on date or the maximum date happens to be a unique value

Mike DeFehr 2009-08-12 15:28:19

In my case I'm not currently in a position to influence the indexing, but I believe in most of the instances I'm looking at there is an index on date.

Phil Nash 2009-08-13 09:14:02

Alright, then you're doing what you can. Anything else to answer your question?

Mike DeFehr 2009-08-13 15:28:13

ansaurus

tags:

views:

answers:

Selecting "latest" row (up to a date) from a table (Sql Server 2008)

related questions