我在我的Oracle 11g数据库中有一个实现扩展表反模式的四列表。我注意到一些查询花费了很长时间,努力创建更好的索引;在交互会话中是可以的,但使用Spring的NamedJdbcTemplate
仍然很慢。
考虑以下例程:
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));
}
这里是结果。确切的时间因运行而异,但始终保持相同数量级的顺序。
- 使用不带任何绑定参数的查询非常快,不到100毫秒。
- 使用具有9个绑定变量的Spring的JdbcTemplate,性能下降到缓慢状态,大约需要4秒。
- 最后,使用最简单和最灵活的NamedJdbcTemplate与第二种情况一样慢;这至少并不令人惊讶,因为在幕后,NamedJdbcTemplate将使用命名参数替换我的查询,生成等效于第二种情况的内容。
所以最后问题是:为什么在没有绑定参数的情况下,Spring的JdbcTemplate比普通语句要慢得多?
count(*)
,会导致所有带有绑定参数的变量同样缓慢。已相应地编辑了问题;现在问题变成了“为什么绑定参数很慢”,这是我仍然不太理解的问题(反正这是什么样的优化器呢?),但这并不是新问题。 - Michael Piefel