Is there any drawback to enabling <share-prepared-statements> for datasources?

[omas...@gmail.com]

When defining a database datasource in Wildfly/Jboss EAP there is a parameter 'share-prepared-statements' which is described like this :

Whether to share prepare statements, i.e. whether asking for same statement twice without closing uses the same underlying prepared statement. The default is false

To me it sounds good to have it enabled. But the fact that it is set to 'false' by default makes me think that there could be drawbacks. The problem is that I cannot find any documentation on this. So what could be the possible drawbacks ?

Note : I previously posted the question on stackoverflow before realising there was a forum for Wildfly :

https://stackoverflow.com/questions/75359931/wildfly-jboss-eap-is-there-any-drawback-to-enabling-share-prepared-statements

[Paul Ferraro]

AIUI, shared-prepared-statements are a rudimentary form of prepared statement pooling.

Generally, PreparedStatement reuse reduces the need to generate redundant query plans, but, ultimately, the impact of this will depends on the capability of your JDBC driver.

AFAICT, this is disabled by default since it requires that all usage of a given statement command would configure the PreparedStatement be in the same manner.

I also suspect that the shared PreparedStatement implementation does not consult isPoolable() method of the underlying PreparedStatement implementation, and would otherwise shared PreparedStatements that are not poolable.

Paul

[omas...@gmail.com]

When you say "the impact of this will depend on the capability of your JDBC driver", does it mean that the impact can be negative with some JDBC drivers or just that there could be no gain in performance with some JDBC drivers ?

Is there any known major database (I'm thinking Posgres, Oracle, SqlServer, DB2) having JDBC drivers not supporting shared prepared statements ? (I cannot really find this information on the web).

For the second part, if I understand correctly:

If we always use PreparedStatement in the same manner in all of our code by just instanciating it this way :

PreparedStatement statement = connection.prepareStatement(...)

and not modifying at all its default configuration (which means that it will be always marked as poolable when created, as this is the default value).

-> It means there should be no problem at all to set 'shared-pepared-statements' to true, right ?

Thanks a lot ;)

Olivier Masseau

[Paul Ferraro]

See inline.

On Friday, February 10, 2023 at 6:25:13 AM UTC-5 omas...@gmail.com wrote:

When you say "the impact of this will depend on the capability of your JDBC driver", does it mean that the impact can be negative with some JDBC drivers or just that there could be no gain in performance with some JDBC drivers ?

For those JDBC drivers whose PreparedStatements encapsulate some work done within the database (e.g. creating a query plan), the performance gain can be significant.

For those JDBC drivers whose PreparedStatements no nothing more than create well formed DML statements (i.e. if the creation of the PreparedStatement itself does not interact with the database), then benefit from sharing PreparedStatement objects is minimal.

 

Is there any known major database (I'm thinking Posgres, Oracle, SqlServer, DB2) having JDBC drivers not supporting shared prepared statements ? (I cannot really find this information on the web).

If the JDBC driver's PreparedStatement.isPoolable() method returns true, then it should support shared prepared statements.

The only reason I can imagine why a JDBC driver might not support shared prepared statements is if the PreparedStatement implementation included some transient state that cannot be otherwise reset by the API methods, e.g. clearBatch(), clearPArameters

 

For the second part, if I understand correctly:

If we always use PreparedStatement in the same manner in all of our code by just instanciating it this way :

PreparedStatement statement = connection.prepareStatement(...)

and not modifying at all its default configuration (which means that it will be always marked as poolable when created, as this is the default value).

Is that the semantic of your JDBC driver?

-> It means there should be no problem at all to set 'shared-pepared-statements' to true, right ?

Yes, if the isPoolable() method of your underlying PreparedStatement return true, you should have no issues.