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 parameter, configuration, specific API, link or trick that can remove the pause from the second and subsequent query executions?
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.