July 17, 2013 at 9:21 am
Hello,
I'm having the following error when running a query :
Msg 8623, Level 16, State 1, Line 1
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.
Query (please see attachments for full query) :
WITH
SAWITH0 AS (select T73739."CONTACT_ID" as c1,
T73739."PROSPECT_ID" as c2,
T73739."ACCNT_ID" as c3,
T73739."INTEGRATION_ID" as c4
from
"W_PARTY_PER_D" T73739
where ( T73739."INTEGRATION_ID" in (
29547 Integration_ids
)))
select SAWITH0.c1 as c1,
SAWITH0.c2 as c2,
SAWITH0.c3 as c3,
'0' as c4,
'' as c5,
'0' as c6,
1 as c7,
1 as c9,
SAWITH0.c4 as c10
from
SAWITH0
I managed to not get an error by reducing the number of IN items to 22000. I have attached the estimated query plan.
I also managed to not get the error by removing the fixed values from the CTE select statement as shown below :
select SAWITH0.c1 as c1,
SAWITH0.c2 as c2,
SAWITH0.c3 as c3,
SAWITH0.c4 as c10
from
SAWITH0
I have also attached the query plan. Query plan for select statement with IN list is attached as well.
select T73739."CONTACT_ID" as c1,
T73739."PROSPECT_ID" as c2,
T73739."ACCNT_ID" as c3,
T73739."INTEGRATION_ID" as c4
from
"W_PARTY_PER_D" T73739
where ( T73739."INTEGRATION_ID" in (
29547 Integration_ids
))
Why am I having this behaviour ? I there some kind of limit at work here ? I know I haven't exceeded the IN limit.
All related queries and estimated plans are attached.
Kailash
July 17, 2013 at 9:27 am
Can you turn the list of 25,000+ hard coded values into a table? I have never come anywhere close to a list that extensive like this. I am 99.999% certain that insanely long list of value is your issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2013 at 9:35 am
Thank you for your reply Sean.
The query is generated by an application, so not much room for change at query level.
You are right in saying that the list is at cause because I could get a query plan with 22000 values. I would however like to know why it is failing with the full list.
Kailash
July 17, 2013 at 10:10 am
twoeyed (7/17/2013)
Thank you for your reply Sean.The query is generated by an application, so not much room for change at query level.
You are right in saying that the list is at cause because I could get a query plan with 22000 values. I would however like to know why it is failing with the full list.
Kailash
I would guess it is failing because the size of the list requires so much processing from sql that it runs of resources. To be honest I am not that surprised.
Do you pass this big list in as a single parameter that is a comma delimited list? If so, you could leverage the DelimitedSplit8K function. You can find details about that by following the link in my signature about splitting strings. An even better approach, but probably requires more development effort would be to use a table valued parameter instead of a big list.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply