Issues getting JTDS driver to accept sendStringParametersAsUnicode=false?

Question

Been using net.sourceforge.jtds.jdbc.Driver as my driver from MSSQL for all my applications. I had trouble with performance in a prepared statement, and learned that sendStringParametersAsUnicode=false should fix the problem. Unfortunately, I can't seem to get the driver to accept the value. I CAN get the Microsoft driver com.microsoft.sqlserver.jdbc.SQLServerDriver to accept the parameter just fine:

jdbc:sqlserver://servername:1433;databaseName=dbname;sendStringParametersAsUnicode=false

works in a persistence.xml, and in my ds.xml. Prepared statements go quickly, 100 in 22 seconds.

However, I can't seem to get the same performance boost out of JTDS. It still hangs around the prepared statement, taking several seconds on each iteration.

I've tried several variations on the string, and see the same lag in my tests (persistence.xml with Hibernate.connection.url) and Server with JTA and ds.xml.

jdbc:jtds:sqlserver://server:1433/dbname;sendStringParametersAsUnicode=false

jdbc:jtds:sqlserver://server:1433;sendStringParametersAsUnicode=false;databaseName=dbname

jdbc:jtds:sqlserver://server:1433;sendStringParametersAsUnicode=false;selectMethod=cursor;socketKeepAlive=true;databaseName=dbname

Everything I've read states the Microsoft driver is slower, and my company had issues with it in the past. I'd really like to use JTDS if possible, but can't wait for a prepared statement for 10 seconds!

Anyone had any ideas?

Thanks

Answer 1

The documentation for sendStringParametersAsUnicode states:

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.

So if including it in your query is making your performance worse, that suggests it's not appropriate for your query, and you're seeing exactly the problems the documentation warns about.

If you are seeing a performance boost in the MS driver, it's possible that sendStringParametersAsUnicode has a subtly different meaning on JTDS than on the MS driver.

What's the performance of each driver like with and without the option? What version of SQL Server are you using? What does your query profiler show for these queries? What's your query and what are the types of the fields involved?