Working with Statements and Result Sets
When you work with the Microsoft SQL Server 2005 JDBC Driver and the Statement and ResultSet objects that it provides, there are several techniques that you can use to improve the performance and reliability of your applications.
Use the Appropriate Statement Object
When you use one of the JDBC driver Statement objects, such as the SQLServerStatement, SQLServerPreparedStatement, or the SQLServerCallableStatement object, make sure that you are using the appropriate object for the job.
If you do not have OUT parameters, you do not need to use the SQLServerCallableStatement object. Instead, use the SQLServerStatement or the SQLServerPreparedStatement object.
If you do not intend to execute the statement more than once, or do not have IN or OUT parameters, you do not need to use the SQLServerCallableStatement or the SQLServerPreparedStatement object. Instead, use the SQLServerStatement object.
Use the Appropriate Concurrency for ResultSet Objects
Do not ask for updatable concurrency when you create statements that produce result sets unless you actually intend to update the results. The default forward-only, read-only cursor model is fastest for reading small result sets.
Limit the Size of Your Result Sets
Consider using the setMaxRows method (or SET ROWCOUNT or SELECT TOP N SQL syntax) to limit the number of rows returned from potentially large result sets. If you must deal with large result sets, consider using a server cursor by setting the connection string property selectMethod=cursor. Alternatively, you can create the statement with com.microsoft.sqlserver.jdbc.SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY (2004) scrollability for forward-only, read-only access, and then use the setFetchSize method to tune performance.
Use the Appropriate Fetch Size
For read-only server cursors, the tradeoff is round trips to the server versus the amount of memory used in the driver. For updatable server cursors, the fetch size also influences the sensitivity of the result set to changes and concurrency on the server. Updates to rows within the current fetch buffer are not visible until an explicit refreshRow method is issued or until the cursor leaves the fetch buffer. Large fetch buffers will have better performance (fewer server round trips) but are less sensitive to changes and reduce concurrency on the server if CONCUR_SS_SCROLL_LOCKS (1009) is used. For maximum sensitivity to changes, use a fetch size of 1. However, note that this will incur a round trip to the server for every row fetched.
Use Streams for Large IN Parameters
Use streams or BLOBs and CLOBs that are incrementally materialized to handle updating large column values or sending large IN parameters. The JDBC driver "chunks" these to the server in multiple round trips, allowing you to set and update values larger than what will fit in memory.