Maximum number of CROSS APPLY functions
-
While we're on the subject, does anyone know of a reason there could be a performance hit from doing all these cross applies? I'm assuming it makes no real difference to the query plan.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (10/3/2016)
This is part of an ETL process I'm having to work on replacing. The existing process has rather complicated logic governing when columns can be updated in the target table. The logic varies from column to column, and there are over 100 columns. No I/O from other tables is required by any of the functions. (Yes, I know about case expressions, thanks guys.)Rather than writing and testing a multi-hundred line function with dozens and dozens of parameters, we are leaning towards a separate function for each column to be updated. These could be coded and unit-tested separately.
That's why I was asking. I'm doing the same thing right now and couldn't figure out why you wanted to do such a thing unless it was for the same purpose. Thanks, Bob.
p.s. Great minds think alike. 😉
--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)
Intro to Tally Tables and Functions -
Eirikur Eiriksson (10/3/2016)
Luis Cazares (10/3/2016)
Jeff Moden (10/2/2016)
The Dixie Flatline (9/30/2016)
Fortunately, I only need around 300, not thousands. 😀Thanks guys.
Just curious.... what are you doing that needs so many APPLYs?
He probably needs to make the server beg for mercy. 😀
Though it might be a case of dyslexia
😎
Being slightly perverse, whenever I need permissions I always ask for persimmons instead. 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
WHOA !! THOSE ARE LARGER THAN LIFE !!!!! :w00t:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (10/3/2016)
While we're on the subject, does anyone know of a reason there could be a performance hit from doing all these cross applies? I'm assuming it makes no real difference to the query plan.So long as they're not "cascading" CROSS APPLYs, you should be OK.
--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)
Intro to Tally Tables and Functions -
Jeff Moden (10/3/2016)
The Dixie Flatline (10/3/2016)
While we're on the subject, does anyone know of a reason there could be a performance hit from doing all these cross applies? I'm assuming it makes no real difference to the query plan.So long as they're not "cascading" CROSS APPLYs, you should be OK.
Got distracted by the massive orange permissions above. Far better than the miserable weedy specimens I found in Cyprus last week, although the feral figs and pomegranates were superb.
Yes, this is absolutely correct. If the APPLY blocks cascade, and especially if they cascade all the way down, then performance will be similar to that of a gnat learning to swim in molasses. If you want to know why, open up the properties sheet of the compute scalar of the last APPLY block. If you dare.
If your APPLY blocks don't reference tables either, then they will be extremely cheap. I've used this method for data cleansing in the past and it's very cool - maintainable, quick, easy to document.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Define cascading please. Output from itvf_1 becomes input to itvf_2 ??
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (10/4/2016)
Define cascading please. Output from itvf_1 becomes input to itvf_2 ??That's exactly it.
-
The Dixie Flatline (10/4/2016)
Define cascading please. Output from itvf_1 becomes input to itvf_2 ??http://qa.sqlservercentral.com/articles/T-SQL/97545/
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Good read, Chris. Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply