Set a default row prefetch in SQL Server using JDBC driver

Question

I have an application where I want to define the default number of rows to prefetch for a connection, using for both Oracle and SQL Server drivers. The Oracle driver has a the OracleConnection interface, which provides the setDefaultRowPrefetch method to do it, but I didn't find anything equivalent for the SQL Server driver.

There is a method to define the default row prefetch for a connection using the SQL Server JDBC driver?

Answer 1

The usual ways to set row fetch size are:

1. Via java.sql.Connection vendor implementation class custom method (e.g. OracleConnection.setDefaultRowPrefetch)
2. Via java.sql.Statement.setFetchSize(int): gives a hint to the driver as to the row fetch size for all ResultSets obtained from this Statement. This method is inherited by PreparedStatement and CallableStatement. Most JDBC drivers support it.
3. Via java.sql.ResultSet.setFetchSize(int): gives a hint to the driver as to the row fetch size for all this ResultSet.

MS SQL Server JDBC driver does not support any of these ways:

1. MSSQL driver has no such a method.
2. Unfortunately, while most drivers respect the hint, the MSSQL driver does not. So not useful for you. See What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?
3. Same problem as Statement.

By default it retrieves all the rows from database unless you specify cursor type in the JDBC driver. MSSQL driver can't directly control the fetch size using the usual methods.

Solutions:

• Cast your Statement to SQLServerStatement and use the method setMaxRows(int). Why they didn't implement this within the standard method Steve Ballmer only knows ;^)
• Create your driver with a cursor type. The default fetch size for a cursor is 1. Set the Connection string property selectMethod=cursor. Alternatively, you can create the Statement with com.microsoft.sqlserver.jdbc.SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY scrollability for forward-only, read-only access, and then use the setFetchSize method to tune performance. http://technet.microsoft.com/en-us/library/aa342344%28SQL.90%29.aspx
• Use (proprietary) SQL to limit the number of rows returned (not the same thing as setting the fetch size): SET ROWCOUNT or SELECT TOP N
• Switch to open source jTDS driver, specially made to overcome the problems of the SQL Server driver. It's a superior driver.