any limitations with large number (10,000) parameters to sql SELECT via JDBC?
I need to query on up to 10,000 different values :
SELECT * from MYTABLE where COL1=? OR COL1=? OR COL1=? ...
Is there a maximum number of parameters that can be passed, or a maximum length to the query string?
Is there any configuration or tuning that might help that type of a query?
I understand that I could obtain the same result with multiple query executions with fewer parameters, but the selection is on non-indexed columns therefore each query execution is going to require a full-table scan, so the less query executions the better.
Re: any limitations with large number (10,000) parameters to sql SELECT via JDBC?
The Teradata Database imposes a limit of 1 MB for SQL request text.
Also, I suspect that you would hit a Teradata Database parser limit for too many question-mark parameter markers.
From a performance point of view, it will probably be faster to create a volatile table, insert the 10,000 values into the volatile table using PreparedStatement batch insert, and then join your actual table with the volatile table.
If the values are small -- such as INTEGER -- then all 10,000 values can probably be added to one batch, and then only one call to PreparedStatement.executeBatch would be needed to insert the values into the volatile table.