March 14, 2008 at 7:54 am
Zarko Jovanovic (3/14/2008)
what about this:update table1
set table1.column1 = #table.column1, table1.column2 = #table.column2
from table1, #table
where table1.pk=#table.pk
That's fine because the table being updated is in the FROM clause and is properly joined... although a lot of people prefer the "new" ANSI joins like INNER JOIN.
--Jeff Moden
March 14, 2008 at 7:55 am
Jack Corbett (3/14/2008)
Hugo Kornelis (3/14/2008)
Could you post the where the documentation of it producing indeterminate results is? I'd be interested in reading that.
Sure. It's in BOL, or the online equivalent on MSDN: http://msdn2.microsoft.com/en-us/library/ms177523.aspx, scroll down to the heading "Using UPDATE with the FROM Clause". You'll see this:
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.
(followed by an example)
You mention adding and index and the performance gained from it. You do mention, in a parenthetical, being careful not to add too many indexes, but I think you could have more clearly stated that you need to really study out the afffects of adding an index and that will take a lot more than 5 minutes. There are many beginners that are on this site regularly who may read that and start adding indexes whenever they encounter performance issues with a single query and then start having problems elsewhere.
Fair enough. Adding indexes by trial and error is always a bad idea, you really have to understand what they are, how they work, and how they impact every aspect of SQL Server.
Thanks to Jeff and Hugo for sharing their expertise and making me think before I code!
And thanks to you for your welll though out reaction!
(edit - changed the BOL address to a proper hyperlink)
March 14, 2008 at 7:55 am
David McKinney (3/14/2008)
Very good article.I just think RBAR would catch on more, if it were pronounced AR-BAR. (Sorry 😉 )
Maybe "our Bar" would work even better 😀
--Jeff Moden
March 14, 2008 at 8:01 am
Nick M. (3/14/2008)
The links to UPDATE are to the UPDATE() function, not the UPDATE statement syntax, which is 1 line above in the index. 🙂
Thanks for pointing that out, Nick. I sure am sorry about that and I'll resubmit the article with the correction...
The correct link is...
http://msdn2.microsoft.com/en-us/library/ms177523(SQL.100).aspx
I apoligize for the confusion.
--Jeff Moden
March 14, 2008 at 8:04 am
Jim Russell (3/14/2008)
RE CTEs. I love them and use them, but I had gotten the impression that they too were a MS nonstandard extension. Are they part of the SQL standard and are they available in Oracle. (Same syntax?)
They are ANSI standard. They were not in SQL-92, but they are in SQL:2003, described as part of "7.13 ". The standard does seam to have a different way to express recursive CTEs though.
I have no idea if Oracle implements them.
March 14, 2008 at 8:09 am
ALZDBA (3/14/2008)
Indeed 70% or our tuning time is actualy sql-education.If our dev's would only apply the basic rules and also try to write their
code at least in two different ways,
they would soon discover where performance is to be found.
Once again, thank you for sharing the knowledge 😎
I appreciate the feedback, Johan...
The problem is that most of the Devs nowadays are really GUI programmers that learned just enough SQL to be able to do necessarily RBAR Inserts/Updates/Deletes. They never really studied SQL so they really don't know enough to do anything about performance especially in any kind of multi-row or batch code. Even those that do know, GUI programming is really a "we need this NOW" environment and the Developers are rarely given the time to think about performance, never mind the basics. They frequently need to program by exception... if it works and no one squawks about it's performance once it's in production, think of all the time they saved... and the DBA just doesn't have time to be a 1 person code reviewing machine. Trust me, not my idea of the right way to do it but more often the truth than not.
--Jeff Moden
March 14, 2008 at 8:10 am
Anders Bendix Nielsen (3/14/2008)
That is simply one of the best articles on RBAR I have ever seen!Excellent!! 🙂
Thanks for the great feedback, Anders.
--Jeff Moden
March 14, 2008 at 8:10 am
Matt Miller (3/14/2008)
And there are other folks who don't know that what you call Oracle syntax is actually ANSI-standard syntax, and that the optional FROM clause in SQL Server is non-standard. In fact, even Access (also microsoft) has a different syntax for the same thing! So beware - if you choose to use this, you can't port your code.
Interesting read. I have to ask though - I have to see any combinations of DBMS' where a straight port of code is simply a matter of cut and paste. Is that really a valid concern?
Noone should expect to port as a straight cut and paste. But that doesn't make the consideration invalid. I'll give you three arguments:
1) If you have to port, all proprietary code is a "must change" and all standard code is a "might have to review" - for it should at least work, though maybe not as fast as some alternative. So by using standard code where possible (i.e. always in non-time sensitive parts of the code, and in the more time-sensitive you still use standard SQL if the performance loss is marginal), you minimize the amount of work for porting.
2) By using standard code, you increase the amount of people who should be able to read and maintain your code. When people show me an Oracle query that uses only standard SQL, I understand it. As soon as it includes a (+), I know that it is a non-standard outer join (much like =* and *= in Sybase and SQL Server), but have no idea what it means exactly.
3) Proprieatry features are in general more prone to be changed in some later version of the product.
(edit - fixed weird "quote all in one line" issue; should respect window size now)
March 14, 2008 at 8:21 am
Hugo Kornelis (3/14/2008)
Matt Miller (3/14/2008)
And there are other folks who don't know that what you call Oracle syntax is actually ANSI-standard syntax, and that the optional FROM clause in SQL Server is non-standard. In fact, even Access (also microsoft) has a different syntax for the same thing! So beware - if you choose to use this, you can't port your code.Interesting read. I have to ask though - I have to see any combinations of DBMS' where a straight port of code is simply a matter of cut and paste. Is that really a valid concern?
Noone should expect to port as a straight cut and paste. But that doesn't make the consideration invalid.
Oh I agree. It's not at all invalid - it just seems secondary.
I'm just trying to get a read on where you place this versus performance, efficiency etc. Would you implement standard code over better performing code? Where's the break-even point (how close in perf would they need to be, etc...)?
For example - my *limited* exposure to Oracle shows me that a LOT of the sub-queries written for it are correlated subs, which seem to work VERY well in Oracle. Well from what I can see - in SQL Server, performance-wise that's more of an exception than the norm (for example - EXISTS is an exception to that). Over two third of the CSQ's I've run into have about the same performance as trying to suck wet cement through a straw. And yet - that's the standard way to write it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 8:27 am
Jeff Moden (3/14/2008)
I appreciate the feedback, Johan...
The problem is that most of the Devs nowadays are really GUI programmers that learned just enough SQL to be able to do necessarily RBAR Inserts/Updates/Deletes. They never really studied SQL so they really don't know enough to do anything about performance especially in any kind of multi-row or batch code. Even those that do know, GUI programming is really a "we need this NOW" environment and the Developers are rarely given the time to think about performance, never mind the basics. They frequently need to program by exception... if it works and no one squawks about it's performance once it's in production, think of all the time they saved... and the DBA just doesn't have time to be a 1 person code reviewing machine. Trust me, not my idea of the right way to do it but more often the truth than not.
Unfortunately a sad fact in many companies. They see SQL as being a secondary or even tertiary skill, that is untill they either hit a brick wall in developing something or (by chance) get an expert in and suddenly find that their reports system is running 1000 times faster than before.
March 14, 2008 at 8:36 am
Matt Miller (3/14/2008)
Hugo Kornelis (3/14/2008)
I'm just trying to get a read on where you place this versus performance, efficiency etc. Would you implement standard code over better performing code? Where's the break-even point (how close in perf would they need to be, etc...)?
(As an aside, could you PLEASE edit your post? There is a "code" tag surrounding a quote that makes it appear as a long line instead of flowing to the window size. Can you either change that to "quote", or remove it? TIA!)
Fair question. The answer is, of course, that it depends 😀 Here's my take on it.
When coding for SQL Server, I keep portability in the back of my head. If I code some SQL that will run in a nightly batch, taking 5 minutes in a 6-hour time window, correctness, maintainability, and portability are my only concerns, in that order. When I have to code a query that is used in an online process and has to return results with sub-second repsonse time, the list changes to correctness, maintainability, portability, performance (where the latter two will swap places if needed to get required performance). And when absolute best performance is of utmost importance, I sometimes even choose performance over maintainability. (Never over correctness, of course :w00t:).
On the other side of the equation, when I have to port code and there is plenty of time available, I'll fix non-portable code first and then start optimizing for the new platform. But when (as is often the case) I am under pressure to port quickly, because the application has already been sold to some Oracle shop, I'm glad if I can get it to run quick - so that we can deploy and THEN start looking at performance, hoping that I can squash the worst performance killers sooner than the customer can fill the tables with enough rows to notice the performance problems 😉
BTW, I'm not talking from personal experience, I've never had to port a DB yet. But I do know enough that I can imagine the pressure typically present in such a project.
March 14, 2008 at 8:49 am
(fixed the tag - thanks).
It's a fair position to start from. I have spent a lot of time supporting and developing in what I will call "hardware crippled" environments, with overloaded servers at pretty much all times, and that colors my assessment. Can't seem to get management to think about getting extra servers for some reason.
So I'm always finding myself having to make the "maintainability vs performance" assessment, since I just don't come across any 6-hour downtime/slow time windows, let alone 20 minutes. But - I have also seen well-provisioned environments with perf to spare, so other concerns then come into play at that point.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 10:33 am
Jeff,
I love articles like this that do such a good job of explaining things step by step. I particularly appreciate the real-life example by taking the BOL example and re-writing it. That really explains your point.
Yesterday I finished a table-valued function where a piece of one query uses a correlated subquery. It uses the correlated subquery because I copied that bit off of an example I had seen on-line, and I do not have time to work with this query any more. Your article inspired me to add a comment that the query may not be optimized, and I should come back and re-write (or at least analyze) in the future when time. It may not sound like much, but I really do pay attention to such comments. I know that when I have time in the future, I will go back and work on seeing if I can't improve the code and the comment will draw my attention to that bit of it. Since it is a query that will run every 3 seconds, it is worth tuning it!
As an aside: I think because of my background (coming first from doing queries in MS Access design view), doing such tasks with joins (using derived tables and now CTEs) always made more sense to me than correlated subqueries. I find joined derived tables mentally easier to work with than correlated subqueries. It has taken me a long time to get comfortable with correlated subqueries and am just now starting to use them more (with caution). Go figure.
March 14, 2008 at 11:04 am
Thanks both Jeff and Hugo for the great article and the argument.
It re-affirms the standard DBA answer to any question: it depends;)
Kudos to both of you.
March 14, 2008 at 12:06 pm
Well written, great info, outstanding examples.
-- Cory
Viewing 15 posts - 16 through 30 (of 76 total)
You must be logged in to reply to this topic. Login to reply