Results 1 to 2 of 2

Thread: Tuning: I'm getting two stories about sub-queries and Update stmts, help please

  1. #1
    Alan Guest

    Tuning: I'm getting two stories about sub-queries and Update stmts, help please

    Here's the deal:

    I just left a site they've rewritten a number of their Select SQL statements from procedural
    row by row processing to inserting into a temp table and then performing update execs to update the fields in
    fields in the temp table that would have been retrieved by procedure calls.

    Got it? (Well, even if you don't you may still be able to answer this question.)

    Most DBA's I've run into say that update statements with sub-queries in them are a major "NO-NO!", but these
    guys claim that it is more efficient to go back and do a series of update statements that sub-query to the temp
    table (otherwise how would they know which rows to update) than to perform row by row processing branching
    out each time to perform query to get that data (if applicable).

    Now, while on site what they said made total sense to me (though it was hard to break from my old ways).
    However, I happened to browsing through the Oracle books at the local borders books and found books that said
    "Hey don't do this. Break it up and make it simple" or words to that effect (an Oracle tuning book by a guy name
    Harris I particularly remember).

    So my question is: Who's right? Or is this one of those product specific answer (Oracle vs Sybase) or perhaps
    it's a Cost-based versus Rule-based optimizer question.

    Any thoughts and definitive references would be appreciated.

    Thanks,
    Alan

  2. #2
    Chris Thibodeaux Guest

    Tuning: I'm getting two stories about sub-queries and Update stmts, help please (reply)

    Hey Alan,
    "Under the Sheets" of SQL Server, if you use a subquery, sql creates a system level temp table in the background. The major benefit for using subqueries is so that you can walk indexes, thus better performance. Usually people don't index temp tables for proc level use, plus you usually create/drop tables which are also process overhead, thus performance degradation. Also, if you ever move to a Clustered environment, you could certainly run into issues since the temp table is created on 1 server and processing is now happening on another, it doesn't know about the temp table on the other server!

    My personal experience, I have never had any issues using subqueries in my updates, which includes very large Web environments with clusters...


    Regards,
    Chris Thibodeaux, President/CEO
    Empowering Solutions, Inc.
    A Management & Technology Consulting Firm
    Phone/Fax - (949)713-3706
    Pager - (888)449-8460

    ------------
    Alan at 1/23/01 9:54:51 PM

    Here's the deal:

    I just left a site they've rewritten a number of their Select SQL statements from procedural
    row by row processing to inserting into a temp table and then performing update execs to update the fields in
    fields in the temp table that would have been retrieved by procedure calls.

    Got it? (Well, even if you don't you may still be able to answer this question.)

    Most DBA's I've run into say that update statements with sub-queries in them are a major "NO-NO!", but these
    guys claim that it is more efficient to go back and do a series of update statements that sub-query to the temp
    table (otherwise how would they know which rows to update) than to perform row by row processing branching
    out each time to perform query to get that data (if applicable).

    Now, while on site what they said made total sense to me (though it was hard to break from my old ways).
    However, I happened to browsing through the Oracle books at the local borders books and found books that said
    "Hey don't do this. Break it up and make it simple" or words to that effect (an Oracle tuning book by a guy name
    Harris I particularly remember).

    So my question is: Who's right? Or is this one of those product specific answer (Oracle vs Sybase) or perhaps
    it's a Cost-based versus Rule-based optimizer question.

    Any thoughts and definitive references would be appreciated.

    Thanks,
    Alan

Posting Permissions

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