What is adaptive response buffering and why should I use it?
Do you currently use selectMethod=cursor to work around OOM errors with very large result sets? Do you find that the driver seems to consume quite a lot of memory for forward only read only result sets? Does it seem like it takes a long time to return from Statement.executeQuery for simple SELECT statements that return many rows? Do you wish you could get that 600MB LOB you streamed into a column back out again without needing loads of memory to avoid an OOM? If you answered 'yes' to any of these questions, then you'll want to use adaptive response buffering.
Adaptive response buffering is a new feature of the v1.2 JDBC driver that allows the driver to reduce memory usage and statement execution latency. With adaptive buffering, the driver retrieves statement execution results from SQL Server as the application needs them, rather than all at once. The driver also discards results as soon as the application can no longer access them.
By default, adaptive response buffering is turned off so that the v1.2 driver is 100% backward compatible with the v1.0 and v1.1 drivers. To use adaptive response buffering, you should add "responseBuffering=adaptive" to your connection URL or use the DataSource method SQLServerDataSource.setResponseBuffering("adaptive"). If you need finer control, at the statement level, you can also downcast any Statement (or PreparedStatement or CallableStatement) instance returned by the driver to a SQLServerStatement and call SQLServerStatement.setResponseBuffering("adaptive").
Ok, but what does adaptive response buffering do?
First, it helps to understand what full buffering (the default, backward-compatible behavior) does. For large results, full buffering trades off use of increased application memory usage and longer perceived statement execution latency against reduced lock contention in SQL Server. For example, by retrieving all the rows of a forward only, read only result set up front, the driver allows the server to relinquish table read locks that may block updates. But to do that, it must buffer all of the rows in memory.
But for very large result sets, a fully-buffered strategy may be infeasible. Let's say you execute a SELECT statement that returns a million rows. Adaptive response buffering allows you to process those rows through a forward only, read only result set, without incurring the overhead of a server cursor (selectMethod=cursor) and without requiring the driver buffer more than one row. With adaptive response buffering, the driver reads row data from the database as the application traverses the result set.
Now let's say that some of the selected column values are large -- really large -- like a 600MB LOB. Buffering the LOB may be infeasible. Random access to the LOB value through the Blob/Clob interfaces currently requires the whole LOB to be buffered, but it is possible to stream the entire LOB value out through an InputStream or Reader obtained through one of the ResultSet methods: getBinaryStream, getCharacterStream, or getAsciiStream. With adaptive buffering, accessing LOB data through a stream requires only a small fixed amount of memory. The only restriction is that with adaptive buffering, the LOB value can be streamed out only once. If your application needs to re-read any portion of the value, it must call the mark method on the InputStream or Reader to start buffering data that is to be re-read after a subsequent call to the reset method.
What about large CallableStatement OUT parameters?
The JDBC CallableStatement interface does not include getBinaryStream, getCharacterStream or getAsciiStream methods. But since accessing LOB values through the Blob/Clob interfaces still require the whole LOB to be buffered, these stream getter methods have been added to the SQLServerCallableStatement class. Any CallableStatement instance returned by the v1.2 driver can be downcast to a SQLServerCallableStatement instance to allow use of these methods.
How the application accesses data is important
How much benefit an application gets from adaptive response buffering is determined not by the size or type of the data accessed, but by how the application accesses the data. For example, putting a large binary column after other columns in the select list, and then accessing it using getBinaryStream rather than getBytes, allows the driver to avoid buffering the value. But if the large binary column appears somewhere in the middle of the select list, the application would need to stream it out before accessing the columns that follow it to keep the driver from buffering it.
I hope this helps explain the basics of the new adaptive response buffering feature.
David Olix, SQL Server
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights