How to execute queries with more parameters than allowed by the database

[gattin...@googlemail.com]

Hi, jOOQ experts!

I'm currently evaluating jOOQ and came across the following problem I've already resolved in a large Hibernate project.

Suppose you have the following query:

DSL.using(...).selectFrom(TABLE).where(TABLE.COLUMN.in(getRange(1, 2002))).fetch();
getRange(from, to) is a helper method that creates a sequence of integers within the given range.

If I execute this query against an SQL Server database using the jTDS JDBC Driver I will get an exception that looks like this (I simplified the generated SQL for the sake of brevity):
Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select ... from [database].[dbo].[table] where ([database].[dbo].[table].[id] in (?, ?, ?, ...) or [database].[dbo].[table].[id] in (?, ?, ?, ...) or [database].[dbo].[table].[id] in (?, ?)) -- SQL rendered with a free trial version of jOOQ 3.9.1]; Prepared or callable statement has more than 2000 parameter markers.
	at org.jooq_3.9.1.SQLSERVER2014.debug(Unknown Source)
	at org.jooq.impl.Tools.translate(Tools.java:1983)
	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676)
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363)
	at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:315)
	at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2708)
        ...
Caused by: java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.
	at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1254)
        ...

I faced the same problem in Hibernate and solved it by rewriting the Criteria prior execution, so that this technical detail must not be considered by any developer who creates the query.
In the end the query rewriting algorithm scans the query for in() clauses and replaces the large number of parameters by a subquery (something like select id from temp where correlationId = ...). The subquery uses a so called correlation id for grouping the parameters of the 
in() clauses. This peace of code is very sophisticated and it requires an intimate understanding of the Criteria-API and uses reflection. It takes me quite a while to make this stable and to ensure that any kind query (even with nested sub queries) can be transformed correctly.

So I wonder if jOOQ has a built-in solution for this database specific problem.
Or am I forced to reinvent the wheel, hence migrate the algorithm to the API of jOOQ?

Kind regards,
Marcus

[Lukas Eder]

Hi Marcus,

Interesting, is that a documented limitation of JTDS? We've experienced the limit with the official JDBC driver to be 2100 bind variables, after which jOOQ automatically inlines all your bind variables. Some more background can be seen in a recent issue, where we fixed jOOQ to limit PostgreSQL bind variables to 32767:

https://github.com/jOOQ/jOOQ/issues/5701#issuecomment-265830847

Perhaps, JTDS tries to support both SQL Server and Sybase ASE, as the latter has a limit of 2000. If this is documented in JTDS, we'll fix jOOQ accordingly. I have registered an issue for this:

https://github.com/jOOQ/jOOQ/issues/5954

In the meantime, I have these suggestions for you:

1) Try using Microsoft's JDBC driver. You should no longer experience this issue.

2) Don't use large IN lists. They're generally a bad idea as they quickly saturate your execution plan cache, which will slow down your entire system. Better put the IDs in a temporary table first, or if you can, don't pass around IDs, but re-use the original query that produced the IDs in the first place as a subquery. In your particular case, why do you need to pass the entire range? If it has no gaps, you can use a BETWEEN predicate instead.

3) If there's no way around the IN list, you can explicitly inline all bind variables either on a case-by-case basis by using DSL.inline(), or by setting Setting.statementType = StatementType.STATIC_STATEMENT.

More info about the latter here:

https://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marcus Gattinger]

Hi Lukas,

thank you very much for the quick and very informational response.

Actually this is just a sample. In the real application, this list of ids is the result of multiple queries that select them iteratively. After all ids have been identified, the corresponding objects are selected.
The limitation of 2000 parameters is really a limitation of jTDS, not of SQL Server (see if-block in method SQLParser.parse from line 1234 to 1259).

Here is the respective code snippet:

//
// Impose a reasonable maximum limit on the number of parameters
// unless the connection is sending statements unprepared (i.e. by
// building a plain query) and this is not a procedure call.
//
if (params != null && params.size() > 255
        && connection.getPrepareSql() != TdsCore.UNPREPARED
        && procName != null) {
    int limit = 255; // SQL 6.5 and Sybase < 12.50
    if (connection.getServerType() == Driver.SYBASE) {
        if (connection.getDatabaseMajorVersion() > 12 ||
                connection.getDatabaseMajorVersion() == 12 &&
                connection.getDatabaseMinorVersion() >= 50) {
            limit = 2000; // Actually 2048 but allow some head room
        }
    } else {
        if (connection.getDatabaseMajorVersion() == 7) {
            limit = 1000; // Actually 1024
        } else
        if (connection.getDatabaseMajorVersion() > 7) {
            limit = 2000; // Actually 2100
        }

    }
    if (params.size() > limit) {
        throw new SQLException(
            Messages.get("error.parsesql.toomanyparams",
                    Integer.toString(limit)),
            "22025");
    }
}

