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

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