Is your Java database driver killing SQL Server performance?

Executive Summary

There is a serious performance problem that can arise when connecting a Java application to a SQL Server 2000 database. Many developers are not aware of the issue or how to avoid it. The essence of the problem is this: By default, most, if not all, Java middleware drivers send string parameters to SQL Server as unicode. If the unicode (16 bit) parameters reference VARCHAR (8 bit) key columns in an index, SQL Server will not use the index to resolve the query, resulting in many unnecessary table scans. Obviously, this can have horrible consequences for application performance. Also, it is bewildering to the developer because it appears as if proper indexing has not and can not prevent the table scan. This problem can be easily corrected by resetting a default parameter in the Java driver. The parameter name and setting value vary from driver to driver.

Details

I recently encountered this problem when called to a client site to resolve performance problems. I found that a simple lookup was executed several times a second on a busy system and always initiated a table scan. It took 50,000 page reads to retrieve a customer record from a table with a non-clustered index on the appropriate columns (last and first name in this example). Using SQL Profiler, I pulled the statement out of the prepared SQL that the Java driver was packaging and sending to SQL Server. It looked something like this:

SELECT firstname,
lastname,
homephone
FROM customer
WHERE lastname = @P1
AND firstname = @P2

The parameters in sp_execute were sent as N’Princeps’ N’Gavrilo’. I pasted the code into Query Analyzer. I ran it and saw the same 50,000 page reads. Then I removed the ‘N’ unicode indicator from the parameter values, effectively changing them to varchar values. The same statement executed with 5 page reads. I went looking for other occurences of this problem and found dozens that were seriously impacting performance.

Fortunately, the fix is simple. There is a parameter that controls this behavior in all the Java database drivers I have examined . By default, the parameter is set to return parameter values in unicode. It must be changed to return ascii parameters. The parameter is named differently in different drivers. Here is a list by vendor. It is not complete but covers most of the drivers in common use at the time of this writing:

The parameter values are generally boolean and usually need to be set to ‘false’ or zero. If you research this issue on the web, you will find sources that insist this parameter is case-sensitive, but, in our in my limited experience, that does not seem to be true.

You should confirm all of this using the documentation for the specific version of your driver. There are some variations in behavior between drivers from different vendors and even between driver versions from the same vendor. For example, older versions of JSQLConnect used ‘1’ as the parameter setting and will not recognized ‘true’ as a valid parameter. Worse, the driver does not complain, it just silently ignores the error.

Very old versions of many drivers do not implement the parameter at all. Again, they silently ignore any setting of the parameter. In that case, upgrading to a new version or different driver is your only option.

Another Issue

There is another problem that is specific to the current Microsoft JDBC driver that should be mentioned here because it is also related to the unicode issue. String data that is retrieved from varchar output parameters is truncated to 4000 characters. This problem does not occur with ResultSet objects or input parameters. In the unlikely event that your application returns output parameters larger that 4000 characters, a patch is available. However, rumor has it that Microsoft is going to announce a new JDBC driver built on a codebase they acquired from another popular vendor. The announcement will likely come shortly after the release of SQL Server 2005. It might be best to wait for that driver.

Conclusion

Both Java developers and SQL Server DBA’s need to understand the details of Java database driver configuration. As we have seen, not being aware of the default conditions can cause major performance problems that are difficult to diagnose. On a more positive note, here is your chance to be an instant hero if your application is suffering this problem. The fix is simple, quick, and complete. The results can be astonishing.

Let visitors to your site take advantage of this SQL Server knowledgebase, have a free consultation with a certified SQL Server Expert or access other sql resources here.

It's easy to enhance your website with these free resources! Just click on the link above.