Will PreparedStatement without bind variable affect performance?

Question

Does performance will be less if I will not use bind variables in prepared statements.

Consider the example below:

String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = '101'";
PreparedStatement preparedStatement =     dbConnection.prepareStatement(selectSQL);

below one is preferable I know. it prevents sql injection

String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";

this question asked because one of my project in all the queries they have hard coded string literals even they are using prepared statements.

both the queries are safe from sql injections. unless the first one gets userid as a dynamic variable. — Abhishek, Jul 21 '16 at 09:06
@Abhishek the example is probably unfortunate. I guess he means something like `"SELECT ... WHERE USER_ID = '" + userId + "'";` — Thomas, Jul 21 '16 at 09:07
http://stackoverflow.com/questions/3271249/difference-between-statement-and-preparedstatement `Advantages of a PreparedStatement: Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.` — Andreas Fester, Jul 21 '16 at 09:10
If the values are really hard coded in your queries, i.e. `"... WHERE USER_ID = '101'"` instead of `"... WHERE USER_ID = '" + userId + "'"` then it should be safe from sql injection (there's no way to inject anything in the first variant) _but_ you indicate you have a lot of queries and that sounds like you have a query per user id etc. - that's bad design and hard to maintain so you should consider using a single query and pass the user id instead, in which case I'd prefer the `" ... WHERE USER_ID = ?"` version. — Thomas, Jul 21 '16 at 09:11
Hard coded constants will not cause less performance. However the number of prepared statements the database will be managing, will increase. Normally no problem. If however SQL strings are sneakily dynamically added together, one might loose performance by a massive number of prepared statements. However in that case one has other serious problems, and the DB might not really degrade even then. — Joop Eggen, Jul 21 '16 at 09:38

raphaëλ · Accepted Answer · 2016-07-21T09:25:21.823

A prepared statement just means you "prepare" the statement. This means you sent it to the database and let the database already parse/compile the statement. This is useful for statements which are executed often.

Parameterised statements are often executed many times so they are sometimes associated with prepared statements (you only have to send the values across and the server will have an already compiled statement ready to execute the query).

But even with a "static" statement which is executed often you might benefit from preparing the statement (especially for "complex" statements). Another technique used is to implement the query inside a a stored procedure. That way the query is already compiled and the server only needs to compile the stored procedure call when you run it.

It will only work if you don't unprepare the stamenent (e.g, when closing the connection). So you might need a pool and a prepared statement cache to not overflow the server with prepared statement request (prepared statements require resources on the server's end)

Parameterised queries are also used to protect against sql-injection (as it is based on data types and not strings). However this is orthogonal to the benefits of prepared statements. (Note both your queries are not exposed to sql injection)

score 2 · Answer 2 · answered Jul 21 '16 at 10:13

Whether or not using parameters is more efficient mainly depends on your use case. When you monitor the times spent in preparation and execution of statements, you will notice, that preparation of statements is somtimes siginificantly more expensive than the actual execution, so it depends, whether you can save on prepares.

You will always win in a scenario like this:

PreparedStatement stmt = dbConnection.prepareStatement(...);
stmt.setInt(1, x);
stmt.executeQuery();
...
stmt.setInt(1, y);
stmt.executeQuery();
...

because you save on the prepare-times. This is DBMS independent.

Some DBMS do some server side caching. What and how they do it is highly vendor and even version-specific. However, we have learned that in Oracle-DBMS parametrised statements which are used more often do sometimes get quite a speed-up in such a scenario:

PreparedStatement stmt = dbConnection.prepareStatement(...);
stmt.setInt(1, x);
stmt.executeQuery();
...
PreparedStatement stmt2 = dbConnection.prepareStatement(...);
stmt2.setInt(1, y);
stmt2.executeQuery();
...

Here the server is able to identify that both statements are essentially the same and prepare-times for the second statement were significantly reduced. This does not work for fixed-string statements because the server cannot determine, they are the same.

I don't think, you will find a scenario, where using parametrized statements are measurable slower than fixed-string-statements but a lot of scenarios, where its the other way around.

Will PreparedStatement without bind variable affect performance?

2 Answers2