June 1, 2018 at 9:11 pm
In the remarks section of the MSDN article on the IN Operator a warning is provided:
Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632.
I imagine that 'many thousands' is dependent on how busy the server is and how many resources are available but maybe not. If I am using say 3000 values could this be an issue? Would this perhaps cause locking on the table?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 2, 2018 at 5:48 am
If you have 3000 explicit values in an IN clause, you probably have a design problem and performance will suffer from SQL Server trying to parse your code. There will almost certainly be a better approach by storing those values in a table, even if it's just to replace it with IN (SELECT value FROM lookup) although usually once it's in a table there are JOIN based solutions that are tidier.
June 2, 2018 at 7:54 am
MothInTheMachine - Friday, June 1, 2018 9:11 PMIn the remarks section of the MSDN article on the IN Operator a warning is provided:
Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. I imagine that 'many thousands' is dependent on how busy the server is and how many resources are available but maybe not. If I am using say 3000 values could this be an issue? Would this perhaps cause locking on the table?Thanks!
Haven't dug deep into this but I know that on an 8Gb system, the limit is 38382 integer items, if you'll do 38383, then it will throw an error. If I'm correct, this applies to SQL Server 2005 and later. My hunch is that this is related to the plan size.
😎
Performance wise, anything more than a handful of items in the IN clause is a bad idea, inserting the values into a temp table and join that to the source table is noticeably faster if the item count goes above 200.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply