Spring NamedParameterJDBCTemplate reuse of Prepared Statements

Question

I am using the Spring NamedParameterJdbcTemplate to fetch some values from a table. For some reason, the query runs very slow in my Java app as opposed to running the same query on SQL Management Studio. I also noticed in the profiler, the prepared statements don't get reused. If I run the same query in my JAVA app multiple times, I see different prepared statements being executed. So, not sure why the statements are not getting reused. Is the performance slow because I am using a IN clause in my query?

Here is my sample java code

StringBuilder vQuery = new StringBuilder();
vQuery.append(" SELECT SUM(Qty) FROM vDemand");
vQuery.append(" WHERE ProductID = :ProductID");
vQuery.append(" AND [Date] >= :StartDate AND [Date] <= :EndDate");
vQuery.append(" AND CustomerID IN ( :CustomerID )");

MapSqlParameterSource vNamedParameters = new MapSqlParameterSource();
vNamedParameters.addValue("ProductID", aProductID);
vNamedParameters.addValue("CustomerID", aCustomerlIDs);
vNamedParameters.addValue("StartDate", aDate, Types.TIMESTAMP);
vNamedParameters.addValue("EndDate", aDate, Types.TIMESTAMP);

int vTotalQuantity = this.getNamedParameterJdbcTemplate().queryForInt(vQuery.toString(), vNamedParameters);
return vTotalQuantity;

Solution

Looking at the source code of Spring's NamedParameterJdbcTemplate, it parses your SQL into a structure ParsedSql, and then replaces your named parameters with question marks, then builds the PreparedStatement and fills it with your parameters.

It caches the ParsedSql entries, but always builds new PreparedStatements so ultimately these are not reused at the JDBC driver level.

A PreparedStatement has two advantages over a regular Statement:

1. You add parameters to the SQL using methods instead of doing it inside the SQL query itself. With this you avoid SQL injection attacks and also let the driver to do type conversions for you.

2. As you said, the same PreparedStatement can be called with different parameters, and the database engine can reuse the query execution plan.

It seems that NamedParameterJdbcTemplate helps you with the first advantage, but does nothing for the latter.

Comments

[user320587]

Thanks.The strange thing I am seeing is even for a single query, the run time is around 50 times more than the run time in sql server management studio. I will try to get rid of Named parameters and see if a straight sql fixes the issue. thanks again.

[Luciano]

I think that's a different problem, check the configuration of your jdbc driver. Create a new project with the driver and a Main method that only executes the query, plain sql. 50 times slower is too much.

[Patrick Cornelissen]

Many databases cache the execution plan for a given SQL statement, so when you have independent instnces of PreparedStatement issuing the same SQL query, the execution plan won't be created again even when the parameters that are provided along are different