Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Unanswered: Select TOP vs Select Max()

    The objective is to select the last row of record from a table.

    which is faster?

    Code:
    select namecolumn from table where identkey=
    (select Max(identkey) from table)
    Code:
    select Top 1 namecolumn from table order by identkey desc
    I can't be sure as I don't have a large DB to test it out...should be second 1 rite??

    Do u guys have a shorter way to select a last record?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Select TOP vs Select Max()

    I was trying your querys for table 12,000,000 rows.
    Speed the same, even execution plans are equal.

  3. #3
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Thanks for your info.

    I thought the second 1 was better since it was with less code....hmm....

    Anyone knows a better way?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Anyone??

    was wondering if there was any command to select last record....

    maybe something like

    Code:
    select lastrow * from table
    of course I would just be imagining for a command like this....if there aren't any way to do it...I hope Yukon will have something like that.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    there was no difference between original queries. the difference is marginal if you change subquery into an explicit assignment to a variable and then using that variable (CPU cost will drop). in case with TOP 1 clause the cpu cost that I observed running it against 73K table was 0.00320, while using ...=@myvar in place of ..=max(..) clause the cost fell to 0.00008. I think the volume of data will have to be increased exponentially for you to see any noticeable difference in performance.

Posting Permissions

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