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 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. 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 initiated 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.
Another Separate Issue
There is another problem with the current Microsoft JDBC driver that I should be mention here because it also relates 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 than 4000 characters, a patch is available. However, rumor has it that Microsoft is going to announce a new JDBC driver built on a code base it 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.
Contact me if you have questions, comments or corrections regarding anything in this article.
About the Author
Kurt Survance is the president of SQL Consulting, Inc. (http://www.sql-consulting.com/), a database consulting group specializing in SQL Server performance tuning. Kurt has been working with SQL Server almost as long as it has been around. First certified by Microsoft in 1993, he still holds current certifications as an MCSE and MCDBA. You can contact Kurt at ksurvance@sql-consulting.com.
]]>