Slow with bind parameters, even slower with JdbcTemplate

Question

I have a four-column table in my Oracle 11g database implementing the extension-table anti-pattern. I noticed that some queries were taking very long and made an effort to create better indexes; it was fine in interactive session but still as slow using Spring’s NamedJdbcTemplate.

Consider the following routine:

private void getObjectIds(ObjectDomain domain, HashMap<String, List<String>> dimensionMap)
    throws SQLException {
String sql = "SELECT m2.OBJECT_ID"
    + "  FROM MetaInformations m1, MetaInformations m2\n"
    + "  WHERE m1.OBJECT_ID = m2.OBJECT_ID\n"
    + "    AND m1.OBJECT_DOMAIN = :domain AND m1.KEY = :key1 AND\n"
    + "        m1.REF_OBJ_VALUE IN (:values1)\n"
    + "    AND m2.OBJECT_DOMAIN = :domain AND m2.KEY = :key2 AND\n"
    + "        m2.REF_OBJ_VALUE IN (:values2)";
String sqlWithBind = "SELECT m2.OBJECT_ID\n"
    + "  FROM MetaInformations m1, MetaInformations m2\n"
    + "  WHERE m1.OBJECT_ID = m2.OBJECT_ID\n"
    + "    AND m1.OBJECT_DOMAIN = ? AND m1.KEY = ? AND\n"
    + "        m1.REF_OBJ_VALUE IN (?, ?, ?, ?)\n"
    + "    AND m2.OBJECT_DOMAIN = ? AND m2.KEY = ? AND\n"
    + "        m2.REF_OBJ_VALUE IN (?)";

// Prebuilding statement, no bind variables left
Stopwatch stopWatch2 = Stopwatch.createStarted();
Iterator<Entry<String, List<String>>> entries = dimensionMap.entrySet().iterator();
Entry<String, List<String>> entry1 = entries.next();
Entry<String, List<String>> entry2 = entries.next();
String prebuilt = sql.replace(":domain", "'" + domain + "'")
    .replace(":key1", "'" + entry1.getKey() + "'")
    .replace(":values1",
        entry1.getValue().stream().map(s -> "'" + s + "'").collect(Collectors.joining(", ")))
    .replace(":key2", "'" + entry2.getKey() + "'")
    .replace(":values2",
        entry2.getValue().stream().map(s -> "'" + s + "'").collect(Collectors.joining(", ")));
Set<Long> rs2 = extractIdSet(getNamedParameterJdbcTemplate().queryForRowSet(prebuilt, Collections.emptyMap()));
log.warn("Prebuilt took: {} ms", stopWatch2.elapsed(TimeUnit.MILLISECONDS));

// Simple JDBCTemplate with 9 bind parameters
Stopwatch stopWatch5 = Stopwatch.createStarted();
Set<Long> rs1 = extractIdSet(getJdbcTemplate().queryForRowSet(sqlWithBind,
    domain.toString(),
    entry1.getKey(),
    entry1.getValue().get(0),
    entry1.getValue().get(1),
    entry1.getValue().get(2),
    entry1.getValue().get(3),
    domain.toString(),
    entry2.getKey(),
    entry2.getValue().get(0)));
log.warn("JdbcTemplate took: {} ms", stopWatch5.elapsed(TimeUnit.MILLISECONDS));

// Most beautiful: NamedJDBCTemplate
Stopwatch stopWatch3 = Stopwatch.createStarted();
Map<String, Object> paramMap = createNamedParameterMap(domain, dimensionMap);
Set<Long> rs3 = extractIdSet(getNamedParameterJdbcTemplate().queryForRowSet(sql, paramMap));
log.warn("NamedParameterJdbcTemplate took: {} ms", stopWatch3.elapsed(TimeUnit.MILLISECONDS));
}

Here are the results. The exact timings varied from run to run but always stayed in the same order of magnitude.

1. Using a query without any bind parameters finishes very quickly, in the order of less than 100 ms.
2. Using a Spring’s JdbcTemplate with 9 bind variables, the performance degrades to a crawl, with about 4 seconds.
3. Finally, using a NamedJdbcTemplate, which is easiest and most flexible, is just as slow as case 2; this at least, comes as no surprise since behind the curtains NamedJdbcTemplate will replace my query with named parameters into something equivalent to case 2.

It’s not getting the connections, as they all get them from the same connection pool. It doesn’t seem to be the queryForRowSet() function alone, since that’s actually what gets used in the fastest case as well. In the same vein, it doesn’t look as if had anything to do with Spring’s exception translation or participation in ongoing transactions, since that should affect case 1 as well.

So finally, the question: Why is Spring’s JdbcTemplate with bind parameters so very slow in this case compared to a plain statement without bind parameters?

Answer 1

It turns out that it is neither JdbcTemplate nor NamedJdbcTemplate. It also not about PreparedStatement versus Statement, even if the latter was the fastest. That was only because a normal statement does not come with bind parameters. If I have the query without bind parameters, it’s about the same speed with raw JDBC and NamedJdbcTemplate alike.

Our Oracle 11g simply choses a bad execution plan for this query with 9 bind parameters and sticks to it no matter what the actual parameters. I have no idea why and no real DBA is available.

A test on a PostgreSQL 9.3 database with the same data showed that it was equally fast both with and without bind parameters; with an out-of-the-box Ubuntu installation.