Re: IN clause with more than 999 elements

andrewst wrote:
<snip>
> .. but presumably you are factoring out the time to construct the SQL
> with this vast IN list? Cutting and pasting the list, appending commas,
> finding the 999th entry or thereabouts and inserting ") OR field IN ("
> at that point, etc.

Not really. The time to build the query is certainly less than the time to create the temporary table and import the data into the database. While the import is not difficult, constructing the in list is a trivial task with any decent text editor (e.g. vi). In fact, I'm pretty sure I could build the in list faster than you could type that last sentence complaining about how hard it's going to be. ;) Besides, the poster implies that he already has the in list written he just needs to split it into 1000s now.

> I mean, no one would seriously write SQL like that would they?

Sure they would. Your ability to construct a sentence that makes this sound incredulous doesn't really provide a plausible argument against it. If I remember my critical thinking class properly, this is known as the "strawman" fallacy.

> Oh yes, probably some horrible web app that allows the user to construct
> a comma separated list of everything they are interested in, and then
> builds a dynamic SQL statement with an enormous IN list.
 > Every SQL statement unique and non-reusable - lovely!

Wait a second. Maybe I misinterpreted the posters question. I envisioned this as a one time query he was writing to answer some question. Not as an application component. I'd certainly not recommend you go around dynamically constructing SQL statements to handle individual queries for an application that's going to be submitting them all the time.

On the other hand ...

There's certainly no reason you couldn't use bind variables here. You could also consolidate the number of unique queries by having the application use lists of certain lengths (increments of 100) and using null values for the left over binds. Again, dynamically constructing and submitting the single query is probably going to be faster than doing all the inserts (especially in a multi-tier application). Hmmm, I'm not so sure I still wouldn't consider it given certain requirements.   It would definitely warrant some analysis and benchmarking.

Richard Kuhler Received on Fri Sep 19 2003 - 13:47:24 CDT