Watch out for this jTDS setting on MSSQL 2000

JTDS is practically the only noncommercial driver you can use with MSSQL 7.0 and 2000. Its original target, MSSQL 7.0, probably left a trace in the default settings that can cause serious performance problems on MSSQL 2000. The whole mess comes down to converting string parameters to Unicode. An explanation and a fix appeared on our company wiki, and I’m republishing it here with the author’s permission.

The jTDS driver and conversion of string parameters to Unicode

By Budík (special thanks to NkD & Tuhoslav)

The jTDS driver has a sendStringParametersAsUnicode setting that converts the parameters sent to the server in SQL statements to Unicode. The default is true, which means conversion is enabled. That’s an interesting option, but it can cause serious problems.

MSSQL 7.0 uses one character encoding for the whole database, while MSSQL 2000 lets you set the encoding column by column. That’s what led to major problems in our production deployment. A SELECT on the DCH_WW table, which has 2,500,000 rows, included this condition after parameter substitution.

...AND OSCIS = ' xyz'

But this is what reached the database.

...AND OSCIS = N' xyz'

The N means the string is converted to Unicode. The problem becomes clear once you realize the column is CHAR and the parameter is Unicode. The database doesn’t use the configured indexes and scans the whole table. The application’s design also meant that the SELECT had to run several times per request to obtain the necessary data.

The fix is fairly simple. Set sendStringParametersAsUnicode to false in the jTDS driver. After restarting the application server, the SELECT reaches the database without the conversion to Unicode. The index is used automatically, exactly what we wanted here, and a query that previously took five seconds at best takes 80 milliseconds.

A small note. You won’t see the N in the application log. It shows up in detailed monitoring with SQL Profiler.

Finally, here’s what the jTDS driver documentation says.

sendStringParametersAsUnicode (default — true)

sendStringParametersAsUnicode (default — true)
Determines whether string parameters are sent to the SQL Server database in Unicode or in the default character encoding of the database. This seriously affects SQL Server 2000 performance since it does not automatically cast the types (as 7.0 does), meaning that if a index column is Unicode and the string is submitted using the default character encoding (or the other way around) SQLServer will perform an index scan instead of an index seek.