Home Forums SQL Server 2005 T-SQL (SS2K5) Performance of Joins over Updates RE: Performance of Joins over Updates

  • September 7, 2012 at 8:38 am

    #1535090

    dwilliscp (9/7/2012)

    Michael Valentine Jones (9/7/2012)

    You haven't provided enough information for anyone to begin to answer that question.

    You should just try each way to see which is faster.

    The problem with that is.. before the second run I would need to make sure the data is flushed from memory... not sure how to do that. Otherwise the second run should always be faster since it does not have hard drive I/O.

    Then do several runs - A after A, A after B, B after A, B after B.

    I've rarely seen an UPDATE to an intermediate table, as you describe, perform faster than a straight SELECT. It's most often seen when the developer has missed something.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden