A Cursor By Any Other Name

Recently again I had to give customer DBA team advice on most performing JDBC settings for data access from SQL Server via MS JDBC Driver. Unfortunately, again majority of “SQL People” after looking at wait stats and traces from SQL Server and seeing some old fashioned data access cursors with commands like see sp_cursoruprepare, sp_cursorfetch, etc. just refuse to go deeper there. As far as majority of Java folks unfortunately they are fairly oblivious to database performance working on the front end and happy just to get data they need from the database.
So when they run into performance issues they tend to blame the driver first. Oh, if we could only change that JDBC driver…
My statement to that is always:

Driver is just a part of the stack. Number of options are controlled from WebSphere, Oracle TopLink settings, etc. In my quick drawing below you can see that its application that controls driver properties and way driver fetches not visa versa:

driver

JDBC Cursor Types and SQL Server
SQL Server supports number of cursor types. Full list is available at “Understanding Cursor Types”
Two of these are the most performing (TYPE_SS_DIRECT_FORWARD_ONLY,TYPE_SS_SERVER_CURSOR_FORWARD_ONLY), especially TYPE_SS_DIRECT_FORWARD_ONLY,
which corresponds to SQL forward only\read only cursor. Cursor type is one of the fields on the SQLServerResultSet class , so if I wanted to use TYPE_SS_DIRECT_FORWARD_ONLY cursor:

public static void executeStatement(Connection con) {
try {
String SQL = "SELECT TOP 10 * FROM Person.Contact";
Statement stmt = con.createStatement (SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, +
SQLServerResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery(SQL);

while (rs.next()) {
System.out.println(rs.getString(4) + " " + rs.getString(6));
}
rs.close();
stmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}

Now above forward only read only cursor isn’t updateable as you understand, but its fastest as fetching data. As far as updatable cursors, using server-side cursors typically can improve performance, but server-side cursors cannot be used with scroll-insensitive result sets or with scroll-sensitive result sets that are not generated from a database table that contains a primary key. To use server-side cursors, set the UseServerSideUpdatableCursors property to true.
InsensitiveResultSetBufferSize
To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.

SendStringParametersAsUnicode to false when querying against VARCHAR data types in SQL Server to eliminate implicit conversions . Most of the JDBC drivers pass string parameters to SQL Server as Unicode, by default. The problem here is, if the Unicode parameters reference the VARCHAR key columns in an index, the SQL Server engine will not use the appropriate index for query resolution, thereby increasing the unwanted table scans.

Use PreparedStatement or CallableStatement interface vs. Statement Inteface in JDBC. You will get better performance in general executing prepared statements or stored procedures with these interfaces vs. Statement. PreparedStatement query is compiled and placed in SQL Procedure cache, in that way its very similar to performance boost and cache reuse you get with parameterized sp_executessql calls vs. standard exec in SQL Server.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s