Maximum number of CROSS APPLY functions
-
I can't find a maximum number for CROSS APPLY lines in a single query. I know this has to be documented *somewhere*. Can anyone point me in the right direction?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
I believe it falls under "Limited by available resources."
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Oh that's a BIG help.
I guess it will tell me when I've gone too far.
Thanks anyway, Drew.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
I was curious about this too and did a little research. There appears to be absolutely nothing about this out there about APPLY. Since APPLY is very similar to a JOIN (joins and APPLY are both table operators with respect to logical query processing) so I looked up the maximum number of joins.
In SQL Server 2005 and earlier the number of JOINs was limited to 256 tables[/url]. In 2008+ it's limited to the "Limited only by available resources" (Note where it reads, "Tables per SELECT statement").
I know this does not answer your question but it's all I could find. I'm going to play around with some dynamic SQL and see if I can find a limit.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Well just for fun I tried it with over 2000 cross applies and it runs sort of, it managed to fill up the tempdb just generating the estimated execution plan before I killed it after 20+ minutes.
So yeah I'd go with resource limited.
-
ok, quick update. There does not seem to be a limit (I know you can do 2048 for sure).
For fun, play around with this code:
-- one time setupUSE tempdb
GO
CREATE TABLE dbo.t1 (someid int identity primary key, somenbr int);
CREATE TABLE dbo.t2 (x int);
INSERT dbo.t1(somenbr) VALUES (1),(2),(10),(100),(5000);
INSERT dbo.t2 VALUES (0);
-- test code
DECLARE @applys int = 50000;
DECLARE @sql varchar(max) =
'SELECT t1.*
FROM dbo.t1'
SELECT TOP (@applys)
@sql = @sql +char(13)+char(10)+ CONCAT('CROSS APPLY dbo.t2 x',number)
FROM master..spt_values
WHERE type = 'P' AND number > 0
EXEC(@sql);
Its instantaneous at 255 APPLYs but crawls beginning at around 300. I suspect there is no limit but, again, never found any documentation.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Fortunately, I only need around 300, not thousands. 😀
Thanks guys.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
One more thing. One of the reasons I've always loved SSC as a resource is that you guys went to the trouble of writing the code to generate those 2000 applies just to see what would happen. It's not just that I hadn't thought to do that, it's that you jumped in and did it rather than just suggesting I try it. I don't think you would get that kind of assistance on any other tech forum.
I'm just happy to be here. 😀 😀 😀 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Quick thought, I wouldn't expect the optimizer to do anything but abort the optimizing process on a time out when handling queries with hundreds of apply operators.
😎
-
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?
--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/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. 😀
-
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
😎
-
Eirikur Eiriksson (10/2/2016)
Quick thought, I wouldn't expect the optimizer to do anything but abort the optimizing process on a time out when handling queries with hundreds of apply operators.😎
It apparently does not :ermm: when i tested it just generating the estimated execution plan ate through 10GB of tempdb space then stalled until my boss walked in and asked wth I was doing on the dev server.....
-
ZZartin (10/3/2016)
Eirikur Eiriksson (10/2/2016)
Quick thought, I wouldn't expect the optimizer to do anything but abort the optimizing process on a time out when handling queries with hundreds of apply operators.😎
It apparently does not :ermm: when i tested it just generating the estimated execution plan ate through 10GB of tempdb space then stalled until my boss walked in and asked wth I was doing on the dev server.....
It will abort the optimizing process, but it still needs to get an execution plan.
-
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.
__________________________________________________
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 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply