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

SQL SERVER JDBC DRIVER 1.2 CTP « BEN GE??ERKEN… [A Blog by ??brahim DEM??R ]

SQL SERVER JDBC DRIVER 1.2 CTP « BEN GE??ERKEN… [A Blog by ??brahim DEM??R ] — Wed, 26 Sep 2007 09:27:32 GMT

PingBack from http://ibrahimdemir.wordpress.com/2007/09/26/sql-server-jdbc-driver-12-ctp/

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

David — Fri, 25 Jan 2008 06:42:42 GMT

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?

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

David Olix, SQL Server — Fri, 25 Jan 2008 20:21:22 GMT

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

David Olix, SQL Server

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

asta — Mon, 10 Mar 2008 13:10:03 GMT

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

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

Sandra — Wed, 07 May 2008 23:13:28 GMT

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

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

haibing qiao — Thu, 08 May 2008 20:49:19 GMT

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?

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

David Olix, SQL Server — Thu, 08 May 2008 21:20:38 GMT

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.

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

haibing qiao — Thu, 08 May 2008 22:34:05 GMT

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");

();

}

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

mpeebles — Wed, 28 May 2008 00:27:39 GMT

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

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

knuterikt — Tue, 26 Aug 2008 11:28:23 GMT

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

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

Mugunthan Mugundan - MSFT — Tue, 26 Aug 2008 18:48:42 GMT

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.

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

knuterikt — Wed, 27 Aug 2008 14:59:18 GMT

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?

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

Mugunthan Mugundan - MSFT — Wed, 27 Aug 2008 18:12:27 GMT

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.

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

knuterikt — Thu, 28 Aug 2008 02:30:51 GMT

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

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

Mugunthan Mugundan - MSFT — Thu, 28 Aug 2008 03:07:30 GMT

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.

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

knuterikt — Thu, 28 Aug 2008 17:11:57 GMT

Ok but still the driver allocates memory to hold some rows from the result set? Do you know how much memory it will consume?

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

Anupam — Mon, 20 Oct 2008 22:00:54 GMT

Does responseBuffering option works when connecting to SQL Server 2000?