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 and merging pr #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:

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 using the following method:

setEnablePrepareOnFirstPreparedStatementCall(boolean value)

2- Prepared Statement Metatada caching

As of 6.3.0-preview version, Microsoft JDBC driver for SQL Server supports prepared statement 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)

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

Method	Description
int getStatementPoolingCacheSize()	Returns the size of the prepared statement cache for this connection. A value less than 1 means no cache.
int getStatementHandleCacheEntryCount()	Returns the current number of pooled prepared statement handles.
boolean isStatementPoolingEnabled()	Whether statement pooling is enabled or not for this connection.
setStatementPoolingCacheSize(int value)	Specifies the size of the prepared statement cache for this connection. A value less than 1 means no cache.
boolean getDisableStatementPooling()	Returns true if statement pooling is disabled.
void setDisableStatementPooling(boolean value)	Sets statement pooling to true or false.