Halloween Protection

Question

Halloween Protection

Viewing 10 posts - 46 through 54 (of 54 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

craig 81366 (11/7/2012)
Only one of us resorts to insults, name-calling and personal abuse to 'make his point'.

So your

craig 81366 (11/1/2012)
I find your flippancy and stubbornness quite annoying. If you're not going to be open-minded enough to actually put thought into what I have to say, I can't be bothered to waste any more time explaining it to you.
I do enjoy a good debate and appreciate logical argument, but presenting unfounded rubbish as fact gets up my nose as much as historical inaccuracies seem to get up yours.

is all good standard debate, is it? Some of it looks remarkably like personal abuse to me. My view is that you started dishing it out and so I started handing it back, so now we are both doing it (which is rather sad) and your "only one" is therefore inaccurate since we are two.

L' Eomot Inversé (11/5/2012)
(to use the style of personal abuse that you have chosen to introduce into this dialogue)
Only one of us uses outright blatant lies to justify his "uncivilised" behaviour.

and the passage I just quoted suggests that the only one using a lie is you, since your "only one" is clearly intended to designate me, and the quoted passage shows you at it. But rather than calling you a liar I would call it a mistake you made in the heat of the moment. Accusing you of "outright blatant lies", whether because I disagree with you or because what you say is plainly untrue or both, would attribute to you a motivation for which there is no evidence - and I won't take personal abuse down to that level (and would have more respect for you if you would refrain from it too).

craig 81366 (11/7/2012)
L' Eomot Inversé (11/5/2012)
I could carry on by introducing an attack on your method of calculating averages
Only one of us specifically avoided providing argument against the content of the other's post and instead chose to direct attacks at the other person.

You are repeating yourself, and just as mistakenly as before.

craig 81366 (11/7/2012)
By the way, I would be very interested to learn how you intend improving on the mathematically provable minimum number of required addition operations, and a single division operation.

You perhaps didn't notice that I said that the technique works only under specific circumstances, not that it would never work. The reason it doesn't work under other circumstances is that it makes no attempt to control the cumulative error, which can be very important when there are millions of additions. The specific circumstances in which it works are that there is a datatype capable of representing exactly every datum and every intermediate result (there will probably be error introduced by the division operation even then, but there's no escaping that except by luck and that is in any case a small rounding error introduced by a single divide op). So, for example, if you have 10 million numbers with an average value of about 10^29 you can't do it because the sum will overflow numeric(38,0), bigint, and any other exact numeric type SQL or most other languages have - you have to use FLOAT to do the arithmetic. So now you are into inexact numerics, and have introduced representation errors as SQL uses the wrong floating point base for accurate representation of our usual decimal number system, so you don't want to introduce yet more errors, and you will use some error reduction technique which is likely to involve something like sorting the numbers (on magnitude, ignoring sign) and eliminating cancelling pairs before doing any arithmetic other than picking up the count to divide by, and then doing the additions in the sorted order (increasing order of magnitude). SQL Server will use the error-ignoring method even when the original data is floating point, which is unfortunate and is why some scientific and engineering projects either pull the data out of the database to compute aggregates, instead of using the built-in capability, or write their own DBMS that doesn't have the problem.

craig 81366 (11/7/2012)
L' Eomot Inversé (11/5/2012)
I could be even nastier with your method of recalculating the average as elements are deleted, ... to be done by the method you suggest
Only one of us deliberately misrepresents the other's statements in order easily attack them. (See http://en.wikipedia.org/wiki/Straw_man)
To reiterate, the only reason I presented the technique was to illustrate your error in assuming such a calculation would need "an awful lot of compute power to spare".

And the technique doesn't work because, again, it does no error reduction when it has to deal with inexact numerics. Of course it would work any time computing the average using the no-error-avoidance worked because everything could be kept exact and would still have worked if the data had been presented in the inverse order to its subsequent removal. But when inexact arithmetic is needed, or when the original data is held in an inexact format, unless the values are removed in the inverse order to that in which they were added to the total when calculating the average of all vales, it could lead to very inaccurate results indeed. I don't think anything I said misrepresented anything you wrote. We both know perfectly well that MS doesn't use that technique for calculating a modified average, because they don't compute modified averages at all. If one wanted to compute them with reasonably small errors then a lot of resource would be required, because after some threshold of values had been removed one would need to compute the average from scratch to stop accumulating error, and that computation involves (as we are looking at inexact data) a sort of all the (remaining) data. So my statement that an awful lot of compute power would be required was perfectly correct (unless one arbitrarily refuses to take account of the need to do error reduction when handling inexact values). I don't think my comments about the error-accumulation potential of the method were in any way misrepresenting what you said, in fact I can't imagine how you could interpret them as doing so, and at first I took your use of the word "deliberately" in that context as an intentionally vicious unjustifiable slur. On cooler reflection, I shall just regard it as another unfortunate mistake in the heat of the moment.

craig 81366 (11/7/2012)
Only one of us resorted to quoting his education background, experience and work history in an effort to give credibility to his argument.

I described some work background because it is clearly relevant to whether I'm aware that producing and using a spool can be an expensive operation - something you had claimed, with no apparent justification at all, that I clearly didn't understand. I mentioned some education background to indicate how old the spool concept is - since you appeared to think it was something newly invented for databases at some time in the 70s.

Only one of us has made comments suggesting an ageist prejudice.

I must have missed that one somehow - which message was it in?

Only one of us has made statements insinuating the other is "stupid".

Well, as far as I can see neither of us has actually applied the word "stupid" to the other. Several things you have said clearly suggest (and have clearly been intended to suggest) that I'm an ignorant and arrogant person who pays no attention to other people's arguments, and as I would regard anyone who displayed those qualities as utterly stupid that I think is the nearest either of us has come to insinuating stupidity.

You're right: Only one of us chose not to "mix the civilised part of this exchange with the uncilvilised part"; but then again, only one of us chose to write an "uncivilised" post at all.

Well, I disagree. You posted a comment with some very offensive and uncivilised remarks in it, and I then responded in kind. Unlike you I recognised that I was being uncivilised (although I don't think I ever got down quite to your level - that "deliberately misrepresents" really is the pits, "not going to be open-minded enough to actually put thought into what I have to say" was almost as bad, and "outright blatant lies" is extremely nasty too - you won't find anything as offensive as any of those in my comments.

Anyway, I'm convinced this isn't the proper forum for a flame war, so I don't think I'll particpate in any more of this silliness.

Tom

craig 81366 SSC Eights! Points: 808 More actions · Answer 2

L' Eomot Inversé (11/7/2012)
use some error reduction technique which is likely to involve something like sorting the numbers (on magnitude, ignoring sign) and eliminating cancelling pairs before doing any arithmetic other than picking up the count to divide by, and then doing the additions in the sorted order (increasing order of magnitude). SQL Server will use the error-ignoring method even when the original data is floating point, which is unfortunate

I'd like to thank you for those tips on how to reduce cumulative error when you can afford the overhead of an index or sort operation.

However, I was under the impression that the primary database platform in our dicusssion is MS SQL Server.

And since by your own admission, SQL Server uses a conceptually similar technique to mine, it appears your argument was nothing short of a Red Herring.

It could be said that you've been somewhat disingenuous.

L' Eomot Inversé (11/7/2012)
and I won't take personal abuse down to that level

You've already illustrated the level you're capable of taking personal abuse down to.

In your previous post (with your obviously well-read, broad and colourful vocabulary) you have already taken personal abuse to a far lower level than the one you now profess to be avoiding.

So yet again, you're being completely disingenuous.

L' Eomot Inversé (11/7/2012)
But rather than calling you a liar I would call it a mistake you made in the heat of the moment.

I might have considered the possibility that your false accusation that "I chose to introduce personal abuse" was merely a mistake on your part.

However, I find it extremely difficult to believe that someone who possesses the grasp of the English language you clearly have would not understand the significant difference personal abuse and attacks on utterly unfounded rubbish statements.

Furthermore, you seem to be the kind of person who tries to be precise and accurate with vocabulary and terminology.

The kind of tangential far fetched leap that's required to label my statements as personal abuse is quite a long stretch further than: An UPDATE statement might use an eager spool to protect it's operations from the side-effects of rows that have already been updated is similar to a DELETE statement that might need to use an eager spool to protect it's operations from the side-effects of rows that have already been deleted.

On the other hand, since you explicitly stated that you're using it as the reason for your personal abuse, I see no other explanation than you deliberately lied to create a fallacious justification to start personal abuse.

I still consider your statement to be an outright blatant lie. (Yes an attack on the statement, not personal abuse - but if the shoe fits, you're welcome to wear it.)

L' Eomot Inversé (11/7/2012)
So your
craig 81366 (11/1/2012)
I find your flippancy and stubbornness quite annoying. If you're not going to be open-minded enough to actually put thought into what I have to say, I can't be bothered to waste any more time explaining it to you.
I do enjoy a good debate and appreciate logical argument, but presenting unfounded rubbish as fact gets up my nose as much as historical inaccuracies seem to get up yours.
is all good standard debate, is it? Some of it looks remarkably like personal abuse to me.

Well, obviously those sentences are riddled with insults and name-calling. If a two-dimensional square looks like three-dimensional sphere, then perhaps it can be considered personal abuse.(sarcasm off)

* If you took offence at me describing one of your statements as "unfounded rubbish", might I remind you that you had previously described statements of mine as "nonsense". (You were wrong, but at least you were still being civilised.)

* If you took offence at me describing your behaviour as stubborn, then perhaps you'll take heart in hearing that I too am stubborn, but I have no qualms about it:

I will stubbornly defend what is right, and stubbornly oppose what is wrong - which is the only reason I'm still replying.

Quite frankly, I couldn't care less if you're Babbage, Date, Einstein, Turing, Newton and a hundred other geniuses rolled into one: If you make a rubbish claim, I'm going to challenge you on it. And I'll back the challenge with relevant and tested/verified information.

You're welcome to rebut my challenge, but just because you discover your claim is indefensible is no cause to get nasty.

I'll reiterate, your statement that "Because a displayed MS SQL query plan only shows spool operations when they are not blatantly obvious (which I think is eminently sensible); no other reason." is utterly unsubstantiated, unfounded rubbish! There's that significant difference between attacking a flawed statement/argument (as I have done) versus attacking the person through insults and name-calling (as you have done).

The following image shows a comparison between two query plans.

The only difference between the two queries is that the first uses a self-join to determine which rows to delete, and the other joins to a second table.

I ensured that #T2 was identical to #T1, data included.

If there weren't a spool operation in the first plan, then it would be possible for the DELETE operation to behave differently depending on the order in which the rows are checked for deletion.

* Going forwards through the sample data would result in all rows being deleted.

* Going backwards through the sample data would result in only the first row being deleted.

Clearly the spool operation in the first plan is essential for consistent intuitive and predictable behaviour.

I agreed to disagree about whether to call this "Halloween Protection", but you're welcome to suggest another name if you'd like.

You claimed that "MS SQL query plan only shows spool operations when they are not blatantly obvious". So:

* Is the essential spool operation in the first plan less 'blatantly obvious' than the one you seem to think is in but merely not shown on the second?

* Don't you think it's fascinating that the "hidden because it's blantantly obvious" spool in the second plan seems to incur significantly less overhead than the spool in the first plan?

What is blatantly obvious is:

* that you never bothered to experiment for yourself with any of the queries I provided.

* you never bothered to check, verify or substantiate your utterly rubbish claim.

* you expected me to take at face value that your claims were valid and correct based purely on your past experience/work/education/indirect association with System R.

And yet you had the cheek and audacity to slander me with:

L' Eomot Inversé (11/5/2012)
you are not just an ignoramus but an arrogant and self-important ignoramus

Perhaps you should take a long hard look in the mirror?

On the off-chance that you might, for a change, actually bother to investigate futher than your past recollections, here are all the queries I used to check the above plans. You'll note the inclusion of the AVG query that's been under discussion - you might find its results even more interesting than the ones I've shown.

CREATE TABLE #T1 (

COL1 int,

COL2 int

)

INSERT INTO #T1

SELECT 1,2 UNION ALL

SELECT 2,3 UNION ALL

SELECT 3,4

CREATE TABLE #T2 (

COL1 int,

COL2 int

)

INSERT INTO #T2

SELECT 1,2 UNION ALL

SELECT 2,3 UNION ALL

SELECT 3,4

DELETE #T1 WHERE COL1 < (SELECT AVG(COL1) FROM #T1)

DELETE #T1 WHERE COL1 < (SELECT AVG(COL1) FROM #T2)

DELETE #T1

WHERE COL1 NOT IN (SELECT COL2 FROM #T1)

DELETE #T1

WHERE COL1 NOT IN (SELECT COL2 FROM #T2)

DROP TABLE #T1

DROP TABLE #T2

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

SQL Kiwi (11/6/2012)
Steve Jones - SSC Editor (11/1/2012)
Interesting discussion. Freedman does mention inserts and deletes, but he never wrote on the subject. Perhaps he was mistaken, perhaps he hasn't had time. The KB also mentions it, but it could be standard wording and not specific application to inserts/deletes.
For now, I will stand by Halloween protection being known as applying to updates.
Did you read http://support.microsoft.com/kb/810026?

Nope, didn't see that one. So Deletes are documented and known.

I'll change the question. Happy to add inserts if I have a reference. Perhaps a certain, very smart, well known, New Zealand Kiwi will blog something and prove it.;-)

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Craig, it's clear that we each have our own viewpoint on what a spool operation is and whether all operations carried out by SQL server show up in query plans, and we are not going to agree on those things any more that we are going to agree on what the original meaning of "Halloween Problem" was and whether the adoption by some people of a broad meaniong should has led to a situation where that broad meaning is standard. I didn't want to get into a personal conflict, and I certainly didn't set out to give offence, but it's clear that you were offended by what I wrote, and I have to accept responsability for that effect of what I wrote, so I'm sorry this discussion has turned out as it has, and I apologize for any offense given.

Tom

Paul White SSC Guru Points: 150467 More actions · Answer 5

Steve Jones - SSC Editor (11/9/2012)
SQL Kiwi (11/6/2012)
Steve Jones - SSC Editor (11/1/2012)
Interesting discussion. Freedman does mention inserts and deletes, but he never wrote on the subject. Perhaps he was mistaken, perhaps he hasn't had time. The KB also mentions it, but it could be standard wording and not specific application to inserts/deletes.
For now, I will stand by Halloween protection being known as applying to updates.
Did you read http://support.microsoft.com/kb/810026?
Nope, didn't see that one. So Deletes are documented and known.
I'll change the question. Happy to add inserts if I have a reference. Perhaps a certain, very smart, well known, New Zealand Kiwi will blog something and prove it.;-)

Hi Steve,

I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:

http://support.microsoft.com/kb/248441

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 6

SQL Kiwi (12/9/2012)
Hi Steve,
I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:
http://support.microsoft.com/kb/248441

Thanks, I'll update things.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Steve Jones - SSC Editor (12/10/2012)
SQL Kiwi (12/9/2012)
Hi Steve,
I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:
http://support.microsoft.com/kb/248441
Thanks, I'll update things.

Had to laugh when I read this. "I'll update things" in a thread about Halloween Protection. 😀

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 8

GSquared (12/10/2012)
Steve Jones - SSC Editor (12/10/2012)
SQL Kiwi (12/9/2012)
Hi Steve,
I did not receive a notification for this post (or perhaps I didn't see it) so apologies for the late reply. I have been meaning to write something around HP for a while, so yes I will take up that challenge. In the meantime, here is a KB reference for a SQL Server 7.0 bug with self-referencing INSERTs with a lack of proper HP as the root cause:
http://support.microsoft.com/kb/248441
Thanks, I'll update things.
Had to laugh when I read this. "I'll update things" in a thread about Halloween Protection. 😀

I hope he has scheduled the update for October 31 :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

Steve Jones - SSC Editor (11/9/2012)
I'll change the question. Happy to add inserts if I have a reference. Perhaps a certain, very smart, well known, New Zealand Kiwi will blog something and prove it.;-)

Completed:

http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-1

http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-2

http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-3

http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-4

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi