TOP 1 vs Min - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TOP 1 vs Min

Author

Topic

Apollois
Starting Member

49 Posts

Posted - 2003-01-08 : 20:35:36

I need to find the next ID, which is integer IDENTITY. Some rows may be deleted, so I cannot assume sequential numbering. Which approach is the fastest?

DECLARE @intCurrentID integer
SET @intCurrentID = 50

-- Which is faster, using Min()

SELECT min(ID)
from Mytable
where ID > @intCurrentID

-- OR USING TOP 1

SELECT TOP 1 ID
FROM Mytable
WHERE ID > @intCurrentID
ORDER BY ID ASCENDING

Best Regards,
Jim

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-08 : 21:38:50

I'd guess the right approach would be to look at the execution plans in query analyzer for each query. Just type in the queries, click on the execution plan button, then get a good book about understanding execution plans.

As a general rule, it should be quicker to calculate the minimums than to sort the list.

Am I wrong about this in SQL 2000?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-08 : 22:18:49

I ran a test and each method generated the same plan, except that MIN() had a Stream Aggregate step with zero cost. All other stats were the same except estimated row count, and that didn't appear to affect the plan (subtree costs and indexes were the same...and yes, I cleared the data and procedure caches between each query)

My personal preference would be to use Min(), simply for the sake of logical neatness: I want the minimum value that's greater than another value. Using ORDER BY with TOP 1 doesn't express that concept as clearly as Min() does, IMHO. If TOP 1...ORDER BY makes more sense for you then use it, but be aware that it's not 100% portable or standard SQL; Min() is.

The fact that these two expressions generated the same plan is fortunate happenstance only; you're querying the clustered index anyway. How each query would work in other circumstances would need to be tested. This might reveal subleties in optimization that would make one method demonstrably better; something as simple as UPDATE STATISTICS could make a world of difference.

Sorry for being vague, that's the nature of query tuning unfortunately. A butterfly flaps its wings in Hong Kong and the whole world could destroy itself; there's a ton of things that affect how queries run. As usual, the only sure way to know is test, test, test.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources