February 24, 2010 at 6:11 am
Man, am I happy to not work in theory, but in the material world (reallity, or at least my experience of it). We can dream up a lot of wonderful constructs, but if they cannot be applied (direct or indirect) in the real world then it is just that, a nice idea without further meaning at that point in time. Be glad that in our case sets and relational thinking gave birth to useful applications. Changing their nature a little does add some good and bad and it might not be exactly the same beast afterwards. But that does not really matter as the new and improved does give real benefits, not just a theorethical ones.
Also keep in mind that every model, modified or not has to run on physical hardware and with a lot of limitations as a result. This makes things really complicated, as in math (let alone whole theories) you dont fully model the technology of the day. What seems superior in math, might end up inferior if directly applied/mapped in software. If I make the most brilliant sorting routine that takes the least amount of steps, but that has to big a working memory footprint, then it is forced to operate outside cpu cache too often and will get stumped by most routines that do work in cache but are inferrior in theory. The thing is that theory does not model everything and assumes things that are likely not true in the real world. In this example it was assuming that accessing memory, regardless of the accessing pattern, always has the same cost. Of course, relative performance can be quite different 10 or 20 years from now, so the theory is still useful as a guide where to put R&D efforts in.
I hope this example explans why naive implementations of superior theory can seriously loose out to experience based implementations in practice.
Models aren't perfect...never forget that, so never assume absolute truth based on a model as 'your truth' will likely burn you in the long run.
February 24, 2010 at 11:54 am
peter-757102 (2/24/2010)
Man, am I happy to not work in theory, but in the material world (reallity, or at least my experience of it).
I know how you feel - I learnt a lot and had a lot of fun from my decade working on a big research projects with a crazy mixture or British, French, and German academics and an even crazier mixture of Britsh, French and German industrialists, but I came back to the real world back in 96 and found it much better to be a real engineer again.
I hope this example explans why naive implementations of superior theory can seriously loose out to experience based implementations in practice.
Often the real problem is that the "superior theory" isn't - it is based on the wrong model, so it is inferior, not superior.
Models aren't perfect...never forget that, so never assume absolute truth based on a model as 'your truth' will likely burn you in the long run.
You have to have a model, or you get nowhere; but you have to model the right thing. To write good T-SQL you have to have a mental model of how SQL Server's Database Engine operates. That Database Engine is a real world object, so you had better be sure that your mental model is a model of that real world object. Your model should be evidence-based - you can collect useful evidence from many people who post here and/or write articles for SQL Server central, but you should always try to repeat their experiments and add some variations to get a feel for how things work and what effects small changes could have, and also to collect evidence by devising and carrying out experiments of your own. Your model should not be fixed because you may discover it's wrong and need to change it and because MS may decide to change the engine you are modelling so that you are forced to change the model to match - and as long as you remember that necessity for change you are unlikely to be burned by using a model. Yes, you could still be burnt because your model is incorrect, perhaps because your model becomes incorrect when MS changes something, and that is a good reason for always including code that checks results before applying them and is able to fall back to a different approach to the calculation that no longer works when it detects that it has failed to work. If you combine modelling the real world instead (or rather a decent abstraction of it) with elementary correctness checking, error detection, error containment, and error recovery you are very unlikely to get burnt. (This process of modelling, experimenting, and doing everything one can to detect and recover from errors has been applied through the centuries in many fields by competent engineers and scientists, it doesn't just apply to T-SQL programming but to just about any engineering activity.)
Relational algebra is fun mathematics, and can be a source of ideas about how to design a quasi-relational calculus, and even ideas about how to build an analytic engine to solve problems posed in that calculus, but it is not in any way a model of SQL Server's database engine. So if you tried to use that as your model you most certainly would get burnt.
Tom
February 25, 2010 at 7:06 pm
GSquared (2/23/2010)
Considering that hardware failures are possible, cosmic rays exist, and the laws of thermodynamics include the concept of increasing entropy in ordered systems, nothing any human being does can ever have fully guaranteed results with complete repeatability.
Thus, you should be fired from every job you might ever hold.
In the meantime, the rest of us will be satisfied with things that work with acceptable reliability in every circumstance they've been tested in, following readily specified and understood rules. After all, what's good enough for nuclear physics is good enough for software engineering.
Well said !!
September 2, 2010 at 6:13 am
Hi Jeff,
I bet we would love to see "groupings on repeated data such as "Start/Stop" indications over time (although there is a clever way to solve that which will be covered in a separate article in the future)" 😀
Also, is it possible to do Quirky update over a table variable? Is the update sequence guaranteed to be in the order rows were also inserted?
Thanks
September 2, 2010 at 7:32 am
boudinov (9/2/2010)
Hi Jeff,I bet we would love to see "groupings on repeated data such as "Start/Stop" indications over time (although there is a clever way to solve that which will be covered in a separate article in the future)" 😀
Also, is it possible to do Quirky update over a table variable? Is the update sequence guaranteed to be in the order rows were also inserted?
Thanks
I'm pretty sure that Jeff will disagree, but the update sequence is not guaranteed at all, on any kind of table.
There is a list of condition where, when met, noone has yet observed out-of-sequence updates. Several of those conditions were not on the list when this method was first published, but were added later (after someone did observe out-of-sequence behaviour and took the time to post a repro). Noone can guarantee that the list is complete now.
Anyone who claims any guarantees for this method is shutting his or her eyes to reality.
September 2, 2010 at 10:19 am
boudinov (9/2/2010)
Also, is it possible to do Quirky update over a table variable? Is the update sequence guaranteed to be in the order rows were also inserted?
You should probably never have enough rows in a table variable to make using the Quirky Update worthwhile.
That said, the literal answer to your first question is 'yes'.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 2, 2010 at 5:54 pm
Hugo Kornelis (9/2/2010)
boudinov (9/2/2010)
Hi Jeff,I bet we would love to see "groupings on repeated data such as "Start/Stop" indications over time (although there is a clever way to solve that which will be covered in a separate article in the future)" 😀
Also, is it possible to do Quirky update over a table variable? Is the update sequence guaranteed to be in the order rows were also inserted?
Thanks
I'm pretty sure that Jeff will disagree, but the update sequence is not guaranteed at all, on any kind of table.
There is a list of condition where, when met, noone has yet observed out-of-sequence updates. Several of those conditions were not on the list when this method was first published, but were added later (after someone did observe out-of-sequence behaviour and took the time to post a repro). Noone can guarantee that the list is complete now.
Anyone who claims any guarantees for this method is shutting his or her eyes to reality.
That's correct... it's undocumented and there's a list of conditions (rules) that you have to meet. No one has yet observed out-of-sequence updates if they've met ALL the rules. That's been since the earliest versions of Sybase and all versions, hot fixes, CU's, and SP's of SQL Server EVER including the current versions. And, yes, the rules needed to be changed to include all the rules because things changed over time including my understanding of a very valuable technique. EVERYONE (especially MS) changes the rules for one thing or another as time wears on so I don't know what you're crabbing about there.
Let's take a look at something that IS well documented and IS supposedly "guaranteed" to work. Let's look at WHERE somecolumn IS NULL. That didn't work back in SQL Server 2000 if just the right indexes where available and parallelism took place. In fact, the WHERE somecolumn IS NULL was totally ignored on occasion. I won't spend the time trying to find the hot fix again (you can if you want) but it took 40 people at my old company 10 days to repair the damage for something that was "guaranteed" to work and didn't.
Technically speaking, the Quirky UPDATE has worked correctly for longer than a WHERE clause criteria which is supposedly guaranteed to work and IS well documented. 😉
Now, the other thing is that I've demonstrated in this article just exactly how to test for a possible fault just in case you're the non-trusting type. I certainly don't mind people like you cautioning others in the technique... what the hell... I caution people on it as well. BUT, if you're going to badmouth a method that can't be made to fail if the rules are followed, then the least you could do is point that out. You should also point out that NOTHING in SQL Server, documented or not, is "Guaranteed" to work and that being documented doesn't make it any more likely that it is.
Noone can guarantee that the list is complete now.
So what? Stop being pissy on this. Take a look at the number of hot fixes and CU's that Microsoft has between SP's and tell the world why a list of rules for a technique should have any more of an absolute guarantee than anything Microsoft writes. And, yes, I'll update the rules if they change just like Microsoft does when it makes a change. 😉
And no... I can't guarantee that this will work as advertised any more than Microsoft could guarantee that the WHERE somecolumn IS NULL statement would work everytime. 😛 Heh... wait for it...:hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2010 at 6:05 pm
boudinov (9/2/2010)
Hi Jeff,I bet we would love to see "groupings on repeated data such as "Start/Stop" indications over time (although there is a clever way to solve that which will be covered in a separate article in the future)" 😀
Also, is it possible to do Quirky update over a table variable? Is the update sequence guaranteed to be in the order rows were also inserted?
Thanks
There's an awesome method in 2K5 and up for grouping repeated data such as "Start/Stop" indication over time. The method uses the difference between to set's of ROW_NUMBER()'s to pull the task off. In 2K, you'd have to either use a RBAR method or a Quirky Update to "smear" the data across groups.
Yes, it is possible to do a Quirky Update over a table variable. All you need to do is assign the clustered PK to the correct columns.
Is it guaranteed? Let's just say that it's worked properly longer than Hugo has. 😛 Could it fail someday in the future do to an update to SQL Server in one form or another? Absolutely... just like anything else... even some of the very well documented stuff that's "guaranteed" to work but they need to write fixes for because they sometimes break. Keep in mind that the backup plan for any such failure will be some form of loop. It might even be a loop for the update technique that I fixed for Hugo. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2010 at 6:11 pm
Paul White NZ (9/2/2010)
boudinov (9/2/2010)
Also, is it possible to do Quirky update over a table variable? Is the update sequence guaranteed to be in the order rows were also inserted?You should probably never have enough rows in a table variable to make using the Quirky Update worthwhile.
I don't remember what I did it for... I only remember "why". I needed to do a "previous row" calculation in a mlTVF in 2K... obviously, Temp Tables aren't going to work there. There weren't many rows for any given "run" of the function but there was no way I was going to write a Cursor or While Loop into the function. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2010 at 10:28 pm
Jeff - there is absolutely no need to take that tone with Hugo.
Hugo is correctly, I have also pointed out the foolishness of relying on this method, a method that is not supported by Microsoft.
The "product bugs" you mentioned did get support from Microsoft and were fixed.
I seriously hope anybody contenplating using this method will consider the wealth and longevity of mine, Hugo's and some of the other SQL MVP's considerable product and industrial experience and use a supported and safe method instead.
If this breaks your data - don't cry! Just go to Jeff Morden for support 😛
Tony Rogerson, SQL Server MVP
SQL Server product expert since 4.21a
September 2, 2010 at 10:59 pm
Jeff Moden (9/2/2010)
I don't remember what I did it for... I only remember "why". I needed to do a "previous row" calculation in a mlTVF in 2K... obviously, Temp Tables aren't going to work there. There weren't many rows for any given "run" of the function but there was no way I was going to write a Cursor or While Loop into the function. 🙂
Ah, good point, yes a multi-statement TVF- lucky I qualified my prior statement with 'probably' 🙂
Funny that Tony has popped up again - there's probably no point responding since heaps of people have already pointed out the flaws in his thinking in previous posts to this thread. It is a bit rich for him to seek to chastise you for 'taking a tone' with Hugo, given his own previous highly unprofessional remarks.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 2, 2010 at 11:09 pm
I wasn't going to respond because I knew I'd get grief for taking the opposite view to you Paul and Jeff and no doubt a few others that are proponents of this un-supported method.
Readers need only go back over the old threads to see the reality and context of your remark, labelling somebody unprofessional for pointing out the utter stupidity in using this method is quite frankly pathetic and only serves to highlight the weak position you have that forces you into personal insults but I'll leave it there.
Readers - please do check previous posts by myself, Hugo and others before playing with your employers most valued asset - their data, in the real world there are other fast but more importantly supported and stable approaches to this - be safe, be professional and use them!
If people want to email me directly on this subject with specific update problems they are trying to address then feel free: tonyrogerson@torver.net.
Many thanks,
Tony Rogerson, SQL Server MVP.
September 2, 2010 at 11:48 pm
tonyrogerson-498793 (9/2/2010)
I wasn't going to respond because I knew I'd get grief for taking the opposite view to you Paul and Jeff and no doubt a few others that are proponents of this un-supported method.
There are indeed very many smart people that disagree with you.
You have the right to hold your opinions, but that is all they are. The reason you get grief is simply down to the way you say what you do.
Readers need only go back over the old threads to see the reality and context of your remark, labelling somebody unprofessional for pointing out the utter stupidity in using this method is quite frankly pathetic and only serves to highlight the weak position you have that forces you into personal insults but I'll leave it there.
Let me help you out here, by quoting you directly:
tonyrogerson-498793
IF you are saying you are happy for this to be used in a production environment then frankly you should be sacked because it is clear and proven that its not repeatable behaviour that is GUARENTEED to work.
You think that's professional?
As far as flashing the MVP thing around is concerned: Argument from Authority
Still recommending the cursor? :laugh:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 3, 2010 at 2:03 am
Jeff Moden (9/2/2010)
No one has yet observed out-of-sequence updates if they've met ALL the rules. That's been since the earliest versions of Sybase and all versions, hot fixes, CU's, and SP's of SQL Server EVER including the current versions. And, yes, the rules needed to be changed to include all the rules because things changed over time including my understanding of a very valuable technique.
Some modifications of the condition list are indeed due to product changes. But not all.
I clearly remember the first time I saw an article written by you on this technique. (Not this article, but on this site - I'm not sure if it was your first article on this subject). I commented in the forum that the technique is unreliable, you challenged me to prove the unreliability, I invested a lot of time but did end up posting some code that observed all rules in that article, yet did the update out-of-sequence. To my surprise, that did not change your mind about the relliability of the technique - you acknowledged my finding, and then updated the article to include one more condition to the list that invalidated my example. I also seem to remember that someone else managed to break the technique as well in that forum topic, I'm not 100% sure and it's too much work for me to try and find it.
You may label this is a change of your understanding of this technique. But that is just word play. Fact is that you were as convinced of the technique back then as you are now, even though at least once, probably more often, your conviction has been proven unwarranted.
In this topic, I again manageed to break your code, by adding a specific WHERE clause (see page 11 - I had already forgotten about it, but decided to read back and then saw it again). I have also broken it by some other WHERE clauses in another topic. You have acknowledged this (on page 14, just before the discussion degraded to a mud-slinging contest between Tony and Paul). As far as I can see, the article has not yet been updated to reflect this. That might be because I did not respond to your request to provide feedback on your findings - I am sorry that I did not have the time then, and later lost track of the discussion again. I'll reread the relevant parts of the discussion and comment in my next post.
So your claim that "no one has yet observed out-of-sequence updates if they've met ALL the rules" at least required a side-note. People who observed only the rules that were known when they implemented this technique HAVE observed (and sometimes sufferred) out-of-sequence updates. Only those who were lucky enough (or clairvoyant) to observe even the rules that were added later (and the one you still have to add to invalidate my last counter-example) have observed out-of-sequence updates. Yet.
EVERYONE (especially MS) changes the rules for one thing or another as time wears on so I don't know what you're crabbing about there.
When MS changes rules (for instance, by changing the syntax of TOP n to TOP (n), or by changing the semicolon statement terminator from optional to mandatory) they document it, announce it explicitly in the Books Online section with compatibility notices, and keep accepting the old syntax for a few versions.
Yes, they did mess up with sp_makewebtask. But that is not how Microsoft generally works when changing or removing features.
Let's take a look at something that IS well documented and IS supposedly "guaranteed" to work. Let's look at WHERE somecolumn IS NULL. That didn't work back in SQL Server 2000 if just the right indexes where available and parallelism took place. In fact, the WHERE somecolumn IS NULL was totally ignored on occasion. I won't spend the time trying to find the hot fix again (you can if you want) but it took 40 people at my old company 10 days to repair the damage for something that was "guaranteed" to work and didn't.
And yes, of course a product as complicated as SQL Server will contain bugs. But if you ever encounter one, Microsoft is committed to finding the cause and releasing a fix as soon as they can. Not only for the party that encounteres the bug, but to all users in the form of hotfixes and service packs. Sure, a hotfix that has not yet been rolled up in a service pack can be a pain to find - but it's there. And if you don't find it and seek help from MS, they'll point you to it. I have never seen Microsoft reacting to a bug report by adding some conditions to Books Online and then claiming that noone who follows the rules ever encountered such a problem.
But who is the user of the quirky update technique to go to if they encounter a problem? MS won't help them (at least not without charging consultanncy fees); MS support will simply point out that the update sequence is not guaranteed and that the results are therefore not wrong.
Technically speaking, the Quirky UPDATE has worked correctly for longer than a WHERE clause criteria which is supposedly guaranteed to work and IS well documented. 😉
Since your bad experience with the WHERE clause was on SQL Server 2000 and the counterexample I posted to prove the unreliability of the quirky update was on SQL Server 2005, this is simply not true.
The fact that you added a condition later to "fix" the problem I found does not change the fact that I did break the technique without using any then published rules.
Now, the other thing is that I've demonstrated in this article just exactly how to test for a possible fault just in case you're the non-trusting type.
True. But the total time required for quirky update AND verification is, if I remember well, longer than the time required for my "set-based iteration" algorithm. And that is even without taking into account the optimizations you found for my code. (For which I am still very grateful).
Why would you ever recommend quirky update + verification if there is a faster technique available?
(In fact, I still wonder why you'd even recommend quirky update without verification, as the time difference with the "set-based iteration" method is minimal).
I certainly don't mind people like you cautioning others in the technique... what the hell... I caution people on it as well. BUT, if you're going to badmouth a method that can't be made to fail if the rules are followed, then the least you could do is point that out. You should also point out that NOTHING in SQL Server, documented or not, is "Guaranteed" to work and that being documented doesn't make it any more likely that it is.
I'm badmouthing the method because I feel that someone has to warn the people.
You are a great author and I'm pretty sure that, at least on this site, you have the number one reputation (and rightfully so). People listen to you, they take your advise very seriously. So if you recommend using a technique that is unreliable and that has in fact already been proven wrong, even when considering all current rules, someone has to try to warn the people what they are getting themelves into.
Noone can guarantee that the list is complete now.
So what? Stop being pissy on this. Take a look at the number of hot fixes and CU's that Microsoft has between SP's and tell the world why a list of rules for a technique should have any more of an absolute guarantee than anything Microsoft writes. And, yes, I'll update the rules if they change just like Microsoft does when it makes a change. 😉
Microsoft usually fixes bugs rather than adding rules and exceptions to the documentation.
Also, Microsoft has a means to ensure that customers get these fixes. Through CUs and SPs, and if the issue is urgent enough they can even put it on Windows Update so that people who use the default settings get it automatically pushed to their system.
How can you reach the hundreds, maybe even thousands of people who have followed their recommendation? Not all might read your next updated article. Some even might never have read the original article, because they copied the code from someone else's stored procedure.
And no... I can't guarantee that this will work as advertised any more than Microsoft could guarantee that the WHERE somecolumn IS NULL statement would work everytime. 😛 Heh... wait for it...:hehe:
The WHERE somecolumn IS NULL condition works now, as the bug has been fixed after it had been discovered.
The WHERE clause in the quirky update still breaks it. And you will never be able to fix it - the best you can shoot for is to exclude yet another bunch of scenarios by adding another condition to the growing list of rules.
September 3, 2010 at 3:32 am
Jeff Moden (2/7/2010)
Sorry this took longer than expected. Ah... looking back at the original post, I remember. What this proves is that my explanation as to why it works sucks and is in need of some revamping. 😛In the testing I just did, it would appear that the "source" in the execution plan may be more important than the destination.
That is correct. If I recall correctly, your article mentions the clustered index update as a requirement. But every UPDATE statement on a table with a clustered index will always include that operator - the clustered index is where the data is stored, so the only way for SQL Server to store the updated data is by adding the clustered index. You will ALSO see nonclustered index updates if there are columns in the SET clause that are included in the nonclustered index, or in the cllustered index (as the clustered index key is embedded in any nonclustered index).
I ran the following queries and only the ones that either had both a clustered index update and a clustered index scan or just a clustered index update with no additional source worked as advertised. Your good example uses a clustered index update but has a clustered index seek as a source and that's when things seem to go astray.
I don't know if you have the time, Hugo, but if you do, I'd sure appreciate your good verification so I can change the article to match. I'll also add the caveat that I don't fully trust execution plans and that the correct order of the update should still (always) be verified by using something like the verification code which is included in the article. Here's the code I used...
The actual problem is not that you can't trust execution plans, but that you can't trust execution plans to remain unchanged. Minor things, beyond your control, can cause the optimizer to recompile and possibly generate a new plan for your query. More on that below, on my comments to your four queries.
--===== This fails to work in the order of the clustered index.-- The "source" in the execution plan is a Clustered Index SEEK.
DECLARE @Counter INT
SELECT @Counter = 0
UPDATE dbo.TransactionDetail
SET @Counter = AccountRunningCount = @Counter + 1
FROM dbo.TransactionDetail WITH (TABLOCKX)
WHERE TransactionDetailID BETWEEN 120000 AND 120010
OPTION (MAXDOP 1)
The reason this fails is, indeed, that the optimizer creates a plan where the nonclustered index is used to find the data to be updated. The optimizer chooses to use this plan based on statistics and estimates.
When using the clustered index, all pages in the clustered index have to be accessed (as TransactionDetailID is not in the clustered index). But they are accessed in order, so read-ahead techniques can be used, and no page is ever accessed more than once.
When using the nonclustered index, only one or two pages of the nonclustered index have to be accessed (because the filter is on the indexed column), and one page in the clustered index for each row to be updated. The clustered index is now in random order (slightly slower on rotating media - still the norm for the optimizer), and pages might have be read and updated multiple times if multiple matching rows happen to be on the same page.
For only 11 matching rows, the nonclustered index read is favored. This remains true for larger numbers of rows. I just tested this and found that the sweet spot is somewhere between 100,000 and 200,000 estimated matching rows. This number depends on the number of rows in the table and the number of rows estimated to match the WHERE clause, so changes to the table data or even an update of stale statistics can change the execution plan of this query.
The examples I used in another discussion (which I don't remember very well, except that I do remember that one used WHERE column = xxx OR column = yyy OR column = zzz) basically were variations on the same technique - using a WHERE clause designed to make the selective nonclustered index seek plus (random order) clustered index update cheaper than the nonselective but sequential clustered index scan.
Time for an interesting intermezzo. I also tried what happens if you add this same WHERE clause to the quirky update to calculate both Running Totals and a Running Count that start over for each AccountID (figure 19 in the article). This query uses columns not included in the nonclustered index, so the plan that simply seeks the nonclustered index and updates the clustered index is no longer an option.
With the WHERE clause that matches 11 rows, the optimizer estimates that the cheapest option is to still seek from the nonclustered index, then use a key lookup to seek the rest of the data from the clustered index, and finally the obligatory clustered index update. But since this is more work than the simple nonclustered index seek used previously, the sweet spot where the optimizer changes to the clustered index scan changes - to somewhere between 2,500 and 5,000 matching rows on my computer. Note that this sweet spot partly depends on the ratio of rows/clustered index page vs rows/nonclustered index page. So in this case, not only changes in the data distribution and/or statistics can change the query plan; adding a column to the table, dropping a column, or changing a data type can now have the same effect. (And, in fact, it might even have that effect on the simple query above that did not require the additional key lookup - I did not test this so I'm not sure, but it might).
Okay, back to your second example:
--===== This works correctly in the order of the clustered index.-- It only uses a Clustered Index Update and has no separate "source".
-- After running the verification code to prove that it actually worked,
-- I would trust it in the future.
DECLARE @Counter INT
SELECT @Counter = 0
UPDATE dbo.TransactionDetail
SET @Counter = AccountRunningCount = @Counter + 1
FROM dbo.TransactionDetail WITH (TABLOCKX)
WHERE AccountID BETWEEN 50 AND 60
OPTION (MAXDOP 1)
The AccountID column is the first column in the clustered index. The fastest way to read the information for this query in the current versions of SQL Server is a clustered index seek. Apparently, the seek and the update can be collapsed into a single operator - I did not know this, but it is interesting information.
I don't think it's possible on any current version of SQL Server to break the quirky update with a WHERE clause that uses only the first column of the clustered index. I might be wrong though - and there might definitely be ways to do this in future versions of SQL Server. (And that includes not only major versions, but service packs as well - optimizer improvements have been included in service packs in the past, and might again be in the future).
--===== This works correctly in the order of the clustered index. HOWEVER,-- it uses a Clustered Index Update and a NonClustered Index Scan
-- on a different index. Since it's a NonClustereded Index Scan,
-- I wouldn't trust it even if the verification code said it worked
-- correctly because of the potential for a "Merry-Go-Round" index
-- in the future.
DECLARE @Counter INT
SELECT @Counter = 0
UPDATE dbo.TransactionDetail
SET @Counter = AccountRunningCount = @Counter + 1
FROM dbo.TransactionDetail WITH (TABLOCKX)
WHERE Date >= '20050701' AND Date < '20050801'
OPTION (MAXDOP 1)
Because the Date column is not the first in any index, no seek operator can be used. A scan is (at least in the current versions) the only option.
The reason the nonclustered index is scanned, is because the nonclustered index includes only the nonclustered and clustered index keys, whereas the clustered index includes all columns. Scanning the entire clustered index takes more time than scanning the entire nonclustered index, as the latter includes less data (and less fills less data pages).
The reason you still get "correct" results is that the optimizer estimates that there are sufficient rows that the cost to first sort the selected rows in an order to match the clustered index and then update the clustered index in sequential order (enabling read-ahead and preventing duplicate access to the same page) is less than the cost to keep the data unsorted and accept random order and potentially duplicate access to the clustered index. If you check the execution plan, you'll see a sort operator, and if you hover your mouse over it you'll also see that its order by list matches the clustered index.
I played around a while with the date boundaries, and I found that I still get "correct" results when I change the upper bound to 20050705, but the results get "incorrect" when I change it to 20050704.
--===== This works correctly in the order of the clustered index.-- It only uses a Clustered Index Update and a Clustered Index SCAN
-- as the "source".
-- After running the verification code to prove that it actually worked,
-- I would trust it in the future.
DECLARE @Counter INT
SELECT @Counter = 0
UPDATE dbo.TransactionDetail
SET @Counter = AccountRunningCount = @Counter + 1
FROM dbo.TransactionDetail WITH (TABLOCKX)
WHERE Amount BETWEEN 10 AND 20
OPTION (MAXDOP 1)
The Amount column is not included in any index. A clustered index scan is the only option currently available to find the rows to be updated. I don't think it's possible on any current version of SQL Server to break the quirky update with a WHERE clause that uses unindexed columns only. But with the same disclaimer I added to your second example: "I might be wrong though - and there might definitely be ways to do this in future versions of SQL Server. (And that includes not only major versions, but service packs as well - optimizer improvements have been included in service packs in the past, and might again be in the future)".
You may have noticed in my discussion of your examples that many of the decisions of the optimizer are based on things beyond your controle. Changes to the table schema are usually in your control - but are you sure you'll remember to revisit the running totals code if you have to add a varchar(500) Comments column to the TransactionDetail table? Yet that change might cause the optimizer to revisit the choices it made, and spew out a different plan.
Changes to the data are definitely beyond your control. The update with the Date filter for a one-month period works now. And since I had to go to a mere 4 days to break it, it will probably still work after doubling the amount of data in the table. But what when the data then doubles again? And again? If the business suddenly booms next year, July 2005 might become an insignificant footnote in the history of the company, with a number of rows that rounds to zero in the context of the entire table. Do you know at what number of rows the table has grown too large for the quirky update with a one-month filter to be safe? Have you set an alert to page you when 99% of that number is reached?
And changes to the indexes are of course a major factor here. I am sure that you, Jeff, will remember to retest all your quirky updates whenever you add, remove, or change an index. But be honest - what percentage of your readers who have built systems that rely on your recommendation do you expect to have the same self-discipline?
As a side bar, there's no practical reason to do any of these "WHERE" type of updates except for the one that uses "WHERE AccountID ..." which would explain why I didn't test them before the article.
Heh! I must admit that I was worried by the unrealistic nature of my examples. But the funny thing is that you just helped me fix that issue. Your third example uses a filter on the Date. I can see companies that have a huge history table, going back to the early eighties. Since this is stale data, running totals (which always restart at zero for each month) are precomputed and stored. But sometimes, someone finds an error in the archived data. And once the error has been discovered and fixed, the running totals have to be updatesd as well.
Now, if you have a tera-byte table with data running from January 1980 up to December 2009, and you just corrected a few erroneous rows in the April 2008 time frame, would it not seem a great idea to just use the quirky update technique with a WHERE Date >= '20080401' AND Date < '20080501' filter to recalculate the running totals for just that month without having to recalculate them for all other rows in the table?
That's not a good excuse... it's just the reason. Your query does just what it was supposed to do, though... show that just a Clustered Index Update is not a sufficient check to determine whether the "Quirky" Update had any chance of being successful or not.
No excuses needed, as far as I'm concerned. And I do of course understand the reason.
But don't you agree that this is exactly the danger of this method? SQL Server is such a complex product, and the optimizer is such an advanced beast, that it is impossible for us to consider all possible scenarios. Maybe, just maybe, a list of all relevant scenarios could be assembled by someone with access to the source code for the optimizer. But neither you nor I have that access, and I don't see Microsoft making SQL Server open source in the near (or even far) future.
Thanks for the help and your testing, Hugo. I'm looking forward to your feedback. Of course, if you don't have the time or the inclination, I'll understand that, as well.
Well, it took more time than it should have, but I hope I have manged to make you understand what went wrong - and why, as a result, the quirky update will always be unreliable.
Viewing 15 posts - 151 through 165 (of 307 total)
You must be logged in to reply to this topic. Login to reply