Performance of Spring NamedParameterJdbcTemplate Query is very slow
java, jdbc, oracle, spring, spring-jdbc
Solution
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
Problem
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?