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

Aromaticity

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?

Mezereum

Good questions Jon. MSSQL 2008. Both drivers delay approximately 9 seconds at the prepared statement without the setting (set to true by default) but the MS driver improves to less than 1 second, while the JTDS driver does not appear to change in performance. – Aromaticity

Also, the query runs in under 1 second in the profiler (measures at 0.00 seconds) – Aromaticity

@javatestcase: Can you look at the logs on the server to determine how the statement was sent? I would expect that to be available somewhere in the details. – Mezereum

Jon, from a stack trace perspective, yes. I actually had another question about this topic, when I discovered it worked in the MS driver, the stack is there, but I all I learned from it really was that the query is passing all the way through to a socket: #8949372 – Aromaticity

@javatestcase: Sorry, I meant the logs on the database itself. – Mezereum

That makes sense. Would I be able to determine the charset used from the logs? I'll check into that. – Aromaticity

@javatestcase: I'd expect so, yes. I'd expect basically everything to be there :) – Mezereum