InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 10:34:49 GMT20http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Matt (2/1/2009)[/b][hr]Great article Jeff. Another developer had a stored procedure that was run daily to create a report. The stored procedure took 3 hours to run. The SP had multiple "death by SQL" updates in it. After rewriting the code, the sp now takes 2 minutes (max) to run. I was a beginner at SQL when I ran across this article, and I have used this technique in many queries.[/quote]That's awesome, Matt. There's no better compliment an author can have than having someone come back with such a success story. Thank you for taking the time to post it. I very happy to have been able to have helped through the article.Sun, 01 Feb 2009 20:59:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxGreat article Jeff. Another developer had a stored procedure that was run daily to create a report. The stored procedure took 3 hours to run. The SP had multiple "death by SQL" updates in it. After rewriting the code, the sp now takes 2 minutes (max) to run. I was a beginner at SQL when I ran across this article, and I have used this technique in many queries.Sun, 01 Feb 2009 19:05:23 GMTMatt-566349http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Jeff Moden (3/20/2008)[/b][hr][quote][b]Manie Verster (3/20/2008)[/b][hr]Hi Jeff, and I thought you were an American. Americans only eats meatloaf or not?:hehe:[/quote]Heh... depends... will meatloaf hold up in a slingshot? ;)[/quote]My Great-Aunt Wilma's would....Course - she wasn't much of a cook....:)How well it holds up in a slingshot is inversely proportional to how well it holds up with mashed potatoes and gravy....:hehe:Thu, 20 Mar 2008 08:05:14 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Manie Verster (3/20/2008)[/b][hr]Hi Jeff, and I thought you were an American. Americans only eats meatloaf or not?:hehe:[/quote]Heh... depends... will meatloaf hold up in a slingshot? ;)Thu, 20 Mar 2008 08:03:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxHi Jeff, and I thought you were an American. Americans only eats meatloaf or not?:hehe:Thu, 20 Mar 2008 03:55:58 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]J (3/19/2008)[/b][hr]HmmmI vaguely remember the furor about VB "dot Nyet" invalidating all code written in previous VB versions...Walking up to Frankenstein's castle with torches and pitchforks ...[/quote]Yes, I was the guy in the front with the pitchfork and the Rocket Launcher.Wed, 19 Mar 2008 15:31:53 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxHmmmI vaguely remember the furor about VB "dot Nyet" invalidating all code written in previous VB versions...Walking up to Frankenstein's castle with torches and pitchforks ...Wed, 19 Mar 2008 15:10:48 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]rbarryyoung (3/15/2008)[/b][hr]Anyway, for those who may actually be interested in what parts of Transact-SQL are standard and what may not be, here is a good readable summary site (yes, I do admit that the official Standards can be hard to read): [url]http://troels.arvin.dk/db/rdbms/[/url][/quote]The best part of that is now I have a least a copy of the final draft of the ISO standards and they didn't cost anything. Thanks for that link, Barry.Tue, 18 Mar 2008 06:05:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Anirban Paul (3/18/2008)[/b][hr]The article was good. I liked it even though it has some probs as pointed by some of my [i]'co-readers'[/i]. For me it started the base of RBAR.Thanks Jeff :P [/quote]Thanks, Anirban... I do appreciate the feedback.Tue, 18 Mar 2008 05:38:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxThe article was good. I liked it even though it has some probs as pointed by some of my [i]'co-readers'[/i]. For me it started the base of RBAR.Thanks Jeff :P Tue, 18 Mar 2008 02:06:42 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxheh... and I have a special batch of porkchops on the next one ;)Mon, 17 Mar 2008 11:18:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxBy the way, Barry - don't give up ranting...You're good at it! I will also be checking that link you mentioned previously (as to the summarized standards ).We can always use another in the "volley for serve" dialogs we have here. Pile on!Mon, 17 Mar 2008 08:02:32 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]rbarryyoung (3/17/2008)[/b][hr][quote][b]Jeff Moden (3/16/2008)[/b][hr]Thanks, Matt... that'll teach me... I normally do a lot more research and "due diligence" before I post an article. I got in a hurry this time and didn't walk all the bases to see where they were before I got up to bat... it showed, too. I got tagged at 3 of the bases, spiked at home, and got in an argument with at least 2 people in the crowd. :D[/quote]So it was more like a Hockey game than Baseball. (I don't think I know the emoticon for "toothless grin").[/quote]Hey - at least you haven't booed Santa (yet).... Stop by Philly some time when I'm up there visiting the family - I'll teach you THAT trick....:)Mon, 17 Mar 2008 07:48:30 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]ALZDBA (3/17/2008)[/b][hr]btw: I've noticed that when you receive at website timeout for the reply, hitting the "back" button may also recouver your typed content, so it may be worth the effort.I just copy all the text before hitting the submit button ;)[/quote]This technique has always worked for me also, but I have been using mostly Opera and Firefox. (yet another reason to support standards!) :)Mon, 17 Mar 2008 07:28:05 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Jeff Moden (3/14/2008)[/b][hr][quote][b]ALZDBA (3/14/2008)[/b][/quote]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.[/quote]And [b]LINQ [/b]will also have its impact with regard to this behaviour :ermm:It took me some time to work through all replies and consequences, but once again I love this article and its grown technical chain reaction :w00t:btw: I've noticed that when you receive at website timeout for the reply, hitting the "back" button may also recouver your typed content, so it may be worth the effort.I just copy all the text before hitting the submit button ;)Mon, 17 Mar 2008 03:08:57 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Jeff Moden (3/16/2008)[/b][hr]Thanks, Matt... that'll teach me... I normally do a lot more research and "due diligence" before I post an article. I got in a hurry this time and didn't walk all the bases to see where they were before I got up to bat... it showed, too. I got tagged at 3 of the bases, spiked at home, and got in an argument with at least 2 people in the crowd. :D[/quote]So it was more like a Hockey game than Baseball. (I don't think I know the emoticon for "toothless grin").Mon, 17 Mar 2008 01:09:25 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxCorrect in some areas, Thomas... you can try the code I posted to verify...The source data is queried once for [i]each [/i]correlated subquery meaning that, with the correct index, 3 correlated subqueries do 3 Index Seeks. The target table is scanned once.The source data is queried only once for the Derived table an it's a scan... the target table get's an index seek.The correlated subquery method with all 3 columns in the presence of an index, takes about 1,470 milliseconds to execute on a million rows in the source table.The derived table method with all 3 columns in the presence of an index, takes about 850 milliseconds to execute on a million rows in the source table.I don't think I'd worry about splitting the tables since both handle a million rows of aggregates in about a second.Sun, 16 Mar 2008 23:54:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxI wouldn’t totally agree with you on this. My first thoughts were its good to have a derive table “it definitely helps to identify the records which are in OrderHeader at different stages”. Few things that concerns me - The chances are that some sort of scan would be performed on the join TB's - All the data will be queried at once in each derive table If OrderHeader was very large table I would probably thought of storing a transformed data set in #temp table(From OrderHeader) and finally doing the required update to the destination table. Your Thoughts!!!Sun, 16 Mar 2008 23:40:02 GMTThomas-428301http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxThanks, Matt... that'll teach me... I normally do a lot more research and "due diligence" before I post an article. I got in a hurry this time and didn't walk all the bases to see where they were before I got up to bat... it showed, too. I got tagged at 3 of the bases, spiked at home, and got in an argument with at least 2 people in the crowd. :DSun, 16 Mar 2008 23:34:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Jeff Moden (3/16/2008)[/b][hr]Ok, folks... I was operating on some old experiences with correlated sub-queries... Hugo and Barry's posts made me look deeper and, after studying the actual execution plans in both SQL Server 2000 and 2005, I have to admit, they're both correct... it turns out to not be rebar in either of the two examples I gave. All personal passion asside, each correlated sub-query is, in fact, evaluated as if it were a derived table. To summarize, a single correlated sub-query with proper indexes will beat the inner join in SQL Server 2000 and tie the inner join in SQL Server 2005. Correlated sub-queries loose when more than one is attempted with the same predicate compared to the inner join.If you have a 3rd party solution that forbids the modification of the schema by adding an index (and possibly voiding a maintenance agreement), then an inner join will be a single correlated subquery.The great point is that even though I was a miserable failure in that I didn't consider the effect of an index in the original article, the folks that took their good time to do a bit of additional analysis brought that fact, and others not intended, to good light.And with, that, my hat is off to all that participated. Thanks, folks.[/quote]Learning by one's own mistakes - one of my "favorite" ways to learn...A good article just got better (and in that unintended way...)Sun, 16 Mar 2008 21:36:11 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxOk, folks... I was operating on some old experiences with correlated sub-queries... Hugo and Barry's posts made me look deeper and, after studying the actual execution plans in both SQL Server 2000 and 2005, I have to admit, they're both correct... it turns out to not be RBAR in either of the two examples I gave. All personal passion asside, each correlated sub-query is, in fact, evaluated as if it were a derived table. To summarize, a single correlated sub-query with proper indexes will beat the inner join in SQL Server 2000 and tie the inner join in SQL Server 2005. Correlated sub-queries lose when more than one is attempted with the same predicate compared to the inner join.If you have a 3rd party solution that forbids the modification of the schema by adding an index (and possibly voiding a maintenance agreement), then an inner join will beat a single correlated subquery.The great point is that even though I was a miserable failure in that I didn't consider the effect of an index in the original article, the folks that took their good time to do a bit of additional analysis brought that fact, and others not intended, to good light.And with, that, my hat is off to all that participated. Thanks, folks.Sun, 16 Mar 2008 20:36:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Hugo Kornelis (3/16/2008)[/b][hr]][/quote]Heh... How often I've been bit by the infamous timeout on this forum... I too have learned to at least do a {CTRL-A}{CTRL-C} before I hit PREVIEW or Post...Hugo, thanks for your post. You're a gentleman and a scholar. Heh... if nothing else, the two of us certainly got folks thinking and some of them also took the time from their busy days to join us in this thread. Each of us shares a similar passion... not so much to bend folks into our way of thinking... it's because we each believe we are correct because of our own experiences and we each strive to try to make it easier for the next poor slob that may have start his or her day by typing the word SELECT... :) Well done.Sun, 16 Mar 2008 20:26:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]anvi (3/16/2008)[/b][hr]This is a top class article: clear, deep and useful.[/quote]Thanks for the feedback, Anvi. What's even better than my humble article is what folks have to say in this thread. I don't always agree with what's said, but it's good solid feedback and a great exhange of ideas both pro and con. That's what makes this forum so worth while. Again, thanks for taking the time to read the article and I really do appreciate the feedback.Sun, 16 Mar 2008 20:15:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Matt Miller (3/16/2008)[/b][hr]I love reading standards. Theory, especially when backed up with actual real-life appications and scenarios the theory is supposed to address, is very useful. I just have a problem having to PAY to read the standards. It defeats the purpose of a standard. After all - they're supposed to be internationally recognized standards, run by (supposedly) government-sponsored agencies. Meaning - I've already paid for it once and don't intend to pay large sums for it again.[/quote]Well that I agree with. I am afraid that too many of the older organizations (of all types, not just standards) became far too dependent on their publishing revenue as a source of income instead of just as a means of defraying their publishing costs. So when the Internet came along, they just couldn't adapt to the much better model that newer standards groups like W3C use. Most of the academic journals are having the same problem.The following site is an excellent summary of the state of the SQL standards as well as how the process works. They have an interesting note about being able to get some of the ISO documents for $18 instead of the usual $130+. Supposedly they have the same content, but, I haven't followed up to see what the caveats might be: [url]http://www.jcc.com/sql.htm[/url].[quote]I've always been a big fan of having standards - I just don't imbue them with god-like abilities to predict the future any better than anyone else involved, nor do I presume that they are in any way complete or comprehensive of the needs in the real-world.[/quote]Neither do I. Nor do I think that predicting the future or what will be best for everyone is a reasonable goal for them, or anybody else for that matter. I do however think that standardizing current practice and codifying established theory and pricipals [b]is[/b] a reasonable goal.[quote]I also think that the theorists do also need to come out of the ivory towers and get their hands dirty from time to time. And by the way - having the vendors at the table along with the theorist, THAT's a good thing IMO (keeps both sides "honest").[/quote]Agreed, although "honesty" isn't what the vendors bring to the table, more like "buy-in" and "implementability" although they posture with both of those things. I don't have anything against the vendors, I worked for one of them for 10 years, and I still work very closely with many of them (in fact some of them are my best customers), I am just realistic about what their real goals are in all of this. By the way, there is a third group that you do not mention: customers and users of the techonology(s). IMHO, this is the most important group, unfortunately it is also the least involved because it doesn't see the importance.Sun, 16 Mar 2008 17:35:19 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxHi Jeff and all,I had just composed a lengthy reply, and when I posted it I got an error about an expired session and all my text was lost. Have I ever told you how I [b]HATE[/b] web-based discussion forums? :angry:Anyway, here is the shorter reconstructed version...[quote][b]Jeff Moden (3/14/2008)[/b][hr]It's a shame that some folks have to get nasty or condescending, but that's kind of a lesson all by itself. ;) [/quote]I hope it's not me you're talking about. If it is, please accept my apologies.I always try to criticise only IDEAS I disagree with, and never the person presenting them. However if I did get carried away this time, or if something came across differently from how I meant it to (maybe lost in translation?), I am really sorry.[quote]someone said that the article said [b]subqueries [/b]are RBAR and are bad. I didn't say that at all. Go back and look... I said [b][color="RED"]correlated [/color]sub-queries [/b]are RBAR and are bad.[/quote]That IS me you're talking about :)My bad. I meant to include the word correlated in my reply. As Barry demonstrates with an example, the query optimizer is in fact able to internally "rewrite" a correlated subquery as a join.[quote]I also said that code portability is a myth and some folks pretty much hammered on that for quite some time. They also hammered on using SQL Server's proprietary Update that has a FROM clause because it's not ANSI. Then they wrote that it shouldn't ever be used because Microsoft is saying it'll be deprecated. [b]HEH, THEN they turned around and admitted to using it a couple of paragraphs later.[/b][/quote]That's me as well :DExcept I did not say that UPDATE FROM will be deprecated. I made such a suggestion on Connect, and Microsoft has said that they are "looking at the future of the FROM clause". I now see that the links to my blog post and the suggestion on connect are barely visible, so here they are again:* Blog post about UPDATE FROM at [url=http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx]http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx[/url].* Suggestion to deprecate UPDATE FROM at [url=https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332437]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332437[/url].And yes, I do use UPDATE FROM in situations where the performance gain is huge and important. Like Barry, I try to code for portability, but sometimes have to make the trade off. I'd like to write portable code all the time, but if that would result in some unmaintainable monster or some slug-like performance, I'll have to cut my losses. More on this on my blog post as well.[quote]While we're at it, some folks worry about the documented "fact" that the UPDATE FROM will cause "indeterminent results". Let me just say that a correctly laid out UPDATE FROM with the Target Table in the FROM clause has never caused an error for me.[/quote]The indetermined results occur when more than a single row matches the criteria (usually because of incorrect specs or a coding error). If this happens in the subquery version, you'll get an error. If this happens in the joined version, there is no error, no warning - SQL Server will just pick one of the matching rows for you. And in the worst case, that is exactly the one you intended, so your test run will produce the expected results and you won't notice the error lurking in your code. Until of course, one day, it will produce errors in the production database.[quote]Ok, let's get down to an example that'll really show why I generally don't allow correlated subqueries in my shop... especially on Updates...[/quote]I won't quote the long example here, but let me compliment you on a GREAT example. This is indeed a compelling case for UPDATE FROM for now, and for MERGE once we get SQL Server 2008 in our hands. Your example illustrates the point I'm trying to make in my blog post even more effectively than my own example.I just don't think that this is an example of RBAR. I'd rather call it SBAS (subquery by agonizing subquery). Every subquery added adds the same amount to the execution time, because each subquery is incorporated into the plan as if it were different from the others - so all the work is done over and over again for each of the subqueries. A better optimizer would have prevented that. Full support for row constructors would have prevented that. And using the UPDATE FROM or (in SQL Server 2008) prevents it as well, since it almost spells out to the optimizer what the better plan is in this specific case.But I do indeed agree that in cases like this, until you can use MERGE, UPDATE FROM is vastly superior to the ANSI-compliant UPDATE with several subqueries.[quote]And THAT's a big part of what I was trying to get across in the article, folks.[/quote]And on THAT, we are in full agreement! ;)And now I'm going to copy the entire post in my clipboard, save it to some text file on my disk and only then will I hit the post button :)[And a good thing too - I just had the same error occur again! :(]Sun, 16 Mar 2008 16:33:51 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxThis is a top class article: clear, deep and useful.Sun, 16 Mar 2008 16:09:40 GMTanvihttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]rbarryyoung (3/15/2008)[/b][hr]*sigh* Implication by Association. You should not be passing judgement on the SQL group based on the actions of two completely different groups, especially when you haven't even read its work.[/quote]I love reading standards. Theory, especially when backed up with actual real-life appications and scenarios the theory is supposed to address, is very useful. I just have a problem having to PAY to read the standards. It defeats the purpose of a standard. After all - they're supposed to be internationally recognized standards, run by (supposedly) government-sponsored agencies. Meaning - I've already paid for it once and don't intend to pay large sums for it again.[quote][quote]... I keep hearing the same criticism about the "standard" SQL: heavy on syntax rules, but light on the semantics backing the syntax up. [/quote]who said that? I don't even know what that would mean, let alone whether it's a bad thing.[/quote]Meaning - several of the blogs point out that it's often more interested in what the FORM of the statement, and the description of the MEANING of the statement is left not fully defined (which again - is a common point of contention with these "standards" in a lot of technical areas). For example - it's one of the reasons why the windowing functions in SQL didn't get defined to allow for "running" aggregate, since the standard didn't say it should[quote][quote] (they're not since to the best of my knowledge - there are still 2 bodies trying to put out standards on SQL).[/quote]Who is that other body? I hope you don't mean ISO since they are cooperating organizations, not competitors. ISO by the way is authorized by the 157 nations who have Standards agencies that are members of it. That is a little bit more authority than Microsoft or Oracle.[/quote]Actually - I AM talking about ISO. I'm fully aware of the "cooperating" status. I also know that just because they cooperate doesn't mean that they put out 100% compatible standards.[quote][quote]Are they really that much better at figuring this out then the rest of us? Or is this another occasion where we're getting theory shoved at us that has no chance at a decent implementation based on how hardware and software works these days?[/quote] Funny, I heard these exact same complaints for 5 years after Codd & Dates book came out, and mostly from people who had never read them. Thank goodness someone did read thier work and did implement it despite how hardware and software worked in those days, [b]or it never would have changed[/b].[/quote]I've read Codd and Date's stuff, thanks. Again - theory is great, but it DOES need to be implementable, or else all it ever becomes is all that much more useless paper (see the Third manifesto if you want to see my drift - every one of dozen or so drafts end with "and this is perfectly useless in any real-life scenario" or some very close approximation thereof).Again - I am more than happy to do my research and have my voice at the table like you mentioned. I do have to doubt that they really welcome our voice at the table, since they seem to have NO interest in hearing what is really needed. If you want to hear opinions - you don't charge a few thousand dollars just for the privilege of reading said standard, and hide it away so that only the cognoscenti and academics may comment.I've always been a big fan of having standards - I just don't imbue them with god-like abilities to predict the future any better than anyone else involved, nor do I presume that they are in any way complete or comprehensive of the needs in the real-world. They're just a core, and not the whole picture. I also think that the theorists do also need to come out of the ivory towers and get their hands dirty from time to time. And by the way - having the vendors at the table along with the theorist, THAT's a good thing IMO (keeps both sides "honest").Sun, 16 Mar 2008 16:03:54 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote]Hmm, I tried to swear off "ranting" some years ago, but that doesn't seem to be working out so well... Sorry.[/quote]I do understand your passion about it and thanks for the link. I'll take a look.Sun, 16 Mar 2008 09:19:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]DAvid (3/15/2008)[/b][hr]I am surprised (being introduced to other DBMS before SS) that the location of the predicate relating the summarised aggregate sub query and the table for update had any effect on the query execution. Whether expressed in the where clause of the correlated sub query referring back to the table being updated or returned from the derived sub query and specified in a join would have any effect on the query execution. For me they are different semantics for the same logical update.Thanks for pointing out that when the join predicate is more appropriate than a correlated sub query where predicate when the column is not covered by an appropriate index.[/quote]Thanks DAvid... maybe without intending to, you bring up a good point... sometimes the table belongs to a 3rd party app and you can "void" the support contract if you do anything to their tables including the addition of a simple index.Sun, 16 Mar 2008 09:10:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxHmm, I tried to swear off "ranting" some years ago, but that doesn't seem to be working out so well... Sorry.Anyway, for those who may actually be interested in what parts of Transact-SQL are standard and what may not be, here is a good readable summary site (yes, I do admit that the official Standards can be hard to read): [url]http://troels.arvin.dk/db/rdbms/[/url]Sat, 15 Mar 2008 20:50:37 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Matt Miller (3/15/2008)[/b][hr]I am not willing to shell out money to get my hands on the actual SQL standard from ANSI, but considering some of the dreck that's been coming out of a lot of the so-called "working groups" (XPATH/XQUERY or CSS, to name a few) - I have to say that a healthy amount of skepticism ought to be applied to those "standards".[/quote]*sigh* Implication by Association. You should not be passing judgement on the SQL group based on the actions of two completely different groups, especially when you haven't even read its work.[quote]... I keep hearing the same criticism about the "standard" SQL: heavy on syntax rules, but light on the semantics backing the syntax up. [/quote]Who said that? I don't even know what that would mean, let alone whether it's a bad thing.[quote]I mean - really. At what point did ANSI become the one and only authority on what SHOULD be in structured query language?[/quote]In 1986 when the owner of SQL (IBM) submitted it to ANSi for standards review. That responsibility is jointly shared with ISO which ANSI is a member of.[quote] (they're not since to the best of my knowledge - there are still 2 bodies trying to put out standards on SQL).[/quote]Who is that other body? I hope you don't mean ISO since they are cooperating organizations, not competitors. ISO by the way is authorized by the 157 nations who have Standards agencies that are members of it. That is a little bit more authority than Microsoft or Oracle.[quote] And - who's out "proving" that the so-called experts are in fact proposing what really IS the very best way?[/quote]The problem with this is, who gets to decide what "best" is? You? Me? Oracle? Microsoft? Well if you ignore standards then it's Microsoft and we are reduced to begging them to implement what we want and since they know that very few of us can afford to extricate ourselves from their highly propietary environments, we don't really have a lot of leverage there. What the standards organizations do is to at least give us a [i]chance[/i] to have a place at the table, to have some say in what we think that "best" ought to be.[quote]Are they really that much better at figuring this out then the rest of us? Or is this another occasion where we're getting theory shoved at us that has no chance at a decent implementation based on how hardware and software works these days?[/quote] Funny, I heard these exact same complaints for 5 years after Codd & Dates book came out, and mostly from people who had never read them. Thank goodness someone did read thier work and did implement it despite how hardware and software worked in those days, [b]or it never would have changed[/b].Sat, 15 Mar 2008 20:29:20 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Jeff Moden (3/15/2008)[/b][hr][quote]Compare that to where C/C++ programmers have been for over 15 years: they can write new code targeted to run on multiple different platforms right from the start AND expect it to work[/quote]So, there's absolutely nothing in C/C++ that is ever outside the standards amongst different vendors? I find that very difficult to believe in this very competative world.[/quote]Not at all what I said. Of course they have extensions in C/C++. However, they also implement the [i]full[/i] standard plus it is very well know and well documented what is standard vs. non-standard as well as what is re-targetable and what is not. That means that you really can implement high-performing, high-quality, full-featured applications using only standard C/C++ and fully expect them to compile for different environments and under different vendors' compilers.[quote]Standards are nice... but if you never have anything that's non-standard, then you have nothing new to become standard in the future. I'm going to keep pushing the envelope. ;)[/quote]You can do both, you know. :DSat, 15 Mar 2008 19:57:29 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxI am surprised (being introduced to other DBMS before SS) that the location of the predicate relating the summarised aggregate sub query and the table for update had any effect on the query execution. Whether expressed in the where clause of the correlated sub query referring back to the table being updated or returned from the derived sub query and specified in a join would have any effect on the query execution. For me they are different semantics for the same logical update.Thanks for pointing out that when the join predicate is more appropriate than a correlated sub query where predicate when the column is not covered by an appropriate index.Sat, 15 Mar 2008 19:22:29 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Jeff Moden (3/15/2008)[/b][hr][quote]Compare that to where C/C++ programmers have been for over 15 years: they can write new code targeted to run on multiple different platforms right from the start AND expect it to work[/quote]So, there's absolutely nothing in C/C++ that is ever outside the standards amongst different vendors? I find that very difficult to believe in this very competative world.Standards are nice... but if you never have anything that's non-standard, then you have nothing new to become standard in the future. I'm going to keep pushing the envelope. ;)[/quote]I am not willing to shell out money to get my hands on the actual SQL standard from ANSI, but considering some of the dreck that's been coming out of a lot of the so-called "working groups" (XPATH/XQUERY or CSS, to name a few) - I have to say that a healthy amount of skepticism ought to be applied to those "standards". I don't know how many of those drafts have come out where they just can't manage to put out a consistent document, and I keep hearing the same criticism about the "standard" SQL: heavy on syntax rules, but light on the semantics backing the syntax up. I mean - really. At what point did ANSI become the one and only authority on what SHOULD be in structured query language? (they're not since to the best of my knowledge - there are still 2 bodies trying to put out standards on SQL). And - who's out "proving" that the so-called experts are in fact proposing what really IS the very best way?Are they really that much better at figuring this out then the rest of us? Or is this another occasion where we're getting theory shoved at us that has no chance at a decent implementation based on how hardware and software works these days?Sat, 15 Mar 2008 19:08:38 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote]Compare that to where C/C++ programmers have been for over 15 years: they can write new code targeted to run on multiple different platforms right from the start AND expect it to work[/quote]So, there's absolutely nothing in C/C++ that is ever outside the standards amongst different vendors? I find that very difficult to believe in this very competative world.Standards are nice... but if you never have anything that's non-standard, then you have nothing new to become standard in the future. I'm going to keep pushing the envelope. ;)Sat, 15 Mar 2008 18:14:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote][b]Jeff Moden (3/15/2008)[/b][hr]Dang, Barry... good stuff but awfully long for saying that folks preach the standards right up to the point where the standards don't work for them and then they violate it. :P Heh... kinda like saying "Thou shalt not commit adultery [size="1"][i](unless you're really horney.)[/i][/size]" :D [/quote]Yes, but that's desire not necessity.[quote]And, no, I'm not talking about the difference between .10 and .11 seconds... on large batch stored procedures, I'm taking about the difference between 24 hours run time and 15 minutes for the same thing. [/quote]Then we are in agreement, I would do the same thing.[quote]The conversion of the code wasn't what took the time... it was the regression testing and that would have to be done even a 100% ANSI code migration (which simply doesn't exist). [/quote]Compare that to where C/C++ programmers have been for over 15 years: they can write new code targeted to run on multiple different platforms right from the start AND expect it to work. And their testing is all automated. That's where I want to be. And after 20+ years of SQL and standards, that is where we should be. It would vastly improve both the value and the importance of the work that we do.[quote]A good example is what Microsoft wrote themselves... take a look at the Information_Schema views and tell me there's enough information to write the likes of sp_SpaceUsed. [/quote]No, but then that is not really what INFORMATION_SCHEMA was targeted at by the ANSI committee. It's really about DDL and metadata, not about server and database administration. And so, it is entirely possible to drive an entire ERD system or a DW code generator from just the INFORMATION_SCHEMA views (and I have done the later). On the other hand, my operations automation procedures have to rely on the system objects and procedures, just like sp_SpaceUsed.[quote]A couple of things we do agree on is that Microsoft, like any company of their stature, does take the user requests into consideration a lot less than the users would like (although the uprising in support of FoxPro several years back seemed to work). [/quote]That one did suprise a lot of people. Mostly because so many others had failed under similar circumstances.Of course, there is nothing more empowering for users and customers than strong support for standards. If just two viable vendors were fully ANSI compliant, the OMB could start to push for compliance as a requirement for Federal purchases and THAT would change the whole ball game. But the big vendors recognize that that is not in their best intrest and so nothing happens.[quote]Of course, there's a lot of appathy on the part of users, too... look at the Poll for SP3 on 2k5... There's almost 3/4 of a million subscribers on the site and just a bit more than, what, a couple hundred votes on the Poll? That's disgusting.[/quote]Exactly the problem that we have with standards.Sat, 15 Mar 2008 17:40:43 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxDang, Barry... good stuff but awfully long for saying that folks preach the standards right up to the point where the standards don't work for them and then they violate it. :P Heh... kinda like saying "Thou shalt not commit adultery [size="1"][i](unless you're really horney.)[/i][/size]" :DMe? I'll tell you up front that whatever RDBMS I use, I'm going to use the tool that I think gets the job done with the best performance and scalability all the time and that usually doesn't involve anything close to ANSI Standard code even if it can be done using that standard because it'll usually be slower. And, no, I'm not talking about the difference between .10 and .11 seconds... on large batch stored procedures, I'm taking about the difference between 24 hours run time and 15 minutes for the same thing. I've only been in 2 migrations from SQL Server to Oracle and the resulting code conversions, but I can say that any attempt at writing ANSI code, usually at the expense of performance or scalability, is usually not worth it, especially considering how rare such migrations have been. The conversion of the code wasn't what took the time... it was the regression testing and that would have to be done even a 100% ANSI code migration (which simply doesn't exist). So, I don't even try.A good example is what Microsoft wrote themselves... take a look at the Information_Schema views and tell me there's enough information to write the likes of sp_SpaceUsed. I got tired of the lame-o output and wrote a version myself and it does the whole database at once. Do I expect that code to be able to withstand the test of time from 6.5 to 2008? No... just 2000 but, as it turns out, they put views in 2005 that are the equivelent of the system tables in 2000 so it still works just fine in 2k5. ;) If they don't put the same system views in, I can write a simple view from whatever their current system is and my code still works.A couple of things we do agree on is that Microsoft, like any company of their stature, does take the user requests into consideration a lot less than the users would like (although the uprising in support of FoxPro several years back seemed to work). Of course, there's a lot of appathy on the part of users, too... look at the Poll for SP3 on 2k5... There's almost 3/4 of a million subscribers on the site and just a bit more than, what, a couple hundred votes on the Poll? That's disgusting.Sat, 15 Mar 2008 14:42:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxAs to the matter of conformance to standards...[quote][b]Jeff Moden (3/14/2008)[/b][hr]I also said that code portability is a myth and some folks pretty much hammered on that for quite some time.[/quote]For the purposes of discussion it is useful to distinguish between [i]Standards Compliance[/i] which is a practice and [i]Portability[/i] which is a potential outcome and benefit of that practice. This is important because there are other potential benefits.[quote][b]Jeff Moden (3/14/2008)[/b][hr]...[b]HEH, THEN they turned around and admitted to using it a couple of paragraphs later.[/b] So, I ask you... out of all you folks that claim that portable code through the use of ANSI only commands should be the goal of every DBA and Developer, [b]how many of you actually pull that off[/b]? None? Maybe a couple of GUI programmers who never have to write batch code? That's kinda what I thought.[/quote]This does not fairly represent the position of those of us who support SQL standards, Jeff, as it confuses [i]striving[/i] for our goal with [i]attaining[/i] our goal. I have never known anyone who seriously thought that we should [i]only[/i] ever use standard SQL. We cannot, because we know the reality. SQL is not like C/C++, Java, etc., because unlike them [b][i]no one[/i][/b] truly implements the darn standards.Sure, some vendors are better than others, but even the best of them are woefully short, IMHO and that leaves us with large holes in what can be done with standard SQL in any specific implementation. Worse, many standards that are implemented are not optimized by the vendor as well as their own extensions (which is what happened to columnar subqueries in Updates before Sql2005).So, the position of myself and most others is "[i]Don't use non-complaint SQL, except where you [u]need[/u] to.[/i]" because we all recognize that sooner or later we have to use non-compliant SQL to fulfill the objectives set for us. The differences that we have are primarily over [i]where[/i] we draw that line.[quote]The non-Ansi extensions of each RDBMS engine are just too valuable and too powerful to not use.[/quote]As above, that is only half of the story. The other half is that because the vendors (not just Microsoft) are not implementing the Standards completely enough or of sufficient performance, that makes their vendor-specific extensions seem more valuable.[quote]I know of [b]no [/b]ANSI only "believers" that actually write ANSI only compliant SQL 100% of the time. They're just like the rest of us and will use whatever tools are necessary to get the job done.[/quote]There is a huge difference between "necessary" and "preferred". When there is a choice, we should choose to use the standard tools.[quote] So, give that tired ol' lecture a break, please. ;)[/quote]*sigh* Sorry, Jeff, I can no more give it up than I could give up trying to stamp out Cursors (and the people that I oversee say the same thing about that).[quote]And, if Microsoft ever deprecates the FROM clause in the UPDATE statement, I want you to know that me and about a million other people who write T-SQL in countries around the world are gonna march on Redmond and give Bill Gates and his crew the worst "pink belly" they've ever had for screwing up several billion stored procedures.[/quote]Been there, done that, with more people and when Microsoft (or a predecessor) was much weaker. It is shockingly ineffective. Also, it won't be a million people. Remember, because of the poor documentation, most people have been writing them wrong all along.Don't get me wrong, Jeff, 25 years ago I agreed with you and in fact made many of the very same arguments. And if it comes to that, I will be right out there with you. It just won't work. Why? Because while we are out there with our torches the powers that be will be looking out at us and saying to each other: "Who are they kidding? It is still far cheaper for them to change this one thing in their code than it would be for them to convert to another vendor."And why can they say this? Because: 1) None of the SQL-based products are conformant enough to achieve full portability. 2) This is because they don't feel enough pressure from us to make their products truly conformant. 3) and this is because we do not even use the standards that they have implemented.And this all highlights the most important aspect of this: Vendor-specific extensions benefit the Vendor far more than they benefit us (because it locks us into their products). Standards benefit us.And this is true even within the same vendor, over time. For instance, I started using the INFORMATION_SCHEMA tables as soon as they were available to me, in preference to the system objects wherever that was possible. Now, almost a decade later, every single clause I wrote that used INFORMATION_SCHEMA tables still works on the latest release of SQL Server (2005) and I expect them to still work on 2008.On the other hand, I have had to rewrite thousands of lines of SQL that touched system objects or other Microsoft-specific extensions because their names changed or their columns changed or their functionality changed or their location changed or whatever. INFORMATION_SCHEMA still works exactly as it originally did [u]because[/u] it is a [i][b]standard[/b][/i] and Microsoft can't "twiddle" with it the way that they twiddle with their own stuff.So that is why I say: - [font="Courier New"]coalesce()[/font] is better than [font="Courier New"]isnull()[/font], and - [font="Courier New"]cast()[/font] is better than [font="Courier New"]convert()[/font], [i]unless[/i] you need the format_style argument, and - column subqueries are better than FROM..JOINS in UPDATES, [i]unless[/i] there is a performance difference that is significant enough to matter to you.So if your UPDATE with Joins executes in 0.1 seconds and the same one with subqueries takes 10 seconds, then by all means, use the Joins.But if the Join version takes 0.10 seconds and the subquery version takes 0.11 seconds, then as far as I am concerned that is a clear win for the standards-compliant SQL.Sat, 15 Mar 2008 13:59:39 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspxBarry, you're correct... the single column update example yields nearly identical results for both query types in 2k5.[font="Courier New"]Correlated Subquery...Table 'TargetTable'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'SourceTable'. Scan count 1, logical reads 1490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(26 row(s) affected)(1 row(s) affected)SQL Server Execution Times: CPU time = 891 ms, elapsed time = 994 ms.======================================================================Update From...Table 'TargetTable'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'SourceTable'. Scan count 1, logical reads 1490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(26 row(s) affected)(1 row(s) affected)SQL Server Execution Times: CPU time = 890 ms, elapsed time = 1003 ms.======================================================================[/font]That being said, it doesn't look like the single correlated sub-query has any performance advantage over the UPDATE FROM and it looses in the 2 and 3 column foot races. Except for chasing some attempt at code portability or maybe some newbie to SS having an easier time of it, I really don't see an advantage in 2k5 to ever using a correlated subquery, not even for single column updates. In 2k, it does edge out the join method on a properly indexed table.I actually can't wait for the MERGE command to finally come out. I hope they do it right... I use it in Oracle to avoid sub-queries instead of Updates even if the query isn't meant to be an "upsert". I find they're easier to trouble shoot because they use joins instead of correlation. I dunno... maybe I'm just wierd :hehe: ... I find it's a lot easier to read, troubleshoot, and write joins than I do with correlated subqueries. Hard for me to believe that some newbie who's never written SQL would find a correlated sub-query easier to understand than a join.Anyway, thanks again for the feedback. You and the rest of the folks have been awesome as always.Sat, 15 Mar 2008 13:42:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic469155-203-1.aspx[quote]All I can say is “Not in My Experience.” Although Sql2000 did have some problems in this area (performance “bugs”, IMHO) these were mostly addressed in Sql2005.[/quote]Thanks for that tip, Barry. I'll check it out.Sat, 15 Mar 2008 13:18:56 GMTJeff Moden