March 8, 2005 at 5:12 am
Does anyone know what the limit is for the number of values you can have in an IN clause
i.e. where xxx IN ('xxx','yyy',......). I am getting 17803 error and am pretty sure I'm blowing out the limit, but can't find listing for sql limits anywhere.
March 8, 2005 at 6:41 am
March 8, 2005 at 6:47 am
Ummmm. I did get that far. Why I'm asking about the IN limit is because the only way the sql would run without this error is to chop up the IN statement into smaller chunks. So I was wondering what the limit was. In researching the error, I didn't see any mention of blowing out the IN limit.....but it looks like that is what it was in my case....hence my question.
March 8, 2005 at 7:26 am
I don't know a specific limitation in the IN clause but I can tell you there is a point a really long typed IN list will actually hurt performance. I in fact tested with a IN list of 50,000 items without issue then doubled it.
But the error you are getting is around insufficent memory. I usually see this when I try to return a large recordset and the server hits the amount of available memory for the recordset to return and no paging space left.
Many time these will transfer and oyu will get a low memory error on your local machine instead so I ask how you are viewing the recordset and suggest you look at your server reporting low memory in the Event Logs.
March 8, 2005 at 9:02 am
Someone is running a query from coldfusion that is creating a flat file based on a column in
a table of 4300 rows matching a "pseudo-column" in a "pseudo-table" .csv table of approximately 4300 entries.
The IN clause would be the .csv values.
When the .csv table is split up into around 1600 entries it works without errors.
March 8, 2005 at 12:41 pm
I am positive that 4300 IDs are FAR from the limit on an IN clause you must look else where in the process!
HTH
* Noel
March 8, 2005 at 4:11 pm
Watch the server with Profiler and see what is coming in for the SQL statement. May be something you haven't accounted for that revolves around the way you are doing it.
March 9, 2005 at 2:16 am
I feel that this is one of those questions where 'If I need to know, I must be doing something wrong'! Could you put the values in a temporary table and then do a join?
A SQL statement can go up to about 250Mb! SQL2000 has a 2k limiy on the number of parameters to a stored procedure (SQL 7 is 1k).
March 10, 2005 at 12:19 pm
Why not use a join statement. I avoid large IN clauses, It seems inefficent to have them when you can make it simpler with a Join.
-Isaiah
March 10, 2005 at 12:21 pm
We did end up working around it (temp table/join), but I was just wondering if there was a limit.
Thanks all.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply