Use IN Record Padding to Your JDBC Utility to Keep away from Cursor Cache Competition Issues – Java, SQL and jOOQ.

An issue few builders are conscious of is the potential for operating into “cursor cache competition” or “execution plan cache competition” issues when utilizing IN lists in SQL. The issue that’s described in lengths in earlier articles, could be summarised as this.

All of those are distinct SQL queries and must be parsed / deliberate / cached as probably distinct execution plans in RDBMS which have robust plan caches (e.g. Db2, Oracle, SQL Server):

SELECT * FROM t WHERE id IN (?);
SELECT * FROM t WHERE id IN (?, ?);
SELECT * FROM t WHERE id IN (?, ?, ?);
SELECT * FROM t WHERE id IN (?, ?, ?, ?);
SELECT * FROM t WHERE id IN (?, ?, ?, ?, ?);

Whereas that is by no means an issue on developer machines, this could produce vital issues in manufacturing. I’ve seen this take down whole Oracle situations throughout peak hundreds. Whereas RDBMS distributors ought to work on avoiding the extreme issues this could trigger, you possibly can work round it utilizing a trick that we invented at jOOQ (and Hibernate additionally has it now):

IN checklist padding

The trick could be very easy. Simply “pad” your IN lists to the closest energy of 2, and repeat the final worth till the top:

SELECT * FROM t WHERE id IN (?);                      -- Left as it's
SELECT * FROM t WHERE id IN (?, ?);                   -- Left as it's
SELECT * FROM t WHERE id IN (?, ?, ?, ?);             -- Padded 3 to 4
SELECT * FROM t WHERE id IN (?, ?, ?, ?);             -- Left as it's
SELECT * FROM t WHERE id IN (?, ?, ?, ?, ?, ?, ?, ?); -- Padded 5 to eight

It’s actually a hack and there are higher options to avoiding this downside, together with utilizing arrays or momentary tables, however your manufacturing system could also be down and also you want a fast repair.

jOOQ has supported IN checklist padding for years now, since jOOQ 3.9 (late 2016), however with the comparatively new parser and the ParsingConnection, now you can additionally apply this system to any arbitrary SQL question in your non-jOOQ primarily based system. Right here’s a easy instance:

// Any arbitrary JDBC Connection is wrapped by jOOQ right here and changed
// by a "ParsingConnection", which can be a JDBC Connection
DSLContext ctx = DSL.utilizing(connection);
ctx.settings().setInListPadding(true);
Connection c = ctx.parsingConnection();

// Your remaining code is left untouched. It's unaware of jOOQ
for (int i = 0; i < 10; i++) {
    strive (PreparedStatement s = c.prepareStatement(

        // This alone is purpose sufficient to make use of jOOQ as an alternative, 
        // however one step at a time :)
        "choose 1 from twin the place 1 in (" +
            IntStream.rangeClosed(0, i)
                     .mapToObj(x -> "?")
                     .accumulate(Collectors.becoming a member of(", ")) +
        ")")
    ) {
        for (int j = 0; j <= i; j++)
            s.setInt(j + 1, j + 1);

        strive (ResultSet rs = s.executeQuery()) {
            whereas (rs.subsequent())
                System.out.println(rs.getInt(1));
        }
    }
}

The above instance simply generates and runs 10 queries of this type:

choose 1 from twin the place 1 in (?)
choose 1 from twin the place 1 in (?, ?)
choose 1 from twin the place 1 in (?, ?, ?)
choose 1 from twin the place 1 in (?, ?, ?, ?)
choose 1 from twin the place 1 in (?, ?, ?, ?, ?)
choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?)
choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?)
choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?)
choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?, ?)
choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

However that’s not what’s being executed. Within the DEBUG logs, we are able to see the next:

Translating from         : choose 1 from twin the place 1 in (?)
Translating to           : choose 1 from twin the place 1 in (?)
Translating from         : choose 1 from twin the place 1 in (?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Translating from         : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Translating to           : choose 1 from twin the place 1 in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

And identical to that, our legacy utility can run in manufacturing once more, and also you’ll have time to repair this extra totally.

Conclusion

Whereas jOOQ is usually an inner DSL for writing sort secure, embedded SQL in Java, you should use it for lots of different issues too on any JDBC primarily based utility. The above instance is utilizing the ParsingConnection that may parse all of your SQL statements and translate / rework them to the rest, together with different dialects.