PreparedStatement metadata caching

Two changes were implemented in the driver that enhance the performance which will discuss in here.

1- Batching of unprepare for prepared statements

Since version 6.1.6-preview, an improvement in performance was implemented through minimizing server round trips to Sql Server. Previously, for every prepareStatement query, a call to unprepare was also sent. For example for a query like:

SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) connection.prepareStatement("insert into table1 
values(?)");
for (int i = 0; i < 5; i++) {
  pstmt.setString(1, "hello");
  pstmt.execute();
  pstmt.close();
}

Profiler would show the following output:

declare @p1 int set @p1=1 exec sp_prepexec @p1 output,N'@P0 int',N'select * from table1 where col1 =@P0',0 select @p1
exec sp_unprepare 1
declare @p1 int set @p1=2 exec sp_prepexec @p1 output,N'@P0 int',N'select * from table1 where col1 =@P0',0 select @p1
exec sp_unprepare 2
...

After version 6.1.6-preview based on solutions proposed in #166, driver is batching unprepare queries up to the threshold ServerPreparedStatementDiscardThreshold which has default value of 10.

Note: Users can change the default value with the following method:

SQLServerConnection.setServerPreparedStatementDiscardThreshold(int value)

One more change introduced in 6.1.6-preview is that prior to this, driver would always call sp_prepexec. For example:

declare @p1 int set @p1=1 exec sp_prepexec @p1 output,N'@P0 int',N'select * from table1 where col1 =@P0',0 select @p1

Now, for the first execution of a prepared statement, driver will call sp_executesql and for the rest it will execute sp_prepexec and will assign a handle to it. For example, for the same code above the profiler outputs this:

exec sp_executesql N'select * from table1 where col1 =@P0',N'@P0 int',0
declare @p1 int set @p1=1 exec sp_prepexec @p1 output,N'@P0 int',N'select * from table1 where col1 =@P0',1 select @p1
exec sp_execute 1,1,N'hello',3
exec sp_execute 1,1,N'hello',3
exec sp_execute 1,1,N'hello',3

Note: Users can change this default behavior to the previous versions of always calling sp_prepexec by setting enablePrepareOnFirstPreparedStatementCall to true for the connection in the connection String or using the following method:

SQLServerConnection.setEnablePrepareOnFirstPreparedStatementCall(boolean value)

Summary of Connection Properties governing this feature:

Connection Property	Default	Description
serverPreparedStatementDiscardThreshold	10	Controls how many outstanding prepared statement discard actions (sp_unprepare) can be outstanding per connection before a call to clean-up the outstanding handles on the server is executed.
enablePrepareOnFirstPreparedStatementCall	false	If set to true, driver calls sp_prepexec for the first execution instead of calling sp_executesql

2- Prepared Statement Metadata Caching

As of 6.3.0-preview version, Microsoft JDBC driver for SQL Server supports prepared statement metadata caching. Prior to v6.3.0-preview, if one executes a query that has been already prepared and stored in the cache, calling the same query again will not result in preparing it. Now, the driver will lookup the query in cache and find the handle and execute it with sp_execute.

For example:

SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) connection.prepareStatement("insert into table1 values(?)");
        for (int i = 0; i < 3; i++) {
            pstmt.setString(1, "hello");
            pstmt.execute();
}

 pstmt = (SQLServerPreparedStatement) connection.prepareStatement("insert into table2 values(?)");
        for (int i = 0; i < 3; i++) {
            pstmt.setString(1, "hello");
            pstmt.execute();
}

 pstmt = (SQLServerPreparedStatement) connection.prepareStatement("insert into table1 values(?)");
        for (int i = 0; i < 3; i++) {
            pstmt.setString(1, "hello");
            pstmt.execute();
}

The result of profiler will look like this:

exec sp_executesql N'insert into table1 values(@P0,@P1,@P2)',N'@P0 int,@P1 nvarchar(4000),@P2 int',1,N'hello',3
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 int,@P1 nvarchar(4000),@P2 int',N'insert into table1 values(@P0,@P1,@P2)',1,N'hello',3
select @p1
exec sp_execute 1,1,N'hello',3

//The query has changed (table name), so it will result in another call of `sp_executesql` and then `sp_prepexec`
exec sp_executesql N'insert into table2 values(@P0,@P1,@P2)',N'@P0 int,@P1 nvarchar(4000),@P2 int',1,N'hello',3
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P0 int,@P1 nvarchar(4000),@P2 int',N'insert into table2 values(@P0,@P1,@P2)',1,N'hello',3
select @p1
exec sp_execute 2,1,N'hello',3

// The query is the same (The first one) so it will pick up from cache
exec sp_execute 1,1,N'hello',3
exec sp_execute 1,1,N'hello',3
exec sp_execute 1,1,N'hello',3

Prepared Statement Metadata caching is disabled by default. In order to enable it, you will need to call the following method on the connection object:

setStatementPoolingCacheSize(int value) //with value being the desired cache size (any value bigger than 0)
setDisableStatementPooling(boolean value) //With false, allowing the caching to take place

For example:

connection.setStatementPoolingCacheSize(10)
connection.setDisableStatementPooling(false)

Summary of Connection Properties governing this feature:

Connection Property	Default	Description
disableStatementPooling	false	Controls whether statement pooling is enabled or not for this connection.
statementPoolingCacheSize	0	Controls the size of the prepared statement cache for this connection. '0' means no cache.

List of the newly added APIs introduced with this feature are the following:

Method	Description
boolean getDisableStatementPooling()	Returns whether statement pooling is enabled or not for this connection.
setDisableStatementPooling(boolean value)	If false, enables statement pooling to be used in coupling with statementPoolingCacheSize value > 0.
int getStatementPoolingCacheSize()	Returns the size of the prepared statement cache for this connection. '0' means caching not enabled.
setStatementPoolingCacheSize(int value)	Sets the size of the prepared statement cache for this connection. Works if disableStatementPooling is set to false and value > 0.
boolean isPreparedStatementCachingEnabled()	Returns whether prepared statement caching is enabled for this connection or not.
int getStatementHandleCacheEntryCount()	Returns the current number of pooled prepared statement handles.