Results 1 to 15 of 15

Thread: Top vs Max

  1. #1
    Join Date
    Apr 2002
    Posts
    8

    Unanswered: Top vs Max

    I am trying to work work out the relative merits of the following queries

    select min(dateofbirth)
    from emp

    against

    select top 1 dateofbirth
    from emp
    order by dateofbirth asc

    Is there a known performance improvement of using one method over the other?
    Will indexing the dateofbirth field help one query to run quicker?
    I can't pick any difference in the time taken to run these queries although the query plans are different.

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Interesting question. I tried a little experiment on a 3M+ row table and found the following..

    No index:
    The execution plan for the TOP appeared to be grossly LESS efficient than MIN. The Trace, however, showed TOP to be a little less efficient. The TOP took about 10-22% longer to run and virtually the same number of reads

    Non Clustered index:
    Dead even

    Clustered index:
    Dead even

    I doubt that I would ever use TOP 1 when looking for the minimum value in a column. I think most people know what MIN is trying to do where as using TOP 1 would make most wonder what the goal is.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2003
    Location
    USZIP94583
    Posts
    1

    Max vs Top, Min vs Top, Top vs Max, Top vs Min, Top 1 vs Min, Top 1 vs Max

    Excellent. I wondered the same a moment ago. Thank you.
    Found this thread by google search "top vs max"

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Unless the optimizer is smart enough to change the TOP query into a standard MIN query, I'm afraid that the TOP query would scan and sort the entire dataset before returning a result, while the MIN query would only need to perform the scan.

    Use MIN.

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey...you should NEVER use TOP with out an ORDER BY, unless you don't care what you're getting, because the result would be meaningless....the way data is stored in the database has no bearing on any result...

    if it doesn't matter, then go nuts
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    blindman I would have expected the TOP directive to force a scan of the resultset not the dataset. Still not the greatestest thing in my opinion.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Check. Result set, not dataset.

    blindman

    "Grander Poobah"? These monikers are getting rediculous.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and using MIN, SQL Server would not necessarily even have to create a resultset before returning the value.

    blindman

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Originally posted by blindman
    Check. Result set, not dataset.

    blindman

    "Grander Poobah"? These monikers are getting rediculous.
    Sorry, I wasn't 100% what you were referering to.


    Yup!
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Originally posted by blindman
    ...and using MIN, SQL Server would not necessarily even have to create a resultset before returning the value.

    blindman
    I think the overwellming response to this issue is use MIN/MAX unless you have a very specific need for TOP.
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How is that SELECT TOP 1 used no CPU?

    Code:
    SET STATISTICS PROFILE ON    	SQL:StmtCompleted	0	0	0	0
    SELECT TOP 1 Org FROM ledger_detail ORDER BY Org   	SQL:StmtCompleted	150	0	103	0
    SELECT MIN(ORG) FROM ledger_detail    	SQL:StmtCompleted	113	113	218	0
    SET STATISTICS PROFILE OFF  	SQL:StmtCompleted	0	0	0	0
    SET NOEXEC OFF SET PARSEONLY OFF	SQL:StmtCompleted	0	0	0	0
    SET ROWCOUNT 0	SQL:StmtCompleted	0	0	0	0
    DBCC USEROPTIONS	SQL:StmtCompleted	0	0	2	0
    That;s from a million row table...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Paul Young
    I think the overwellming response to this issue is use MIN/MAX unless you have a very specific need for TOP.
    Esp because TOP is meaningless without an order by?

    Anyway to add to statistics in QA?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Oct 2003
    Posts
    706
    Originally posted by Brett Kaiser
    How is that SELECT TOP 1 used no CPU?
    That;s from a million row table...
    In my experience it's really not that uncommon for an operation to use almost no perceptible CPU time, and even (if the buffers are working as they should) not a lot of I/O.

    If you think about it, there's really almost nothing for the CPU to do on most queries, except for the time spent "thinking about them" to come up with the execution plan. All the rest of the time the CPU's merely keeping the I/O devices busy... and trying to arrange the buffers so that I/O requests are kept to a bare minimum. Query processing is almost exclusively "I/O bound."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,794
    Provided Answers: 11
    Brett: If you have an index on ORG, then I would suspect all SQL Server has to do is work it's way down the index to find the min value. If not, then SunDial is probably right, you are seeing just a pile of I/O activity.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by MCrowley
    Brett: If you have an index on ORG, then I would suspect all SQL Server has to do is work it's way down the index to find the min value. If not, then SunDial is probably right, you are seeing just a pile of I/O activity.
    Thanks guys...yeah it's a paper thin index on org..

    and an index seek...there's an operation called TOPn Sort? That takes up must of the cost...

    Is this considered thread theft yet?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •