Results 1 to 10 of 10

Thread: What's Faster

  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Smile Unanswered: What's Faster

    Okie I have a situation where I have to get the lastest record for a particular account...

    Now I could use something like this....

    Select top 1 * from tblAccount where accountid = 12345
    order by transactiondate desc

    or I could use something like

    Select * from tblAccount where accountid = 12345
    and transactiondate = (select max(transactiondate from tblAccount where accountid = 12345))

    Which is the best to why and why?

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Probably the first one. Testing this type of query against some tables I have, it turns out to be the quicker of the two.

    I guess you could look at the query execution plan to see which is better for you, obviously indexing will play some part in it.

    If nothing the query plan shows that the second query is having to look at tblAccount twice, which seems to me to be wholly inefficient

    Mark

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: What's Faster

    If tblAccount is a large table, the second query is faster. I assume your accoutid is the primary key of the table, so when you get the max value you would return with one row instead of going thru a sort for the whole table.

    But the second query may not always give you the result you want though. Is it possible that you have two or more rows with the same date?

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    No, there will only be one record per date/accountid conbination.

    As it turns out I have to use the top version (the tool I am using will not allow the subselect (yeah, brilliant. )

    I was really more curious then anything.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What tool are you using that won't allow subselect?!

    blindman

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    DTS, doing a lookup. where the subselect has to include a parameter value.

    Okie, maybe you can do it, but I don't know how....

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DTS SUX, IMHO.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I see it as more of a torture then anything else.... I think that after this stint of DTS work I am going to refuse to do any more....

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DTS is a cobbled-together piece of crap designed to act as a crutch to VB programmers who refuse to learn SQL.

    In my humble opinion of course...

    I thought the concept sucked from day one, and the execution has always been severely flawed. Witness all the posts about it on this forum.

    I use it to transfer data between databases and servers. Period. The data goes into staging tables where all the program-flow logic is embedded in stored procedures.

    I admit that there may be some applications where it is necessary, but I HAVE NEVER SEEN DTS ACTUALLY USED BY A COMPANY THAT I COULDN'T WRITE FASTER AND CLEARER AND MORE ROBUST WITH STORED PROCEDURES AND STAGING TABLES.

    Sorry. It's late here...

    blindman

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    heheheh I tend to agree with you... unfortunately I don't get a lot of chance to influence decisions here...

    plus we have to write something that people even less skilled that I can pick up and carry on with later... *sigh*

Posting Permissions

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