Hi everyone
I am getting this error when I run the query:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
I have a 700 line SQL query so I have no idea where the error actual is. It gives line 18 but that is the beginning of the WITH clause. I have 20 plus CTEs to break up the calculation into smaller meaningful ways.
How can I begin to trouble shoot this?
Any suggestions would be much appreciated.
Thank you
Disassemble the CTE into single queries and run them one at a time?
insert the intermediate results into a temporary table once you fix them and then query tempdb?
December 24, 2021 at 4:56 am
I ran each CTE and I think I found the problem. It is this one:
SELECT
T2.UNDERLYING_SYMBOL,
T2.QUOTE_DATE,
T2.STRIKE,
T2.CALL_PRICE,
T2.PUT_PRICE
FROMNEAR_TERM_OPTIONS_CTE AS T2
INNER JOIN
(
SELECT
T1.UNDERLYING_SYMBOL,
T1.QUOTE_DATE,
MIN(ABS(CALL_PRICE - PUT_PRICE)) AS MIN_DIFFERENCE
FROMNEAR_TERM_OPTIONS_CTE AS T1
GROUP BYT1.UNDERLYING_SYMBOL, T1.QUOTE_DATE
) AS T3
ONT2.UNDERLYING_SYMBOL = T3.UNDERLYING_SYMBOL AND
T2.QUOTE_DATE = T3.QUOTE_DATE
WHEREABS(T2.CALL_PRICE - T2.PUT_PRICE) = T3.MIN_DIFFERENCE
Any ideas on how to improve it?
December 24, 2021 at 9:11 am
the issue is not the sql itself but the whole combination - your system is likely constrained in memory and was unable to produce a plan for the full query.
the solution as mentioned is to split some of the CTE's onto temp tables and use those instead
December 24, 2021 at 2:59 pm
As was communicated in a previous post, we don't know the definitions of the CTEs your referencing. Without DDL for all underlying tables/views, and the entire query, there is little information to suggest tuning a snippet of a larger query.
I agree with Frederico -- try moving some of the intermittent results from CTEs to separate inserts into temp tables & referencing the temp tables in the main query.
In addition to the fact that the complexity of many CTEs, may leave SQL unable to find a good plan, a CTE may be evaluated every time it is used in a query (Most links I've found say it will be re-evaluated, but I found one that suggested it could be cached). So in a big complex query, CTEs may add complexity that makes it harder to understand & debug, and impedes good query plans... and may not even provide the performance benefit of cached/persisted data.
CTEs are very useful, but can be tempting to overuse.
December 24, 2021 at 3:15 pm
One problem does stand out in the snippet -- use of most functions in WHERE clauses or JOINS prevent sargability (ability to use indexes)
WHERE ABS(T2.CALL_PRICE - T2.PUT_PRICE) = T3.MIN_DIFFERENCE
T3.MIN_DIFFERENCE cannot be negative -- it's calculated using ABS():
MIN(ABS(CALL_PRICE - PUT_PRICE)) AS MIN_DIFFERENCE
So if you're just filtering out negative differences between CALL_PRICE & PUT_PRICE, perhaps you could use something like this instead of using ABS()
T2.CALL_PRICE - T2.PUT_PRICE = T3.MIN_DIFFERENCE
AND T2.CALL_PRICE >= T2.PUT_PRICE
Or perhaps you can pre-calculate the absolute value in a temp table?
Why are the spaces missing between FROM/BY/ON/WHERE & the referenced objects?
FROMNEAR_TERM_OPTIONS_CTE AS T2
FROMNEAR_TERM_OPTIONS_CTE AS T1
GROUP BYT1.UNDERLYING_SYMBOL, T1.QUOTE_DATE
ONT2.UNDERLYING_SYMBOL = T3.UNDERLYING_SYMBOL AND
WHEREABS(T2.CALL_PRICE - T2.PUT_PRICE) = T3.MIN_DIFFERENCEFunctions like ABS() in your where clause will prevent sargability (ability to use an index).
December 24, 2021 at 3:29 pm
the issue is not the sql itself but the whole combination - your system is likely constrained in memory and was unable to produce a plan for the full query.
the solution as mentioned is to split some of the CTE's onto temp tables and use those instead
Or - combine the current CTE's where possible. Without seeing the full code - it is impossible to tell for sure, but it is almost certainly a situation where multiple CTE's are referencing the same source tables and either the same where clause, or very close to the same.
For those, it is possible to rewrite the query to provide those calculations in a single query which would then be a single pass through the data. In the one shown, it can probably be rewritten using ROW_NUMBER and TOP 1 WITH TIES.
Select Top 1 With Ties
T2.UNDERLYING_SYMBOL
, T2.QUOTE_DATE
, T2.STRIKE
, T2.CALL_PRICE
, T2.PUT_PRICE
From NEAR_TERM_OPTIONS_CTE T2
Order By
row_number() Over(Partition By T2.UNDERLYING_SYMBOL
, T2.QUOTE_DATE
Order By abs(T2.CALL_PRICE - T2.PUT_PRICE));
Since this query relies on a previous CTE - it would be much easier to create a DIFF_PRICE column in that previous CTE with the above calculation so it can be referenced in the later CTE's as needed. Depending on how that earlier CTE is created - you could also add the ROW_NUMBER to that previous CTE and then simply filter the results.
Assume you add those 2 new columns to the previous CTE, this one would then be:
Select T2.UNDERLYING_SYMBOL
, T2.QUOTE_DATE
, T2.STRIKE
, T2.CALL_PRICE
, T2.PUT_PRICE
From NEAR_TERM_OPTIONS_CTE T2
Where T2.MIN_DIFF_PRICE_RN = 1;
If there is a requirement to get the MAX DIFF PRICE - then another calculated column using row_number and order by desc value, and then you can filter it using: T2.MAX_DIFF_PRICE_RN = 1.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 24, 2021 at 4:16 pm
As was communicated in a previous post, we don't know the definitions of the CTEs your referencing. Without DDL for all underlying tables/views, and the entire query, there is little information to suggest tuning a snippet of a larger query.
I agree with Frederico -- try moving some of the intermittent results from CTEs to separate inserts into temp tables & referencing the temp tables in the main query. In addition to the fact that the complexity of many CTEs, may leave SQL unable to find a good plan, a CTE may be evaluated every time it is used in a query (Most links I've found say it will be re-evaluated, but I found one that suggested it could be cached). So in a big complex query, CTEs may add complexity that makes it harder to understand & debug, and impedes good query plans... and may not even provide the performance benefit of cached/persisted data.
CTEs are very useful, but can be tempting to overuse.
One of the reasons I used CTEs is my understanding that they are cached so they are not re-calculated each time they used. Is there a way to tell SQL Server to cache them?
December 24, 2021 at 4:17 pm
the issue is not the sql itself but the whole combination - your system is likely constrained in memory and was unable to produce a plan for the full query.
the solution as mentioned is to split some of the CTE's onto temp tables and use those instead
Good idea. I will try that.
December 24, 2021 at 4:22 pm
One problem does stand out in the snippet -- use of most functions in WHERE clauses or JOINS prevent sargability (ability to use indexes)
WHERE ABS(T2.CALL_PRICE - T2.PUT_PRICE) = T3.MIN_DIFFERENCET3.MIN_DIFFERENCE cannot be negative -- it's calculated using ABS():
MIN(ABS(CALL_PRICE - PUT_PRICE)) AS MIN_DIFFERENCESo if you're just filtering out negative differences between CALL_PRICE & PUT_PRICE, perhaps you could use something like this instead of using ABS()
T2.CALL_PRICE - T2.PUT_PRICE = T3.MIN_DIFFERENCE
AND T2.CALL_PRICE >= T2.PUT_PRICEOr perhaps you can pre-calculate the absolute value in a temp table?
Why are the spaces missing between FROM/BY/ON/WHERE & the referenced objects?
FROMNEAR_TERM_OPTIONS_CTE AS T2
FROMNEAR_TERM_OPTIONS_CTE AS T1
GROUP BYT1.UNDERLYING_SYMBOL, T1.QUOTE_DATE
ONT2.UNDERLYING_SYMBOL = T3.UNDERLYING_SYMBOL AND
WHEREABS(T2.CALL_PRICE - T2.PUT_PRICE) = T3.MIN_DIFFERENCEFunctions like ABS() in your where clause will prevent sargability (ability to use an index).
The specifications that I am working off state that it is the absolute difference between the prices is used as a filter.
December 25, 2021 at 4:38 pm
One of the reasons I used CTEs is my understanding that they are cached so they are not re-calculated each time they used. Is there a way to tell SQL Server to cache them?
I am not sure where this comes from - and it really doesn't make sense. Data will be read from disk into memory (buffer cache) for *every* query that is run. If the data is already in memory, then that query only performs logical reads of the data - no physical reads are necessary.
A CTE is the same as a view - and the entirety of the query with all CTE's, views, functions and tables are evaluated to generate an execution plan. That plan determines how SQL Server retrieves the data to get to the final results.
If you reference the same table in different CTE's with different criteria, then SQL Server must read that data (logical reads) for each one. There could be some exceptions where SQL Server is able to combine them into a single operation - but it isn't likely.
The misconception that a CTE is somehow 'materialized' and treated as a distinct operation in SQL Server is often the cause of lots of confusion. I have seen this same idea in relation to views - and again, that is not correct. The code in CTE's, Views and inline-table valued functions are incorporated into the final query - and then SQL generates the plan.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 26, 2021 at 5:09 pm
To add to what Jeffrey said - if a CTE is mentioned in the following code 2 or more times it's evaluated and executed as many times. No caching and reusing of datasets happening here.
so, in the code snippet above the code behind FROMNEAR_TERM_OPTIONS_CTE is included into the final query twice and executed twice.
_____________
Code for TallyGenerator
December 26, 2021 at 5:12 pm
To add to what Jeffrey said - if a CTE is mentioned in the following code 2 or more times it's evaluated and executed as many times. No caching and reusing of datasets happening here.
so, in the code snippet above the code behind FROMNEAR_TERM_OPTIONS_CTE is included into the final query twice and executed twice.
Is there a way to cache the results so it isn't re-calculated each time it is used?
December 26, 2021 at 6:46 pm
Sergiy wrote:To add to what Jeffrey said - if a CTE is mentioned in the following code 2 or more times it's evaluated and executed as many times. No caching and reusing of datasets happening here.
so, in the code snippet above the code behind FROMNEAR_TERM_OPTIONS_CTE is included into the final query twice and executed twice.
Is there a way to cache the results so it isn't re-calculated each time it is used?
yes - calculate it outside of main query and put results onto a temp table.
December 26, 2021 at 11:26 pm
Thank you to everyone who helped on this issue. I modified the query so key results are put into temp tables and voila the performance improved big time. The original query took close to 10 seconds to run. The new one runs in less than 1 second. WOW! what a difference.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply