SQL queries to MSSQL contains pauses even with MARS enabled_问答_开发者

We are testing JDBC drivers from jTDS and Microsoft, and we are suffering from unwanted pauses in query execution. Our application opens many ResultSets and fetches only a few rows from each. Each query selects about 100k rows, but we fetch only about 50 (which is enough to fill a page). The problem is that every query after the first contains a pause of about 2s, on which the driver loads all rows from the previous ResultSet to a temporary storage (memory or disk), so they can be traversed later. Because we have about 6 queries in worst scenarios, there will be a pause of about 10s, which makes the application unresponsive to the user. MSSQL version is 2005.

To remove such pauses, we've tried to enable MARS (Multiple Active Result Sets) via connection string parameters of Microsoft JDBC driver (due to lack of documentation, we tried everything that is listed on https://sites.google.com/site/sqlconnect/sql2005strings). Example of connection string:

jdbc:sqlserver://TESTDBMACHINE;instanceName=S2005;databaseName=SampleDB;MarsConn=yes

But none of them solves the problem. Microsoft JDBC driver seems to accept anything at connection string - if you replace MarsConn=yes by PleaseBeFast=yes, MS driver ignores the parameter and doesn't even log the fact. I don't know if MARS is a client-only feature that just caches rows from a previously active result set, or if it's a server feature. I don't even know how to detect, from the server side, if a given connection is using MARS. If you can comment on this, it will be welcome.

Another solution for the pause was to use scrollable (bidirectional) result sets. This removes the pause, but makes fetch time 80% slower and more network consuming. We are now considering to implement a JDBC connection wrapper that keeps a pool of actual connections and automatically issue queries to distinct "ResultSet free" connections. But this is somewhat cumbersome because we need to keep a link between each connection and its active ResultSet. Also it would consume more connections from the server and may cause troubles to DBAs. And this solution doesn't help if there is an active transaction, on which all queries must be issued on the same connection.

Do you know some par开发者_运维知识库ameter, configuration, specific API, link or trick that can remove the pause from the second and subsequent query executions?

fix your SQL queries! why only use the first 50 or so from 100k rows?? use TOP 100 or something like that! There is no reason that the application should be filtering 100k rows, that is the job of the database.

Far more important that your client woes is what happens on the server side. Since you issue queries and then you stop reading the result the server will have to suspend the query in the middle of the execution because the network buffers are full and has no room to write the result into. A query suspended in the middle of the execution is consuming a lot of resources: memory, locks and, most importantly, a worker thread (there are very few of these lying around).

Issue queries for only the data you need, consume all the data, free the connection and, more importantly, the server resources. If your queries are to complex, go back to the drawing board and redesign your data model to properly answer, efficiently, the queries your requesting from it. Right now you are totally barking up the wrong tree, you're simply asking how to make a bad situation worse.

We've created an ODBC data source using the SQL Server driver (Native Client 10 - sqlncli10.dll). This ODBC data source was configured to enable MARS (key HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ datasource, value of MARS_Connection must be Yes). Then we used Sun's JDBC-ODBC bridge, and voilá! The pauses were gone, and surprisingly, fetch time became faster than JTDS and MSSQL pure Java JDBC drivers!

According to http://msdn.microsoft.com/en-us/library/ms345109(SQL.90).aspx, MARS is a server-aided feature. The pure Java drivers (from JTDS and MSSQL) doesn't seem to support served-based MARS (at least we couldn't enable it after many configuration changes). Because most of our user base that uses MSSQL Server runs on Windows (no surprise), we are about to make the switch from JTDS to JDBC-ODBC. Both Native Client ODBC driver and JDBC-ODBC bridge seems to be mature, full featured and up-to-date solutions, so I guess there should be no problems. If you know some, please comment!

Linux based users will still use JTDS. Since now we know that MARS is a server-aided feature, we'll fill a feature request for JTDS and Microsoft to support MARS in their pure Java JDBC drivers.