What is adaptive response buffering and why should I use it?

What is adaptive response buffering and why should I use it?

  • Comments 18

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

Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post
  • PingBack from http://ibrahimdemir.wordpress.com/2007/09/26/sql-server-jdbc-driver-12-ctp/

  • My experience using this new setting do not seem to square with its description. I altered my connection to use selectMethod=direct and responseBufering=adaptive. When I executed my query and watched my console output and Task Manager network activity I saw my "exec query" println, then several minutes of sustained incoming data, then ...processing N records..." as my loop read the data. While the loop was running there was no network activity. I would have expected to see network activity concurrent with moving through the result set. What am I missing?

  • Do you have a typo in your connection string?  The responseBuffering connection property is spelled with two 'f's.

    David Olix, SQL Server

  • Does responseBuffering option work also when connecting to SQL Server 2000?

  • it works perfectly for me. I could get a 400000 records query without any Out of Memory errors. THANK YOU.

  • if I have a image column called "image_col", and the execute:

    1: ResultSet rs = Statement.execute ("select image_col from table_";

    2: InputStream in = rs.getBinaryStream("image_col");

    the problem is that after (1), it already allocating memory with the size of the image_col, if (1) returns 1 row, and there is 100M, then it will allocate 100M memory in the Java process already.

    is this something expected?

  • A simple select of a single large column in a forward only read only result set should work fine with adaptive buffering.

    Is selectMethod=cursor set as a connection property?  That property inhibits adaptive response buffering.  If you were using selectMethod=cursor to avoid OOM errors before v1.2, that property should be removed to use adaptive response buffering instead.

    Is the ResultSet scrollable or updatable?  If so, you'll need to crank down the fetch size to 1 and set adaptive response buffering directly on the Statement object (cast it to SQLServerStatement to get to the setResponseBuffering method) before executing.

  • Thanks foir the quick reply.

    here is the code snippet for how I create a DB connection and how to do a query, but if I select a 100M blob, I still see 100M drop of free memory (you need to start JVM with -ms500m -mx500m),

    I will appreciate if you could suggest if I should do it differently, and if not if you can spend sometime to reproduce it,

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    Connection conn = DriverManager

    .getConnection("jdbc:sqlserver://" + dbServer+";user="+user +

    ";password="+password +";databaseName="+ dbName +";responseBuffering=adaptive");

    System.gc();

    long  m1 = Runtime.getRuntime().freeMemory();

    System.out.println(" the memory before is  :" +  m1));

    PreparedStatement ps2 = conn.prepareStatement(" select c2 from dbo.table1 where c1=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    ps2.setFetchSize(1);

    ((SQLServerStatement ) ps2).setResponseBuffering("adaptive");

    ps2.setInt(1, number);

    ResultSet rs = ps2.executeQuery();

    ps.close(); // cause server call of "unprepare"

    System.gc();

    long  m2 = Runtime.getRuntime().freeMemory();

    System.out.println(" the memory after select is  :" +  (m2)  );

    InputStream inc = null;

    if( rs.next() ) {

    inc = rs.getBinaryStream("c2");

    ();

    }

  • I am finding that the adaptive buffering returns rows at the same rate as the server side cursor.  It may improve memory on large resultsets but I cannot seem to get performance up to the level of a similar app written in .NET using SqlDataReaders.  The JDBC performance is really bad.

    My app creates 6 threads, each with their own connection to the same SQL Server instance query different tables with the same replicated data.  The resultset will return 83,000 rows of 17 columns of data (a good mix of data types...no blobs).  The app will loop through every row and column but do nothing with the data, except reading it.

    JDBC averages 79 seconds

    .NET averages 0.90 seconds

    Any ideas?

    Here is a snippet of the java thread code:

    Connection c = DriverManager.getConnection("jdbc:sqlserver://" + host +

                  ":1433;databaseName=" + database + ";user=" + user + ";password=" + password +

                  ";responseBuffering=adaptive;");

    long start = System.currentTimeMillis();

    Date startDate = new Date(start);

    //  Statement stmt = c.createStatement();

    Statement stmt = c.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY,

                 SQLServerResultSet.CONCUR_READ_ONLY);

    ((SQLServerStatement) stmt).setResponseBuffering("adaptive");

    ResultSet rs = stmt.executeQuery(sql);

    rs.setFetchSize(10000);

    ResultSetMetaData rsMetaData = rs.getMetaData();

    int numberOfColumns = rsMetaData.getColumnCount();

    String tempVar;

    int rowCounter = 0;

    // loop through rows in resultset

    while (rs.next()) {

     // loop all columns and read value

     for (int i=1; i<numberOfColumns; i++) {

     tempVar = rs.getString(i);

     }

     rowCounter += 1;

    }

    Is the JDBC code that much different at the TDS protocol than .NET?

    Thanks

  • Hi

    I have a scenario where I send several queries to sql server simultaniously (processed in different threads). OOM problem dissapears when I execuite only one query but resurface when more are beeing executed in paralell.

    Questions:

    what is the size of the client side buffer?

    Can we control the size of the client side buffer?

    TIA

    Knut

  • Are you using adaptive buffering or full buffering? With full buffering there is no limit to the client cache and you cannot control the size of the cache.

  • I'm using adaptive response buffering, my scenario is like this.

    I have a component that reads a large resultset.

    First test: read data without adaptive response buffering -> OOM error

    Second test: Turn on adaptive response buffering (using parameters on url) -> No OOM error

    Third test: Start several reader threads in parallel with adaptive response buffering enabled (as in prev test) -> OOM error

    My question(s):

    "adaptive response buffering" indicates to me that the driver use some memory for buffering, how is the size of this buffer determined?

    Can/Will the size of the client side buffer be adjusted during execution?

    Can I set the maximum memory used for buffering?

  • Adaptive buffering implies it buffers it absolutely has to. The size of the buffer is not limited or controlled in anyway.

    The problem is that when you have several readers working off a single connection, we are forced to buffer the reply from the previous requests to get the data for the current request.

    There are few ways of solving this.

    1) Using server cursors.

    2) Using multiple connections instead of a single connection. However you may not want to create too many connections.

  • Hi thanks for your answer.

    Just to be clear, I have one connection and one statement per thread. But all these connections will compete over the same available memory. So if SQL server can deliver rows faster than I can process them I will se the OOM error at some point?

    So how is memory size used for buffering contolled in this scenario? What's the adaptiv part?

    Thanks

    Knut

  • If you are using one statement per connection then the driver will not load all the data SQLServer is sending. IE the driver will block and wait for you to consume the data before reading more data from the server. The adaptive part will kick in only when if you are trying to execute another command from the same connection.

Page 1 of 2 (18 items) 12