CODEWITHSUNDEEP
javaoracle-databasespringjdbcspring-jdbc
theMothaShip
theMothaShip

Reputation: 1211

Performance of Spring NamedParameterJdbcTemplate Query is very slow

I am working on a project that requires JDBC Calls to an Oracle Database. I have set up UCP pooling to work with SpringJDBC. I have a fairly simple query that I am executing like the following...

SELECT * FROM TABLE WHERE ID IN (:ids)

my java code to set this query up looks like the following...

NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(datasource);
Map<String,Object> paramMap = new HashMap<String,Object>();
paramMap.put("ids", Arrays.asList(idArray));

List<Result> results = template.query("SELECT * FROM TABLE WHERE ID IN (:ids)",
    paramMap, new ResultRowMapper());

This all performs fine as long as there is only 1 id in the array. When I add a 2nd ID the query takes nearly 5 minutes to run. If I take the exact query and execute it in SQLDeveloper, it takes .093 seconds.

Something must be going terribly wrong with my code or configuration... Does anyone have any ideas?

EDIT:

I stripped out the usage of the Spring NamedParameterJdbcTemplate and went with just straight Jdbc and everything seems to perform great. What is it that NamedParameterJdbcTemplate is doing differently?

Upvotes: 6

Views: 10979

Answers (1)

theMothaShip
theMothaShip

Reputation: 1211

Well, I found the difference between my straight jdbc solution and my spring-jdbc solution in this situation... It appears that as @Annjawn below explained, it is a bind variable issue not a spring-jdbc issue. My spring-jdbc issue was trying to bind a variable to an index (that doesn't exist) thus doing a table scan...

My straight JDBC solution ends up just doing a string replacement and executing as is thus no table scan...

The below link explains the difference.

http://bytes.com/topic/oracle/answers/65559-jdbc-oracle-beware-bind-variables

Upvotes: 3

Related Questions