HTH, Marcus

[Lukas Eder]

Hi Marcus,

Thanks for digging this out. We'll work around this jTDS limitation in jOOQ then.

I thought so, the generated range couldn't possibly be part of the real query :) So, with 2000+ IDs, I do suggest you spill those to a temporary table and semi-join that one instead of the IN-list. In the short run, explicitly using StatementType.STATIC_STATEMENT or DSL.inline() will do the trick.

In fact, I've just thought of another workaround which will be available in jOOQ 3.10 (documenting here for completeness' sake). We've implemented support for SQL Server Table Valued Parameters. They could be unnested back in a table again, to allow for this pattern:

SELECT *

FROM table

WHERE id IN (SELECT * FROM unnest_tvp(?))

This would then work with a single bind variable. However, this also only works with the official driver, not with jTDS. The TVP feature request is here:

https://github.com/jOOQ/jOOQ/issues/5171

While I'm at it: I'd like to better understand the use-case of using jTDS (to be sure we get things right in integration tests). What's the reason why you're not using the official driver?

Cheers

Lukas

--

[Marcus Gattinger]

Lukas,

we are using jTDS for historical reasons. The development of our software product goes back to 2010. On those days the JDBC driver from Microsoft wasn't as fast as nowadays and significantly slower than the jTDS driver (some rough testing seems to prove that jTDS is still a bit faster). But in general our product can run with any JDBC driver, hence with the one from Microsoft, too. In fact our product should run with any database, not only SQL Server, but also with MySQL, Oracle, DB2, etc. which is the reason we try to stick to ANSI-SQL in most cases and let the JDBC driver choose the best technique to execute the SQL statement we have written in code.

Your suggestion is quite interesting. Is that something the developer writing the SQL must be aware of or is jOOQ smart enough to apply this pattern if the target database is SQL Server (of a version that supports it) and the SQL contains large sets of parameters (as in my example)?

Kind regards,
Marcus

[Marcus Gattinger]

Lukas,

after using DSL.inline() as you proposed, I receive the following exeception:
Exception in thread "main" org.jooq.exception.SQLDialectNotSupportedException: Type class java.util.ArrayList is not supported in dialect DEFAULT
    at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:820)
    at org.jooq.impl.DefaultDataType.getDataType(DefaultDataType.java:764)
    at org.jooq.impl.DSL.getDataType(DSL.java:18663)
    at org.jooq.impl.DSL.val(DSL.java:16502)
    at org.jooq.impl.DSL.inline(DSL.java:15735)

I create the DSLContext like this:

DSL.using(DataSource.get(), SQLDialect.SQLSERVER2014)

What am I missing here?

Cheers,
Marcus

[Marcus Gattinger]

However, using the StatementType.STATIC_STATEMENT approach works fine.

[Lukas Eder]

Hi Marcus,

You cannot inline an array list. You have to inline each individual value. In terms of streams:

List<Integer> ids = ...

MY_TABLE.ID.in(

    ids.stream()

       .map(DSL::inline)

       .toArray(new Field[0]));

Hope this helps,

Lukas

--

[Lukas Eder]

OK, I see. Thanks for the explanations.

I remember jTDS having been faster in the past. Specifically the serialisation of binary data was significantly faster if I remember correctly. On the other hand, with the driver now being MIT licensed and on Github, there might be some new traction towards a better official driver:

https://github.com/Microsoft/mssql-jdbc

We'll see.

2017-03-09 7:59 GMT+01:00 Marcus Gattinger <gatt...@gmx.de>:

Your suggestion is quite interesting. Is that something the developer writing the SQL must be aware of or is jOOQ smart enough to apply this pattern if the target database is SQL Server (of a version that supports it) and the SQL contains large sets of parameters (as in my example)?

Hmm, I suggested a few things :) I'm assuming this is about reverting to temporary tables rather than IN lists.

No, jOOQ doesn't automatically optimise your SQL. In principle, jOOQ is an expert tool that helps you write exactly the SQL you want on any target database, so if you *want* to have queries with large IN lists, then jOOQ won't prevent this from happening.

However, if you're aware of these problems, then jOOQ will help you work around them as well. For instance, jOOQ 3.9 introduced a feature called IN list padding (https://github.com/jOOQ/jOOQ/issues/5600) which reduces the combinatorial complexity of possible SQL strings when doing dynamic SQL by repeating some values in the IN list in order to have fewer possible SQL strings.

But it would be unwise to apply this automatically, because padding IN lists has its own overhead (more bind variables than needed) and you might not have any problem in production with large IN lists, if you run this kind of query only periodically.

However, we're thinking about writing a jOOQ lint extension in the future, that logs messages to some lint log to indicate common mistakes in SQL queries. Ultimately, our mission is to teach as much as to help.

Lukas