Maximum Number of Values in an In in a where clause

Question

Maximum Number of Values in an In in a where clause

andymay-496937

SSC Eights!

Points: 939
More actions
August 6, 2013 at 4:18 pm

#395253

Hi
Can anyone tell me if there is a limit to how many entries there can be in an in?
As in where somevalue in ('Fred','George','Dave'....)
The reason I ask is I have a query that was created by SSRS, it creates an 'in' with 20975 entries and it makes SQL sever respond with
"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."
SQL Server has over 7 Gb of RAM allocated.
Any help would be much appretialed

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

From Books Online:

Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.
Error 8623:
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.
Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andymay-496937 SSC Eights! Points: 939 More actions · Answer 2

Thanks for that, I'm not sure that Books on Line advice about putting the values in a table are that helpful for SSRS, but it's good stuff in general.

thanks

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

andymay-496937 (8/6/2013)
Thanks for that, I'm not sure that Books on Line advice about putting the values in a table are that helpful for SSRS, but it's good stuff in general.
thanks

Where is the "IN" list coming from?

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

andymay-496937 (8/6/2013)
Thanks for that, I'm not sure that Books on Line advice about putting the values in a table are that helpful for SSRS, but it's good stuff in general.

Well, to be honest you can do that or you can reduce the number of values until the query runs, or you can throw memory at the problem which might help or might not. Not all that many solutions, especially if you feel that the common solution isn't helpful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

andymay-496937 (8/6/2013)
Thanks for that, I'm not sure that Books on Line advice about putting the values in a table are that helpful for SSRS, but it's good stuff in general.

So I don't know SSRS at all, but I am fully convinced that there are alternate solutions that does not require IN. A Reporting Services forum should be able to help you.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

HowardW One Orange Chip Points: 29109 More actions · Answer 6

The multiple parameter value option in SSRS is for end users to select multiple items. It would be pretty unusual for an end user to select 20975 different items in a report. Are you driving that list from another data set? Can you not join them together at the source instead?