Halloween Protection

Question

Halloween Protection

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

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

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

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: 738589 More actions · Answer 2

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: 104768 More actions · Answer 3

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: 150468 More actions · Answer 4

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
All articles available on SQL.kiwi
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 738589 More actions · Answer 5

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 6

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: 150468 More actions · Answer 7

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
All articles available on SQL.kiwi
@SQL_Kiwi

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

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
All articles available on SQL.kiwi
@SQL_Kiwi