Asking for help to understand subtle nuances of randomised query.

Question

Post reply

Asking for help to understand subtle nuances of randomised query.

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

It's because of the uniqueness of this: ORDER BY new_id,base.id,base.colid

That uniqueness doesn't let it cache.

It's all about the uniqueness of the input into the Applied structure.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

Craig Farrell (5/26/2011)
It's because of the uniqueness of this: ORDER BY new_id,base.id,base.colid
That uniqueness doesn't let it cache.
It's all about the uniqueness of the input into the Applied structure.

Thanks Craig,

Yes, I understand that principal better now - that's why I changed the query to include that specific ORDER BY.

My questions now are:

1. Why does this query work on Denali, but not on earlier versions if the behaviour is "by design" - what have they changed in the "design"?

2. Why would changing the UNION to UNION ALL stop it working?

3. Why does CROSS APPLY break it also?

Thanks

SELECT

[RandomCoinTosses].[Result]

,[RandomCoinTosses].new_id

FROM

[sys].[syscolumns] AS [base]

OUTER APPLY

(

SELECT TOP 1 [Result] = [Val] , new_id

FROM (

SELECT [Val] = 'HEADS' ,newid() as new_id

UNION

SELECT 'TAILS' ,newid()

) AS [Source]

ORDER BY new_id,base.id,base.colid

) AS [RandomCoinTosses]

Edit: added question 3

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

mister.magoo (5/27/2011)
Yes, I understand that principal better now - that's why I changed the query to include that specific ORDER BY.

Ah, sorry. Misunderstood your confusion.

1. Why does this query work on Denali, but not on earlier versions if the behaviour is "by design" - what have they changed in the "design"?

At a guess, they improved the detection on column inputs. Order By doesn't really change the result, so it may have been a design flaw.

For example, this works:

SELECT

[RandomCoinTosses].[Result]

,[RandomCoinTosses].new_id

FROM

[sys].[syscolumns] AS [base]

OUTER APPLY

(

SELECT TOP 1 [Result] = [Val] , new_id

FROM (

SELECT [Val] = 'HEADS' ,newid() as new_id

UNION

SELECT 'TAILS' ,newid()

) AS [Source]

WHERE isnumeric ( base.id) = 1

AND isnumeric( base.colid) = 1

ORDER BY new_id

) AS [RandomCoinTosses]

However, including those columns into the SELECT or the ORDER BY doesn't (on 2k8). Since it didn't actually affect query results my guess is they originally didn't use a rerun and went to cache, simply resorting, or including column values, as necessary.

2. Why would changing the UNION to UNION ALL stop it working?

In Denali? No idea, don't have a copy. In 2k8, even using the ISNUMERIC() in the where clause, it's still cache'ing. Odd. I'm not sure that's one I can puzzle out from trial, error, and experimentation. Would need to get someone who's gotten down to the source code on these, I think. Good question for MS.

3. Why does CROSS APPLY break it also?

It doesn't for me, not with the isnumeric()'s. Did you leave the UNION ALL in there by accident?

This worked fine for me on 2k8:

SELECT

[RandomCoinTosses].[Result]

,[RandomCoinTosses].new_id

FROM

[sys].[syscolumns] AS [base]

CROSS APPLY

(

SELECT TOP 1 [Result] = [Val] , new_id

FROM (

SELECT [Val] = 'HEADS' ,newid() as new_id

UNION

SELECT 'TAILS' ,newid()

) AS [Source]

WHERE isnumeric ( base.id) = 1

AND isnumeric( base.colid) = 1

ORDER BY new_id

) AS [RandomCoinTosses]

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Not that it helps in the way you'd like, but the original code seems to work fine in SQL Server 2005, as well. Here's the first 20 rows...

HEADS

TAILS

HEADS

TAILS

HEADS

TAILS

HEADS

TAILS

HEADS

If I run it again, it runs fine again...

HEADS

TAILS

HEADS

TAILS

HEADS

TAILS

HEADS

TAILS

HEADS

TAILS

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)