Ran out of Stack Space - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Ran out of Stack Space

Author

Topic

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 12:11:49

The query processor ran out of stack space during query optimization. Please simplify the query.

I have the following error appearing when I try to write my query. It has the following structure so perhaps someone can point me in the right direction.

cte TableA (has no where clause)
cte TableB (same as TableA, but has a where clause)
cte TableC (combines both tables... there are metrics calculated and used in this table and allows cteTableA.Field1 to be next to cteTableB.Field1)

MainTableA gets percentile metrics from cte TableC and this is being inserted into MainTableB.

It is a long query. How would I go about making it more efficient or expanding the stack space or whatever it will take to get it to run?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 12:42:45

Does this fit your query? http://support.microsoft.com/kb/288095

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 12:56:53

There isn't any IN clauses in this particular query

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 12:58:53

You'll likely need to use the same type of workaround where you use a temp table or similar.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 13:45:28

Can you show me an example using three cte's as listed and a Table?

Sounds like instead of cteTableA, I need TempTableA. Can you also explain the difference? I thought cte was basically a temp table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 13:54:09

I don't have an example, but here's some pseudocode:
CREATE TABLE #temp (Column1 int, Column2 varchar(30))
INSERT INTO #temp
Your CTE query goes here

Then join to #temp wherever you would have used the first CTE.

You may need to do this for your other CTEs as it sounds like the code is massive.

http://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 14:28:07

This isn't clear to me.

I have the following:
a with statement creating cte TableA --> ;with cteTableA as ([query])
then after that, I create cte TableB --> , cteTableB as ([query] where xyz)

cte TableC joins the two --> with cteTableC ... joins A and B on PrimaryID

The Select Into gets the results from cte Table C, but at percentiles for each of the results... so using Metric1, it would be a.PercentileMetric1, b.PercentileMetric1 and so on... I am using the new style of grouping/partition for this table

The cte statements have to be before if there is a reference to them, correct?

In your example, you are creating a temp table before the CTE....

Wouldn't I need to convert cte Table A to Temp Table A and cte Table B to Temp Table B? Why would I need a cte anymore? Your example isn't too clear on these things.

It is just over 800 lines of query. I am not sure what constitutes as being large, etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 15:28:38

Can you show a very simplified version of your query so that we can help rewrite it? It is hard to help without code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 15:58:20

;with cteTableA as (SELECT
[MyDataTable].[PrimaryID],
[MyDataTable].[GroupField],
[MyDataTable].[FieldA]
FROM MyDataTable),
cteTableB as (SELECT
[MyDataTable].[PrimaryID],
[MyDataTable].[GroupField],
[MyDataTable].[FieldA]
FROM MyDataTable
WHERE FieldA > 0 AND < 1),
with cteTableC as (SELECT
a.[PrimaryID],
a.[GroupField],
a.[FieldA],
b.[FieldA] as FieldA_B
FROM cteMyTableA a INNER JOIN cteMyTableB b ON a.PrimaryID = b.PrimaryID)

SELECT DISTINCT [GroupField], Round(PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [FieldA] Asc) OVER (PARTITION BY [GroupField]),3) AS [FieldA_Perc],
[GroupField] Round(PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [FieldA_B] Asc) OVER (PARTITION BY [GroupField]),3) AS [FieldA_B_Perc]
INTO NewTable
FROM cteMyTableC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 16:15:48

I used SELECT INTO #temp for simplicity reasons. For best practice reasons, you should instead use CREATE TABLE #temp and then INSERT INTO #temp SELECT.


SELECT 
	[MyDataTable].[PrimaryID],
	[MyDataTable].[GroupField],
	[MyDataTable].[FieldA]
INTO #temp1
FROM MyDataTable

SELECT 
	[MyDataTable].[PrimaryID],
	[MyDataTable].[GroupField],
	[MyDataTable].[FieldA]
INTO #temp2
FROM MyDataTable
WHERE FieldA > 0 AND < 1

SELECT
	a.[PrimaryID],
	a.[GroupField],
	a.[FieldA],
	b.[FieldA] as FieldA_B
