The query processor ran out of internal resources and could not produce a query plan.

Question

Post reply

The query processor ran out of internal resources and could not produce a query plan.

mpsathiskumar

SSC-Addicted

Points: 400
More actions
October 6, 2010 at 1:56 am

#229126

Hi All,
I have created more than 30 temporary tables like
"DECLARE @tblPop9351 TABLE(Name VARCHAR(12), EmpId VARCHAR(12),
Role VARCHAR(10))
DECLARE @tblPop9352 TABLE(Name VARCHAR(12), EmpId VARCHAR(12), Role VARCHAR(10))"
.
.
.
upto more than 30 temp tables.
After that I inserted some values in all temp tables and select those table in below queries.
"SELECT * FROM @tblPop9351 INTERSECT
SELECT * FROM @tblPop9352 INTERSECT
SELECT * FROM @tblPop9353 INTERSECT
.
.
.
SELECT * FROM @tblPop93531"
But this query returns below error message:
"Msg 8623, Level 16, State 1, Line 487
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."
So, my questions is how to solve this issue without changing the temp table and INTERSECT logic?
Thanks & Regards,
Sathiskumar.P

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

What happens , if you cut the query down to 5,10 , 15 or 20 tables ?

Do you get the same error ?

Can you post a full reproduction script ?

Also , please confirm your exact SqlServer version with

SELECT @@VERSION

Clear Sky SQL
My Blog[/url]

mpsathiskumar SSC-Addicted Points: 400 More actions · Answer 2

Hi,

I verified it's working fine upto 30 select statements and if I include another one select query then that error occurred.

This is the full sample set of queries:

"

DECLARE @tblPop9351 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9352 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9353 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9354 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9355 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9356 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9357 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9358 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop9359 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93510 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93511 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93512 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93513 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93514 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93515 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93516 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93517 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93518 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93519 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93520 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93521 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93522 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93523 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93524 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93525 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93526 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93527 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93528 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93529 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93530 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93531 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93532 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93533 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93534 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93535 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93536 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93537 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93538 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93539 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93540 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93541 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93542 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93543 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93544 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93545 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93546 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93547 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93548 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93549 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93550 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93551 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93552 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93553 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

DECLARE @tblPop93554 TABLE(Name VARCHAR(12), EmpId VARCHAR(12))

INSERTINTO @tblPop9351 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9352 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9353 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9354 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9355 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9356 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9357 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9358 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop9359 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93510 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93511 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93512 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93513 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93514 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93515 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93516 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93517 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93518 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93519 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93520 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93521 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93522 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93523 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93524 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93525 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93526 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93527 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93528 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93529 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93530 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93531 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93532 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93533 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93534 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93535 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93536 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93537 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93538 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93539 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93540 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93541 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93542 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93543 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93544 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93545 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93546 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93547 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93548 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93549 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93550 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93551 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93552 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93553 (Name, EmpId)

SELECT'Test1','0154454'

INSERTINTO @tblPop93554 (Name, EmpId)

SELECT'Test1','0154454'

SELECT * FROM @tblPop9351 INTERSECT

SELECT * FROM @tblPop9352 INTERSECT

SELECT * FROM @tblPop9353 INTERSECT

SELECT * FROM @tblPop9354 INTERSECT

SELECT * FROM @tblPop9355 INTERSECT

SELECT * FROM @tblPop9356 INTERSECT

SELECT * FROM @tblPop9357 INTERSECT

SELECT * FROM @tblPop9358 INTERSECT

SELECT * FROM @tblPop9359 INTERSECT

SELECT * FROM @tblPop93510 INTERSECT

SELECT * FROM @tblPop93511 INTERSECT

SELECT * FROM @tblPop93512 INTERSECT

SELECT * FROM @tblPop93513 INTERSECT

SELECT * FROM @tblPop93514 INTERSECT

SELECT * FROM @tblPop93515 INTERSECT

SELECT * FROM @tblPop93516 INTERSECT

SELECT * FROM @tblPop93517 INTERSECT

SELECT * FROM @tblPop93518 INTERSECT

SELECT * FROM @tblPop93519 INTERSECT

SELECT * FROM @tblPop93520 INTERSECT

SELECT * FROM @tblPop93521 INTERSECT

SELECT * FROM @tblPop93522 INTERSECT

SELECT * FROM @tblPop93523 INTERSECT

SELECT * FROM @tblPop93524 INTERSECT

SELECT * FROM @tblPop93525 INTERSECT

SELECT * FROM @tblPop93526 INTERSECT

SELECT * FROM @tblPop93527 INTERSECT

SELECT * FROM @tblPop93528 INTERSECT

SELECT * FROM @tblPop93529 INTERSECT

SELECT * FROM @tblPop93530 INTERSECT

SELECT * FROM @tblPop93531 INTERSECT

SELECT * FROM @tblPop93532 INTERSECT

SELECT * FROM @tblPop93533 INTERSECT

SELECT * FROM @tblPop93534 INTERSECT

SELECT * FROM @tblPop93535 INTERSECT

SELECT * FROM @tblPop93536 INTERSECT

SELECT * FROM @tblPop93537 INTERSECT

SELECT * FROM @tblPop93538 INTERSECT

SELECT * FROM @tblPop93539 INTERSECT

SELECT * FROM @tblPop93540 INTERSECT

SELECT * FROM @tblPop93541 INTERSECT

SELECT * FROM @tblPop93542 INTERSECT

SELECT * FROM @tblPop93543 INTERSECT

SELECT * FROM @tblPop93544 INTERSECT

SELECT * FROM @tblPop93545 INTERSECT

SELECT * FROM @tblPop93546 INTERSECT

SELECT * FROM @tblPop93547 INTERSECT

SELECT * FROM @tblPop93548 INTERSECT

SELECT * FROM @tblPop93549 INTERSECT

SELECT * FROM @tblPop93550 INTERSECT

SELECT * FROM @tblPop93551 INTERSECT

SELECT * FROM @tblPop93552 INTERSECT

SELECT * FROM @tblPop93553 INTERSECT

SELECT * FROM @tblPop93554"

My SQL version is:

Thanks & Regards,

Sathiskumar.P

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

mpsathiskumar (10/6/2010)
So, my questions is how to solve this issue without changing the temp table and INTERSECT logic?

Thanks for the repro script , it obviously is a bug.

I suggest you raise a connect item with this script.

You wont fix this without changing the code..

First off lots of similar table is usually the wrong thing to do IMO, but without know why you have done this its hard to know. I advise you relook at that.

A fix would be to , UNION ALL , these tables together and insert to another table.

Then

Select Name, EmpId , Role

from <newtable>

group by Name, EmpId , Role

having count(*) >= <numberofactualtables>

HTH

Clear Sky SQL
My Blog[/url]