When running an SQL query with an IN predicate in the WHERE clause, the query runs fine as long as the number of entries in the IN list does not exceed a certain number.
Is there a limit as to the number of values that can be specified in an “IN” list?
Release: All supported releases.
Component: SQL Option.
If a query experiences a change in performance when it is altered, that is almost certainly due to a different path being taken through the database on the different queries.
There are indeed limits for various aspects of SQL, including syntax limits. These are documented at Summary of Limits. However, it can be confusing as to how the limit applies to a particular SQL statement, because the limit is applied after the compiler processes the statement and translates the clauses into how they will be processed. An "IN" clause, for example, is actually an "OR-list" so it's a series of OR clauses, each clause representing a statement that compares the table column to a specific value in the list.
If a limit is exceeded, an error message will be produced indicating that has occurred. In some cases, the query will not receive an error; it will just encounter differences in performance time once it exceeds a certain number of parameter values.
This change in performance is almost certainly due to a different path being taken through the database on the different queries. That can be verified using the EXPLAIN command.
There are a couple of options available if there needs to be more entries in the "IN" list.