INTO #temp3
FROM #temp1 a 
INNER JOIN #temp2 b ON a.PrimaryID = b.PrimaryID

SELECT DISTINCT [GroupField], Round(PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [FieldA] Asc) OVER (PARTITION BY [GroupField]),3) AS [FieldA_Perc],
[GroupField] Round(PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [FieldA_B] Asc) OVER (PARTITION BY [GroupField]),3) AS [FieldA_B_Perc]
INTO NewTable
FROM #temp3

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 16:35:50

Awesome, that was very helpful on the structure.

What is the syntax to drop a temp table if it exists?

I know this is the syntax for a regular table:
IF OBJECT_ID('dbo.NewTable', 'U') IS NOT NULL
DROP TABLE dbo.NewTable

Or should I add Drop the temporary Tables after the insert into New Table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 16:42:51

Same syntax as a regular table. Drop them right after you are done with them to free up resources in tempdb. If it's in a stored procedure and there's nothing more to do in it, then you don't have to explicitly drop them. After the stored procedure completes, the temp objects are removed.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 16:47:52

I am not sure what the U stands for or whether it needs to be something else for a tempTable rather than a regular Table.

Is this the syntax?
IF OBJECT_ID('#tempTableA', 'U') IS NOT NULL
DROP TABLE #tempTableA

I haven't investigated stored procedures yet. That is my next step after I learn this piece though. I want to learn what stored procedures are and where they can help in what I am learning.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 16:53:41

You'll need to use tempdb..#temp for the IF check.

The second parameter for OBJECT_ID is for the object type. U=table (user-defined). Full list of types: http://msdn.microsoft.com/en-us/library/ms190324.aspx

I don't bother with the second parameter though.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-11 : 17:03:22

IF OBJECT_ID('temp..#tempTableA', 'U') IS NOT NULL
DROP TABLE #tempTableA

This gives me the error:
There is already an object named '#tempTableA' in the database

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 17:16:13

It should say tempdb instead of temp, but you are getting that error because of the creation of the temp table, not because of the drop. Add it to the top of your script for testing purposes so that the script is re-runnable regardless if you drop the table at the end.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-11 : 17:31:25

Late to the party, but:
1. Are any of CTEs recursive?
2. Temp tables work just fine, but what about ditching the CTEs and just combining the queries (derived tables) into one query?

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-12 : 08:02:47

Tara,

I realized that I left off the db portion shortly after I posted and fixed it.

I am getting the same error now if I run the full query. I am having to cut off some of the portions in this part of the query for it to run:
SELECT DISTINCT [GroupField], Round(PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [FieldA] Asc) OVER (PARTITION BY [GroupField]),3) AS [FieldA_Perc],
[GroupField] Round(PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [FieldA_B] Asc) OVER (PARTITION BY [GroupField]),3) AS [FieldA_B_Perc]
INTO NewTable
FROM #temp3

============================================================
I have a number of fields in which I am getting percentiles for. "The query processor ran out of stack space during query optimization. Please simplify the query."

Lamprey,
The first two cte's or temp tables are very similar. The difference is the where clause so it provides different data. I am trying to grab the percentiles with and without the where clause for every numeric field and there are a number of fields. I am open to suggestions on how to make this more efficient so that it can run. It is above my experience level with queries, but it is providing a good learning case study for me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-12 : 11:45:53

Though it will be very hard to provide help for a complex script that is 800 lines long, I think at this point we do need to see the whole beast. In case there is proprietary stuff in there, just do a find/replace for object names and data values.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-12 : 19:44:23

The last main table that is being created has about 170 percentile calculations just like I have already posted. It is allowing about 150 of them to work. Any more than that, and I get the error I showed.

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-13 : 00:16:45

Are there any suggestions on how to optimize this?

1) TempTableA
2) TempTableB (has where clause, but otherwise same as A)
3) TempTableC Combines the two

MainTable takes the percentiles of each calculation in TempTableC. There are about 170 of them. If I mark out the last 20, the query will process. If I go one more beyond that, I get the error "The query processor ran out of stack space during query optimization. Please simplify the query."

Is this something a setting can fix?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources