any limitations with large number (10,000) parameters to sql SELECT via JDBC?

Connectivity

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.

Thanks,

- Mike
1 REPLY
Teradata Employee

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.