Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Unanswered: Best method: 'TOP 1' or 'DISTINCT' or 'MAX'

    'TOP 1' or 'DISTINCT' or 'MAX'
    Any sugestions on which is better to use if I need to select a record that has the highest value - could be a INT or sometimes a DATETIME.

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Distinct will not get you a max value, if you use top make sure you use the order by.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To select the entire record, use TOP 1 on a sorted recordset. To get just the highest value for the field, use MAX().
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2003
    Posts
    46
    [blindman]: someone else suggested that TOP is more efficient then MAX is that true?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't know. It probably depends on a lot of factors and makes little difference either way.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  7. #7
    Join Date
    Nov 2003
    Posts
    46
    Originally posted by rhigdon
    Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)
    I used "SET STATISTICS IO ON" command and got a line for each table in query...

    Table 'tblUser'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
    Table 'ctsJrn_Location'. Scan count 7, logical reads 14, physical reads 2, read-ahead reads 0.
    Table 'ctsIndex'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0.

    Can anyone tell me what does each count of 'reads' mean???

    Thanks,
    Lito

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Scan count - number of times data or clustered index pages were scanned;
    Logical reads - total number of records read from cache (I think);
    Physical reads - total number of pages read from disk (I think);
    Read-ahead reads - number of pages optimizer chose to read ahead (I think)

    But the point is, you want to minimize the first 2 indicators. And, BTW, scan count does not always mean that the actual scan occurred. It just means that the optimizer had to look at data/clustered index pages of the corresponding table so many times.

  9. #9
    Join Date
    Nov 2003
    Posts
    46
    Originally posted by rdjabarov
    ... But the point is, you want to minimize the first 2 indicators...
    What range should those indicators be in, are mine ok?

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It depends on number of rows the tables have vs. number of rows returned.

  11. #11
    Join Date
    Nov 2003
    Posts
    46
    but is there a ratio???

    I am selecting one row from 9 joint tables with approx. 18k records each

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Then your numbers are actually good.

  13. #13
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    The only counter I truly look at is logical IO as it is the number of times a page is hit (not number of pages) the lower you canb get this the better. The problem with physical and read-aheads is they can be optimistic and not exactly accurate.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    your physical reads should be zero or as close to zero as possible.
    this means that you are reading pages from disk into memory.. that is something that you want as little of as possible.

    you will want logical reads to be as low as possible as well but those numbers are based on the actual work that SQLSVR had to perform to retrieve your query. so the number is academic based on your query, statistics, indexing etc.

    typically you should only retrive the rows that you need in a query result, so if the question is which would be the best query to perform? so if you want to just get one row the logical answer would be an aggregate function

    Select Max(Col1) as 'MAXNUM' from table2
    this will retrieve a scalar value for you (one row one column)
    ex
    MAXNUM
    =====
    100

    as far as distinct and top, are concerned
    DISTINCT does not give you a max value, it removes duplicates from the columns gueried which i guess you could then sort decending to get the largest value
    ""select distinct state from table2 order by state Desc""
    ex
    STATE
    ====
    TX
    GA
    FL
    CA

    TOP 'n' is designed to return an restricted set of values
    ""select TOP 5 col1 from table2 order by col1 desc""

    COL1
    ====
    5
    4
    3
    2
    1

    your best method here would be to run the query with each of the different types of commands
    view the stats io and compare all three.

  15. #15
    Join Date
    Nov 2003
    Posts
    46
    Thank you all for your comments and sugestions, this helped me alot. Learn something new every day...

    Lito

Posting Permissions

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