Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is PreparedStatement efficient for multiple application runs or for single?

 
Volodymyr Lysenko
Ranch Hand
Posts: 512
1
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello!

This is the extract from one tutorial:

Longer answer: Most relational databases handles a JDBC / SQL query in four steps:
1. Parse the incoming SQL query
2. Compile the SQL query
3. Plan/optimize the data acquisition path
4. Execute the optimized query / acquire and return data

A Statement will always proceed through the four steps above for each SQL query sent to the database. A PreparedStatement pre-executes steps (1) - (3) in the execution process above. Thus, when creating a PreparedStatement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time.

My question is :
The effect of pre-executing steps 1-3 by PreparedStatement is for single application run or for multiple application run-times?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At least some databases cache the SQL queries themselves, so that the parse/compile/optimize step can be skipped if the database has already seen the same exact query, regardless of whether it comes from Statement, PreparedStatement or another (non-Java) application altogether. So it is not possible to give a general answer.

However, if you never run identical statement (and a PreparedStatement with different values of parameters is an identical statement in this context) more than once on a single physical connection, you're not going to benefit from it. A simple Statement would perform about the same. Re-running the same application again almost certainly means a new physical connection to the database, so the PreparedStatement needs to be re-created from scratch in the JVM (but it might still be cached in the database).

If your application uses connection pool, the same prepared statement can be used by different sessions, so even if it is always used at most once in a single session (eg. verify user/password during a login), it might still be cached by the physical connection in the pool and you can still get the performance benefit from it (the exact behavior probably depends on the connection pool configuration). This is very often overlooked when assessing the performance of prepared statements.
 
Volodymyr Lysenko
Ranch Hand
Posts: 512
1
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply!

So, you say that benefit we get using PreparedStatement is not done by PreparedStatement but by database .

The question was: does PreparedStatement caches and reuses my query or it's database that caches and reuses my query?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Both of them (database and the JDBC driver) have to do their part.

It might be driver and database dependent, but the usual scenario is that the PreparedStatement obtains some sort of a handle from the database, which points directly to the compiled query. So when you execute it, the database doesn't even need to look at the text of the query to look it up in the cache, it grabs directly the query which needs to be executed. And since parsing and compiling not only takes some time, but also scales poorly, using PreparedStatements gets more and more important as the number of statements executed in the database grows.

The query handle is tied to the prepared statement. Closing the statement loses the handle, but some JDBC drivers cache prepared statements in the connection, so that reopening identical statement in the same physical connection retrieves the cached PreparedStatement instead of creating a new one, completely sidestepping any database processing (this is especially useful with connection pools, as I've mentioned before). Physically closing the connection prevents from reusing its cached prepared statements, AFAIK.

The cache in the database can also get invalidated, or the cached queries can be evicted when there isn't enough room for all of them. The mechanism has to somehow accommodate this possibility, so that the PreparedStatement can be recompiled when it is no longer cached. I don't know much of the details here.

This is how it works in Oracle. In other databases it might be different, but I guess that most of the mainstream databases work more or less like this.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic