CODEWITHSUNDEEP
javajdbcmariadb
Deepak Bhatia
Deepak Bhatia

Reputation: 6276

Prepared statement for Select SQL with Connection Pooling

Is it good practice to use Prepared statement for SELECT SQL with connection pooling. (In my case I use Tomcat JDBC connection pool).

Does it add any advantage(speed ups) or it will add overhead for maintaining the Prepared Statements, connections and keep them alive or track whether closed as Pooled connections are maintained internally and they get closed according to different settings as specified here.

I am using DataSource to get connection, Database is MariaDB.

While reading various posts, documentations and examples most of Prepared Statement have been built using INSERT or UPDATE queries. Does it points that for SELECT it will not add any advantage?

Upvotes: 0

Views: 929

Answers (2)

Jan
Jan

Reputation: 13858

It's good to use PreparedStatement if you can:

  • Prevent SQLInjection
  • Abstract Date/Time representation
  • Deal with Charset conversions
  • Readability (you see one string with full SQL)
  • As the SQL stays constant (with ?) the database might cache the plan and doesn't have to reparse

In case of SELECT the main focus of cause lies with the parameters passed into the WHERE condition.

As for performance: This may depend - but I've never experienced PreparedStatements to be significantly worse than simple Statements - if coded correct of cause.

The fact that you're pooling connections doesn't add much to this. The concept of somehow "preparing all the statements you're going to need on that connection for later" is not how PreparedStatments are meant to be used. It's perfectly fine to prepare the same tiny Statement over and over and over - altough if faced with a loop of INSERTs or UPDATEs it would be wise to reuse PreparedStatement and/or batch the INSERTs

Upvotes: 1

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

MariaDB/MySQL prepared statements do not have any advantages when it comes to query parsing / optimizing, the query plan is not preserved as on some other SQL databases.

They do have a performance advantage when it comes to transferring result sets as column values can be transfered in binary form and get stored into result variables right away. With classic non-prepared statements all result fields are converted to textual form on the server side. This adds processing time on the server side, leads to more bytes having to be transfered over the wire, and depending on your application needs the client side may need to convert values back from textual to binary form (e.g. for integer and float values).

The other reason for using prepared statements, as also noted in the previous comments, is that it is a reliable way to prevent SQL injection, and that applies to SELECT as well as INSERT/UPDATE/DELETE

Upvotes: 1

Related Questions