CODEWITHSUNDEEP
javajdbc
Phate
Phate

Reputation: 6602

Is a compiled prepared statement stored within an object or in general?

I know that, when used the first time, jdbc keeps somewhere the compiled prepared statement so that next time it will be accessed in a more efficient way.

Now, suppose I have this situation:

public class MyDao{
       public void doQuery(){
           try(PreparedStatement stmt = connection.prepareStatement(MY_STMT)){

            }
       }

 }

Both the following snippets will keep the compiled prepared statement in memory?

Snippet 1:

MyDao dao = new MyDao();
dao.doQuery(); //first one, expensive
dao.doQuery(); //second one, less expensive as it has been already compiled

Snippet 2:

MyDao dao = new MyDao();
dao.doQuery(); //first one, expensive
MyDao dao2 = new MyDao();
dao2.doQuery(); //will it be expensive or less expensive?

I am afraid that, by creating a new dao object, the jvm will see that prepared statement as a new one and so it will not compile it.

And, if it's not the case, is there any situation in which the jvm will "forget" the compiled statement and will compile it again?

Thanks

Upvotes: 2

Views: 1216

Answers (3)

Mark Rotteveel
Mark Rotteveel

Reputation: 108939

The most basic scenario for prepared statement reuse is that your code keeps the PreparedStatement open and reuses that prepared statement. Your example code does not fit this criteria because you close the prepared statement. On the other hand trying to keep a prepared statement open for multiple method invocations is usually not a good plan because of potential concurrency problems (eg if multiple threads use the same DAO, you could be executing weird combinations of values from multiple threads, etc).

Some JDBC drivers have an (optional) cache (pool) of prepared statements internally for reuse, but that reuse will only happen if an attempt is made to prepare the same statement text again on the same physical connection. Check the documentation of your driver.

On a separate level, it is possible that the database system will cache the execution plan for a prepared statement, and it can (will) reuse that if the same statement text is prepared again (even for different connections).

Upvotes: 3

Serg M Ten
Serg M Ten

Reputation: 5606

Do a benchmark. It is the best way to get some certainty about the performance difference. It is not necessarily the case that the statement is always recompiled at server side. Depending on your RDBMS, it may cache the statements previously compiled. In order to maximize the cache hit probability, submit always exactly the same parameterized SQL text and do it over the same connection.

Upvotes: 0

john16384
john16384

Reputation: 8044

You're correct it will be compiled again. PreparedStatements will only be reused if you actually use the statement itself multiple times (ie, you call executeQuery on it multiple times).

However, I wouldn't worry too much about the cost of compiling the statement. If your query takes more than a few milliseconds, the cost of compiling will be insignificant. The overhead of compiling statements only becomes apparent when doing 1000's of operations per second.

Upvotes: 0

Related Questions