#40 fix performance with PreparedStatement and varchar indices

[Lari Hotari]

Please fix the performance problem described here:

http://cephas.net/blog/2005/12/02/java_jtds_preparedstatement_and_varchar.html

quoting the article:
"It quickly became clear what the problem was. Here's
the SQL created by the prepareStatement() method:

create proc #jtds000001 @P0 varchar(4000) as UPDATE
mytable SET x = 1 WHERE y = @P0

and then the executeUpdate() method:

exec #jtds000001 N'005QDUKS1MG8K'

See the problem? The JTDS driver turned the 13 byte
varchar column into a 4000 byte varchar column (the
maximum number of bytes for a column) and then prefixed
the parameter with 'n', which is used to identify
Unicode data types. This substitution caused the query
processor to ignore the index on 'y' and do an index
scan instead of an index seek."

I just happened to see this article and javablogs and I
wanted to post it here since I'd like to get a fast fix
to this issue.

All credits go to the author, Aaron Johnson, of this
nice blog article:
http://cephas.net/blog/2005/12/02/java_jtds_preparedstatement_and_varchar.html

[Brian Heineman]

Logged In: YES
user_id=716174

Lari,

Most SQL Server JDBC drivers have a parameter for
controlling how character data is sent to the database. You
can change the default behavior of the jTDS driver using
sendStringParametersAsUnicode=false.

From the jTDS FAQ (http://jtds.sourceforge.net/faq.html):

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. For Sybase,
determines if strings that cannot be encoded in the server's
charset are sent as unicode strings. There is a performance
hit for the encoding logic so set this option to false if
unitext or univarchar data types are not in use or if
charset is utf-8.

Cheers,
-Brian

[Alin Sinpalean]

Logged In: YES
user_id=564978

As Brian explained, jTDS already has a workaround
implemented for this JDBC/SQL Server limitation. Closing
tracker.

Alin.