Is your Java driver killing performance?

A serious performance problem can arise when connecting a Java application to a SQL Server 2000 database. Many developers are not aware of the issue or of how to avoid it. 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. You can easily correct this problem 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 executed several times a second on a busy system and always caused a table scan. It took 50,000 page reads to retrieve a customer record from a table with a nonclustered 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 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 five page reads. I went looking for other occurrences of this problem and found dozens that were seriously impacting performance.

Fortunately, the fix is simple. A parameter in all the Java database drivers I have examined controls this behavior. By default, the parameter is set to return Unicode parameters. It must be changed to return ASCII parameters. The parameter has different names in different drivers. Here is a list by vendor. (It is probably not complete but covers all the drivers in common use that I am aware of.)

Vendor	Parameter
JSQLConnect	asciiStringParameters
JTDS	sendStringParametersAsUnicode
DataDirectConnect	sendStringParametersAsUnicode
Microsoft JDBC	sendStringParametersAsUnicode
WebLogic Type 4 JDBC SQL Server driver	sendStringParametersAsUnicode

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 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.

home about us contact us email us SQL Server Newsletter

Copyright © 2004-2010 SQL Consulting Inc. All rights reserved