When NULL IS NOT NULL

Question

When NULL IS NOT NULL

Viewing 3 posts - 16 through 17 (of 17 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Peter Brinkhaus (7/29/2013)
mister.magoo (7/29/2013)
Of course, I could be wrong and will skulk away with my tail between my legs when Paul tells me I am wrong 😛
I guess Paul will tell you you're right! http://sqlblog.com/blogs/paul_white/archive/2012/09/05/compute-scalars-expressions-and-execution-plan-performance.aspx
To illustrate it, let's rewrite the first query:
WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
SELECT n, [Type], value=(ABS(CHECKSUM(NEWID()))%10000)/100.
FROM Tally a
CROSS APPLY ( /* OUTER APPLY changed to CROSS APPLY */
(
SELECT CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'a' END
UNION ALL
SELECT CASE WHEN CHECKSUM(NEWID()) > 0 THEN 'b' END
)
EXCEPT
SELECT NULL /* Added */
) c ([Type])
--WHERE [Type] IS NOT NULL /* Removed */
ORDER BY n;
Looking at the query plan, the UNION ALL/EXCEPT subquery is evaluated only once and all rows in the outer query are all joined with exactly 0, 1 or 2 rows.
Edit: poor example, because it does not really illustrate that the expression (Scalar Operator(CASE WHEN checksum(newid())>(0) THEN 'a' ELSE NULL END)); (Scalar Operator(CASE WHEN checksum(newid())>(0) THEN 'b' ELSE NULL END)) is not recomputed when checking for NULL. Anyway, Paul White's blog really seems to justify mister.magoo's presumption.

That's what I thought too, Peter, until I read this in the article: "In test four, we replaced the @@DBTS function with the side-effecting function CRYPT_GEN_RANDOM (the other such function is NEWID). One of the effects of this is to disable the deferred evaluation of the expression defined by the Compute Scalar." However, our expressions aren't defined by a Compute Scalar, they're defined - as far as I can see - by the Constant Scan...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

Just wanted you all to know I appreciate the research you've done and I haven't absconded from this thread. Merely lurking until I have something of value to add.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

I agree with ChrisM@Work, that there is nothing in the query plan explaining such extravagant behaviour of these queries.

From what I can see, it's only happens when you UNION ALL between results of some nondeterministic functions.

I would call it bug, but MS most likely will call it "SQL optimizer feature"

😉

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]