Any performance benefit to PreparedStatement in simple, static method query?

Question

Update 2 The down-vote was deserved and encouraged me to write a more robust test: Using java 1.8, ojdbc8.jar against a non-production, Oracle 12C database where I am normally the sole user, I ran 10 iterations of 100 selects: 50 using a PreparedStatement and 50 using a Statement (building the query with String concatenation). To try and rule out some kind of database caching, I then ran the test again but switched the order, running the Statements first followed by the PreparedStatements. There was no significant difference in performance, regardless of order. See below for results (in milliseconds with averages and standard deviations).

UPDATE: Thanks for the all the input. I updated the title. I found a lot of platitudes while researching this ("PreparedStatements are always faster", "PreparedStatements are always slower") but nothing definitive so, I ended up writing a test and found that, for this case, PreparedStatements were consistently ~25% slower.

Initial question: I have a static method with a simple query that checks if an ID is already in the database:

public static boolean isPersisted(Integer id) {
   String sql = "select id from table where id = "+id+"";

I understand they can guard against SQL injection, and I know they offer big benefits when looping over an insert with a list of IDs (for example) but, is there any performance/memory benefit to using a prepared statement here?

The PreparedStatement could be used for the whole lifecycle of your application.The database would not have to parse the query again everytime — Felix, Nov 09 '21 at 15:35
You are asking the wrong question: Don't get into the habit of building SQL queries with string concatenation like this and you won't lose your job because someone exploited a vulnerability years later ... Performance and memory considerations should almost always be a far second to maintaining the integrity of the system. — Joachim Sauer, Nov 09 '21 at 15:49
Basil, Joachim, I asked: Is there any **performance/memory benefit** — mrcrag, Nov 09 '21 at 15:51
Felix, I'm closing my connection in a finally block, and my understanding is that negates any db caching/benefit. Yes? — mrcrag, Nov 09 '21 at 15:52
Some db servers maintain a cache of parsed statements, so repeated runs with `id = ?` style should re-use a parsed statement (and possibly it's execution plan). If you run 1000s of `id = actualvalue` then the server could be discarding other - possibly more complex to evaluate - application SQL and the impact of your bad code may be apparent elsewhere. Good DB admins may spot this and request fixes. — DuncG, Nov 09 '21 at 16:14
@crcrag: yes, and I'm telling you to just use PreparedStatements all the time instead of building SQL. It may or may not be faster, but if you always close all of your connections, then *any* statement will be very slow because it will have to re-establish the connection to the database anyway and that's likely to take up a good amount of the time (assuming you have simple queries with little return-data as the one you showed). — Joachim Sauer, Nov 09 '21 at 17:55
It's right there in your title: _"Any benefit to PreparedStatement in simple, **static query**?"_ — Mark Rotteveel, Nov 09 '21 at 18:11
JDBC performance varies from server to server - there are many factors involved. If you put "consistently ~25% slower" please add some metrics. The performance hit may be on other users of your database. — DuncG, Nov 09 '21 at 19:40
@mrcrag did you reuse the PreparedStatement in your comparison, or did you create a new one every time? Most application-servers have long-running connections to their database, so reusing of PreparedStatements is possible in that case. — Felix, Nov 12 '21 at 11:26
@Felix, I created a new one each time. I would love to use the same one but, from what I've read, it's bad practice to leave connections open and any db-side benefit disappears when the connection is closed. My app uses pooling (org.apache.tomcat.jdbc.pool). — mrcrag, Nov 15 '21 at 17:22

score 2 · Answer 1 · edited Nov 09 '21 at 15:50

2

Is there any performance/memory benefit to using a prepared statement here?

Prepared statements are much faster when you have to run the same statement multiple times, with different data. That's because SQL will validate the query only once, whereas if you just use a statement it will validate the query each time.

The details of why and what's going on can be found in this post

edited Nov 09 '21 at 15:50

Joachim Sauer

302,674
57
556
614

answered Nov 09 '21 at 15:36

Arthur Klezovich

2,595
1
13
17

score 0 · Answer 2 · answered Nov 09 '21 at 15:48

If the query is only executed once, and if id was cleanly produced for the application itself (for example as a simple computation result), prepared statements have no gain.

If the query has to be executed a number of times with various id values, you will have a high performance gain in using a prepared query, because the query will only be parsed once by the sql engine.

If the id parameter comes from a user interface, you must use a prepared statement unless you intend to be vulnerable to SQL Injection. Long story made short, it used to be a common attack against poorly coded web application and allowed the attacker to execute arbitrary SQL commands.

score 0 · Answer 3 · answered Nov 09 '21 at 15:55

Basically prepared statements are less vulnerable to SQL Injections and faster in terms of query response time.

And I think you are looking for something like this:

public static boolean isPersisted(Integer id) {
String sql = "select id from table where id = ?";
 
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, id);

gmanjon · Answer 4 · 2021-11-09T16:11:41.603

PreparedStatements are precompiled. That means that are validated, escaped and cached (More details on the precompilation of a PreparedStatement in this question What does it mean when I say Prepared statement is pre-compiled?)

Being precompiled means that all that work is only done once, avoiding all the costs that it takes to do it every time you execute the query.

But as always, the magic is done if used correctly. You could create a PreparedStatement with String concatenations, but beware that any change in the final String will result in a new precompilation (and thus losing the benefits).

And the same for SQL Injections. Use always the placeholders ?. If you concatenate Strings in a PreparedStatement (nothing will stop you from doing such a thing) you can easily fall into SQL Injections, For instance:

connection.prepareStatement("Select * from MY_TABLE where email = '" + userInputEmail + "'")

Never do the above. Is a PreparedStatement, but can be SQL Injected.

So, to benefit from PreparedStatements use them properly.

Any performance benefit to PreparedStatement in simple, static method query?

4 Answers4