Re: IN clause with more than 999 elements

Originally posted by Richard Kuhler

> Turkbear wrote:

> > Depending on the number of records and other fields, that
> combination of OR and IN could still be running when the Heat
> Death

> > Of The Universe happens... ;-)

>

> I'm curious how you came to this conclusion. Can you provide some

> benchmarks or technical explanation that support this belief? My

> benchmarks indicate that they perform very similarly (I did tests with

> 2500 rows against a 12 million row table). Actually, the OR IN LIST

> method is always going to be faster if you factor in the time
> to insert

> the values into the temporary table.

>

> Richard Kuhler

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

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

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!

--
Posted via http://dbforums.com