How prepared statement improves performance

Question

I came across below statement that tells about the performance improvement that we get with JDBC PreparedStatement class.

If you submit a new, full SQL statement for every query or update to the database, the database has to parse the SQL and for queries create a query plan. By reusing an existing PreparedStatement you can reuse both the SQL parsing and query plan for subsequent queries. This speeds up query execution, by decreasing the parsing and query planning overhead of each execution.

Let's say I am creating the statement and providing different values while running the queries like this :

String sql = "update people set firstname=? , lastname=? where id=?";

PreparedStatement preparedStatement =
        connection.prepareStatement(sql);

preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong  (3, 123);

int rowsAffected = preparedStatement.executeUpdate();

preparedStatement.setString(1, "Stan");
preparedStatement.setString(2, "Lee");
preparedStatement.setLong  (3, 456);

int rowsAffected = preparedStatement.executeUpdate();

Then will I still get performance benefit, because I am trying to set different values so I can the final query generated is changing based on values.

Can you please explain exactly when we get the performance benefit? Should the values also be same?

score 5 · Accepted Answer · answered Mar 25 '15 at 17:22

When you use prepared statement(i.e pre-compiled statement), As soon as DB gets this statement, it compiles it and caches it so that it can use the last compiled statement for successive call of same statement. So it becomes pre-compiled for successive calls.

You generally use prepared statement with bind variables where you provide the variables at run time. Now what happens for successive execution of prepared statements, you can provide the variables which are different from previous calls. From DB point of view, it does not have to compile the statement every time, will just insert the bind variables at rum time. So becomes faster.

Other advantages of prepared statements is its protection against SQL-injection attack

So the values does not have to be same

More precisely, Oracle only needs to do a "soft" parse, i.e. a lookup from cursor cache, instead of a "hard" parse (which is expensive) the next time it encounters the same statement being used. — Mick Mnemonic, Mar 25 '15 at 19:36
So it only really improves performance if you are using the same query more than once in a connection? In that case, connection pooling would improve your chances of reusing queries in the same connection and benefiting in performance. — Michael K, Apr 12 '18 at 14:46

score 1 · Answer 2 · answered Mar 25 '15 at 21:11

Although it is not obvious SQL is not scripting but a "compiled" language. And this compilation aka. optimization aka hard-parse is very exhaustive task. Oracle has a lot of work to do, it must parse the query, resolve table names, validate access privileges, perform some algebraic transformations and then it has to find effective execution plan. Oracle (and other databases too) can join only TWO tables - not more. It means then when you join several tables in SQL, Oracle has to join them one-by-one. i.e. if you join n tables in a query there can be at least up to n! possible execution plans. By default Oracle is limited up to 8000 permutations when search for "optimal" (not the best one) execution plan.

So the compilation(hard-parse) might be more exhaustive then query execution itself. In order to spare resources, Oracle shares execution plans between sessions in a memory structure called library cache. And here another problem might occur, too many parsing require exclusive access to a shared resource. So if you do too many (hard) parsing your application can not scale - sessions are blocking each other.

On the other hand, there are situations where bind variables are NOT helpful. Imagine such a query:

update people set firstname=? , lastname=? where group=? and deleted='N'

Since the column deleted is indexed and Oracle knows that there are 98% of values ='Y' and only 2% of values = 'N' it will deduce to use and index in the column deleted. If you used bind variable for condition on deleted column Oracle could not find effective execution plan, because it also depends on input which is unknown in the time of the compilation. (PS: since 11g it is more complicated with bind variable peeking)

How prepared statement improves performance

2 Answers2