November 17, 2015 at 4:39 pm
Hugo Kornelis (11/17/2015)
I have never found or seen a method for computing running (pre SQL Server 2012) that has comparable performance to a well-programmed cursor solution, except for the methods that abuse undocumented and hence unreliable behavior - not safe for production use in my opinion.
You good, Sir, know MUCH better than that! YOU have never broken a proper working Quirky Update yourself and the additions that Tom Thompson and Paul White added have made it nearly bullet proof and it reports when it's not.
And please don't make the same mistake of calling improper use of the code to begin with a "Break" like you've done in the past. 😉 That's nothing more than preventing it from working to begin with.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2015 at 12:50 am
Jeff Moden (11/17/2015)
Hugo Kornelis (11/17/2015)
I have never found or seen a method for computing running (pre SQL Server 2012) that has comparable performance to a well-programmed cursor solution, except for the methods that abuse undocumented and hence unreliable behavior - not safe for production use in my opinion.You good, Sir, know MUCH better than that! YOU have never broken a proper working Quirky Update yourself and the additions that Tom Thompson and Paul White added have made it nearly bullet proof and it reports when it's not.
And please don't make the same mistake of calling improper use of the code to begin with a "Break" like you've done in the past. 😉 That's nothing more than preventing it from working to begin with.
Hi Jeff,
Allow me to shakup your memory. When you first posted quirky update, you had included a good handful of conditions in the article, and swore that those conditions made it safe. In the discussion thread, several people (including me) posted repros showing that it wasn't. Some time after that, you updated your article to include if I remember correctly about a dozen conditions for "safe use", and resumed claiming that the algorithms was safe and never broken as long as the conditions were observed.
At that point I stopped trying to find ways to break it. I have already proven how dangerous it is to rely on undocumented tricks. When it breaks, you can either choose to admit that this is indeed dangerous, or add an item to the list of already-known conditions that break it and retroactively call the repro improper use of the code. It is clear that we disagree on the right course of action here.
I do not remember who came up with the addition of an extra computed 'safety switch" column that will cause a runtime error when the update malfunctions, but that was the addition I already mentioned in my previous post - I assume that this is also what you refer to when you write "reports when it's not". I already acknowledged that this addition makes the algorithm, in your words, "nearly bullet proof". But "nearly" is a key word here. The behaviour is still undocumented and subject to change (and with the introduction of columnstore indexes and batch mode execution, I now actually can imagine theoretical scenarios where the safety switch might not trigger when it should - but these are sufficient unlikely that I can still agree with the "nearly bullet proof" qualifier).
So yes, in a situation where (a) performance is critical, *and* (b) SQL 2012 is not an option, *and* (c) a very low risk of incorrect results is acceptable, I would consider the quirky update as one of the possible answers. And as more and more companies upgrade to SQL 2012 or better, I personally will be very happy to see both the cursor version and the quirky update version of running totals wither and die.
November 18, 2015 at 6:13 am
Jeff Moden (11/17/2015)
Hugo Kornelis (11/17/2015)
I have never found or seen a method for computing running (pre SQL Server 2012) that has comparable performance to a well-programmed cursor solution, except for the methods that abuse undocumented and hence unreliable behavior - not safe for production use in my opinion.You good, Sir, know MUCH better than that! YOU have never broken a proper working Quirky Update yourself and the additions that Tom Thompson and Paul White added have made it nearly bullet proof and it reports when it's not.
So when it breaks, what do you do? Of course, you run the cursor. So to put this into production, you'd code the cursor anyways right?
The thing is that the quirky update does rely on undocumented behavior and its unsupported, isn't that enough for prudent folks to not put it into production? Was there ever any effort to persuade Microsoft to support the method, say like a connect item? Why is it unsupported and undocumented if the "properly working Quirky Update" has never been broken?
I'm guessing its a bit moot given the version is going away, but was just curious on why its not supported given that all of your fans seem to rave about it.
November 18, 2015 at 6:58 am
Hugo Kornelis (11/18/2015)
Allow me to shakup your memory. When you first posted quirky update, you had included a good handful of conditions in the article, and swore that those conditions made it safe. In the discussion thread, several people (including me) posted repros showing that it wasn't.
Not quite the way it went down, Hugo. I showed why it was safe and a couple of good folks showed how to make it even safer by adding code that would detect if it ever broke. Your personal attempts to demonstrate how it didn't work were akin to removing a WHERE clause from a DELETE. You didn't prove that it was unreliable... you kept it from working in the first place.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2015 at 7:56 am
I think that Jeff's and Hugo's recollections are going to be biased, so I think a link should be posted for the more objective thread participants, and we can put this important matter to rest!
November 18, 2015 at 8:01 am
patrickmcginnis59 10839 (11/18/2015)
Jeff Moden (11/17/2015)
Hugo Kornelis (11/17/2015)
I have never found or seen a method for computing running (pre SQL Server 2012) that has comparable performance to a well-programmed cursor solution, except for the methods that abuse undocumented and hence unreliable behavior - not safe for production use in my opinion.You good, Sir, know MUCH better than that! YOU have never broken a proper working Quirky Update yourself and the additions that Tom Thompson and Paul White added have made it nearly bullet proof and it reports when it's not.
So when it breaks, what do you do? Of course, you run the cursor. So to put this into production, you'd code the cursor anyways right?
The thing is that the quirky update does rely on undocumented behavior and its unsupported, isn't that enough for prudent folks to not put it into production? Was there ever any effort to persuade Microsoft to support the method, say like a connect item? Why is it unsupported and undocumented if the "properly working Quirky Update" has never been broken?
I'm guessing its a bit moot given the version is going away, but was just curious on why its not supported given that all of your fans seem to rave about it.
"So when it breaks..."
That's a bit like saying "So when you use the new heavily documented and supported VARDECIMAL feature, what are you going to do when they deprecate it and remove it"?
The thing is, the behavior is documented but, as with many other things in BOL, it's improperly documented. MS says in the UPDATE documentation that the output isn't reliably predictable. If you believe that, then I agree with you... don't use it. I suspect the reason why it's unsupported is because they're the ones that think it isn't reliably predictable. But, it IS a bit of a moot point now that 2012 is out although, being 5 times slower than a Quirky Update, it's a real shame that they botched it when it comes to performance.
As to using undocumented features... MS has had several very well documented features go south on them (MERGE comes to mind along with the REBUILD of indexes in an online fashion corrupting data in 2012). As for the idea of an undocumented feature going away at any time, there are many examples of well documented, well used features going away. Of course, if you need support on something, you shouldn't use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2015 at 8:03 am
I do not want to go into a mud-slinging contest or in any play on words. I value you as a person, and I value your contributions to this site and to the SQL Server community in general. But I do want to make sure that people are aware of the facts about quirky update. And these facts are:
1. The "quirky update" method is not documented (and in one KB article, Microsoft even explcitly warns that a very similar technique can produce unexpected results).
2. When you originally posted your first quirky update article, there was a short list of conditions that, according to you, guaranteed correct results. In the discussion that followed, many people showed code that stayed within those conditions, yet produced incorrect results. This eventually resulted in you pulling the article.
3. You later posted a new version of the article, with a much longer list of conditions - based on the feedback to your first version. And yet, there again were comments with code that observed all the conditions in the new list but still produced incorrect results. In the discussion that followed, people introduced a technique that tries to ensure that you will not get incorrect results by throwing a run-time error if you would. (As I said before, a technique that I considered reliable at that time, though I am not 100.000% sure if that still is the case when batch mode execution is used).
When you posted your first article, you were confident that your list of conditions was complete - but you apparently had overlooked some situations.
When you posted your second article, you were once more confident that your revised list was complete, and that the technique was now safe. And again it turned out that you had overlooked some situations.
Perhaps you have once more updated the list of conditions. Perhaps you haven't. To me, that is irrelevant. What matters is that history has proven that you are not infallible, and that your claims of quirky update being safe and reliable have been disproven twice. Why should I believe that this cannot happen a third time?
The quirky update method is not terrible. It has its merits, and there may be places where it is the best solution. But anyone who choses to implement this method has to be aware of the risks, and be willing to accept this.
Any claim that quirky update is safe and reliable is simply wrong.
November 18, 2015 at 8:32 am
Jeff Moden (11/18/2015)
patrickmcginnis59 10839 (11/18/2015)
Jeff Moden (11/17/2015)
Hugo Kornelis (11/17/2015)
I have never found or seen a method for computing running (pre SQL Server 2012) that has comparable performance to a well-programmed cursor solution, except for the methods that abuse undocumented and hence unreliable behavior - not safe for production use in my opinion.You good, Sir, know MUCH better than that! YOU have never broken a proper working Quirky Update yourself and the additions that Tom Thompson and Paul White added have made it nearly bullet proof and it reports when it's not.
So when it breaks, what do you do? Of course, you run the cursor. So to put this into production, you'd code the cursor anyways right?
The thing is that the quirky update does rely on undocumented behavior and its unsupported, isn't that enough for prudent folks to not put it into production? Was there ever any effort to persuade Microsoft to support the method, say like a connect item? Why is it unsupported and undocumented if the "properly working Quirky Update" has never been broken?
I'm guessing its a bit moot given the version is going away, but was just curious on why its not supported given that all of your fans seem to rave about it.
"So when it breaks..."
That's a bit like saying "So when you use the new heavily documented and supported VARDECIMAL feature, what are you going to do when they deprecate it and remove it"?
But only a bit right?
The thing is, the behavior is documented but, as with many other things in BOL, it's improperly documented. MS says in the UPDATE documentation that the output isn't reliably predictable. If you believe that, then I agree with you... don't use it. I suspect the reason why it's unsupported is because they're the ones that think it isn't reliably predictable. But, it IS a bit of a moot point now that 2012 is out although, being 5 times slower than a Quirky Update, it's a real shame that they botched it when it comes to performance.
We are stuck with that because Microsoft implements the product. We get stuck with whats documented, supported, or if we so choose, what we determine to be so based on evidence we gather or reliable trusted third parties gather.
I like the entire quirky update thing, when I ran across it, I gained some insight into what t-sql ran fast and what didn't which is a subject I'm interested in. But unlike many here, for me, correctness trumps performance, and so in my case, I'd probably just go with the best cursor or non T-SQL solution I could come up with.
But still, its my understanding that, if you implement a quirky update, you also implement a flag for failure and if you want your job to still finish in an automated fashion, you subsequently run a cursor if that flag flips right? So isn't even implementing quirky update still a case for cursor code, even if mostly it sits idle?
As to using undocumented features... MS has had several very well documented features go south on them (MERGE comes to mind along with the REBUILD of indexes in an online fashion corrupting data in 2012). As for the idea of an undocumented feature going away at any time, there are many examples of well documented, well used features going away. Of course, if you need support on something, you shouldn't use it.
The difference is whether quirky update was ever on the list of issues to fix (ie., become supported). I hear you about those MERGE and REBUILD bugs, but by virtue of being supported, they would also land on an internal "to fix" list.
Anyways, I found a link which inside the article links to earlier discussions.
November 18, 2015 at 10:21 am
No. Like any other software, you find out what went wrong and fix it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2016 at 12:49 am
I think this is a good post. Cursors are good when use right for the right process. But I have seen some when badly written help with the myth that they are evil.
September 9, 2016 at 2:10 am
It's not so much a case for cursors as education by/for designers in using the right tools for a given use case.
Most developers begin with a row-by-row education then progress to set based so it's easy to default to using cursors as it suits the mind-set.
I remember the early days of VB3 and databases where a typical developer would ask ' just present the data as a single table and I'll handle it in (row-by-row) code'. Education.
September 9, 2016 at 2:57 am
Hi
nobody is arguing with the fact that cursors should never be used when a set based operation can be used.
What the author is saying is that the mantra "Never use cursors" is incorrect.
I think this mantra is usually just repeated by people who don't understand that cursors are a very powerful tool to use when appropriate
Mike
September 9, 2016 at 3:58 am
I like your post: Each command has it's right to exist, and WE have to decide the appropriate use.
Your compilation of criteria is a nice catalogue for a discussion when or when not to use cursors...
________________________________________________________
If you set out to do something, something else must be done first.
September 9, 2016 at 4:55 am
You would strengthen your argument by adding non Microsoft examples of when a cursor is an appropriate option. The only time I have personally found cursors a better option has been in some maintenance routines.
September 9, 2016 at 5:25 am
I often use them with dynamic SQL.
I recently wrote a proc to search every column in every table in a database for a certain value and obviously I didnt want to manually code for about 10,000 different columns in 500 tables
Sometimes I need to create pivot tables so I wrote a generic routine which will go through the columns in a table and dynamically create the SQL for the pivot.
I have also written some systems where users entered SQL "rules" through a GUI which are stored in a table and then executed on the server so I use a cursor to select which rules and in which order and go through them then execute the dynamic set based SQL and return info like number of rows affected and write that back to the "rules" table.
Another use was a generic routine to compare every value in every field in two tables without needing to specify column names.
So often I use cursors with dynamic sql and frequently with sp_executesql. Once again I must stress I never use them for set based operations
Viewing 15 posts - 166 through 180 (of 215 total)
You must be logged in to reply to this topic. Login to reply