CODEWITHSUNDEEP
javaperformancejdbc
Timo
Timo

Reputation: 921

Performance impact when not reusing PreparedStatements?

The common mantra is to always prefer PreparedStatement over Statement because of protection against SQL injection and a (significant) gain in performance.

But what if my code rebuilds the same PreparedStatement over and over again, instead of properly reusing it?

Do some of the performance benefits over using Statement remain, or am I left with a heavyweight quote escape engine for string parameters?

Upvotes: 0

Views: 179

Answers (1)

Glenn
Glenn

Reputation: 9150

There can still be advantages to rebuilding a PreparedStatement over a Statement, but it is going to depend on the vendor. As an example, in Oracle, using bind variables was good practice in order to not destroy the server's sql cache.

The Oracle server would take a hash of the sql string to see if it already existed in the Oracle server's sql cache. If so, the cached pcode for the statement could be re-used. In this case a lot of the work like parsing, validating, checking authorizations, etc was already done and just the parameter area would need to be updated. Also, usage of the sql cache would be minimized since a new statement didn't need to be put in there.

In poorly designed application, we would see the sql cache full of statements like:

SELECT wage FROM employee WHERE employee_id = 1
SELECT wage FROM employee WHERE employee_id = 2
SELECT wage FROM employee WHERE employee_id = 3
SELECT wage FROM employee WHERE employee_id = 4

The statements received from the client would not result in a cache hit in the sql cache, so all the parsing work would need to be done again and the new statement added to the cache. Not only was there the extra work of parsing the statement, but the cache became useless because it was flooded with this stuff. In a well designed app, you might be able to size the sql cache to have every statement the application will run sit in the cache once it warms up.

We would query the dictionary tables, counting statements with a similar prefix to find these offenders, get the application code to use bind parameters (if it was Java code, then ask the developers to use PreparedStatements). Then we would see statements like this in the cache:

SELECT wage FROM employee WHERE employee_id = :1

A single statement. And it didn't matter if the client rebuilt it over and over as a Prepared statement if it came out to the same cache hit.

Upvotes: 1

Related Questions