PreparedStatement is faster in Java, How db do it?

Question

I know that PreparedStatement is faster than Statement in Java.

I don't know How oracle db server do it.

PreparedStatement gets pre-compiled In database server -> less work. It's reduce load on database.

String sql = "SELECT * FROM users u WHERE u.id = ?";
PreparedStatement pstmt = connenction.prepareStatement(sql);
pstmt.setLong(1, userId);
pstmt.executeQuery();

The query is cached in the database server, and compile only once?
If yes, how the database server knows that this query was execute before?
For how long is it cached?

Answer 1

The query is cached in the database server, and compile only once?

More precisely, the query plan is cached on the server. When you run a query, your RDBMS prepares a plan first, then executes it. Preparing a plan requires parsing the query, then analyzing and optimizing it, considering the indexes available and the statistics collected on the participating tables.

If yes, how the database server knows that this query was execute before?

By comparing the string of the query to other queries available in the cache. Since you use parameterized queries, the other query would be textually identical. Caching is the second major reason^* to use query parameters: if you were to prepare statements like this

// WRONG! Don't do it like this!
String sql = "SELECT * FROM users u WHERE u.id = "+userId;
PreparedStatement pstmt = connenction.prepareStatement(sql);

all the performance improvements would be gone, because providing a different ID would make it a different query that needs a new plan.

^* The top reason for parameterizing queries is, of course, avoiding injection attacks.

Answer 2

You can think of a PreparedStatement as a "cached" statement that will be compiled once on the database server.

When you create the statement, it will be sent to the DB server, which will do the usual syntax checking and determine an efficient execution plan for the query. This way, it can re-use the same execution plan (which is cached as well) for multiple invocations of the same statement.

The key of this cache is the statement itself without its parameter values filled. If the values are explicitly filled in the statement (i.e. you don't fill them using the set* methods), then a new execution plan would be accessed in the cache. That's why prepared statements are best used when executing several statements but with different values.