Using Adaptive Buffering
Adaptive buffering is a new feature of the Microsoft SQL Server 2005 JDBC Driver version 1.2, and is designed to retrieve any kind of large-value data without the overhead of server cursors.
Normally, when the Microsoft SQL Server 2005 JDBC Driver executes a query, the driver retrieves all of the results from the server into application memory. Although this approach minimizes resource consumption on the SQL Server, it can throw an OutOfMemoryError in the JDBC application for the queries that produce very large results.
In order to allow applications to handle very large results, the Microsoft SQL Server 2005 JDBC Driver version 1.2 provides adaptive buffering. An application developer may want to use adaptive buffering with a statement for the following reasons:
The query produces a very large result set: The application can execute a SELECT statement that produces more rows than the application can store in memory. In previous releases, the application had to use a server cursor to avoid an OutOfMemoryError. In the version 1.2 release, the application can use adaptive buffering instead. Adaptive buffering provides the ability to do a forward-only read-only pass of an arbitrarily large result set without requiring a server cursor.
The query produces very large SQLServerResultSet columns or SQLServerCallableStatement OUT parameter values: The application can retrieve a single value (column or OUT parameter) that is too large to fit entirely in application memory. Adaptive buffering allows the client application to retrieve such a value as a stream, by using the getAsciiStream, the getBinaryStream, or the getCharacterStream methods. The application retrieves the value from the SQL Server as it reads from the stream.
Setting Adaptive Buffering
There are three ways that an application can request that statement execution should use adaptive buffering:
The application can set the connection property responseBuffering to "adaptive". For more information on setting the connection properties, see Setting the Connection Properties.
The application can use the setResponseBuffering method of the SQLServerDataSource object to set the response buffering mode for all connections created through that SQLServerDataSource object.
The application can use the setResponseBuffering method of the SQLServerStatement class to set the response buffering mode for a particular statement object. Note that the application must cast the statement object to a SQLServerStatement to use the setResponseBuffering method.
When large values are read once by using the get<Type>Stream methods, and the ResultSet columns and the CallableStatement OUT parameters are accessed in the order returned by the SQL Server, adaptive buffering minimizes the application memory usage when processing the results. When using adaptive buffering:
The get<Type>Stream methods defined in the SQLServerResultSet and SQLServerCallableStatement classes return read-once streams by default, although the streams can be reset if marked by the application. If the application wants to
resetthe stream, it has to call themarkmethod on that stream first.The get<Type>Stream methods defined in the SQLServerClob and SQLServerBlob classes return streams that can always be repositioned to the start position of the stream without calling the
markmethod.
When the application uses adaptive buffering, the values retrieved by the get<Type>Stream methods can only be retrieved once. If you try to call any get<Type> method on the same column or parameter after calling the get<Type>Stream method of the same object, an exception is thrown with the message, "The data has been accessed and is not available for this column or parameter".
Minimizing Application Memory Usage
Developers should follow these important guidelines to minimize memory usage by the application:
Avoid using the connection string property selectMethod=cursor to allow the application to process a very large result set. The adaptive buffering feature of the Microsoft SQL Server 2005 JDBC Driver version 1.2 allows applications to process very large forward-only, read-only result sets without using a server cursor.
Read large text or binary values as streams by using the getAsciiStream, the getBinaryStream, or the getCharacterStream methods instead of the getBlob or the getClob methods. In the version 1.2 release, the SQLServerCallableStatement class provides new get<Type>Stream methods for this purpose.
Ensure that columns with potentially large values are placed last in the list of columns in a SELECT statement and that the get<Type>Stream methods of the SQLServerResultSet are used to access the columns in the order they are selected.
Ensure that OUT parameters with potentially large values are declared last in the list of parameters in the SQL used to create the SQLServerCallableStatement. In addition, ensure that the get<Type>Stream methods of the SQLServerCallableStatement are used to access the OUT parameters in the order they are declared.
Avoid executing more than one statement on the same connection simultaneously. Executing another statement before processing the results of the previous statement may cause the unprocessed results to be buffered into the application memory.