|Subject||Re: How to handle a large 'IN' clause?|
> However, I know that people are unlikely to go through 10000 recordsHi Set,
> and carefully select 5000 of them randomly distributed, which is what
> your generated SQL seems to assume. Put a little bit more intelligence
> into your SQL generator
A case that I have seen this sort of problem arise has been a
reporting filter. Basically, employees were categorised into
departments in a many to many relationship.
Depending on which departments you selected, the employee list
changed, and you do not a choice except to use a where in clause. They
don't carefully tick anything, a well designed UI does it for them,
they then untick what they don't want (and can save their selection).
Also, based on the fact that not everyone is granted security access
to every department, the employee list does not contain every employee
it would be quite difficult to work out what the selection was.
Generally speaking, one person wouldn't be interested in reporting on
1500 employees, so it would fit within an IN statement, however head
office staff tend to select everyone in reports.