Problems with the JTDS driver to accept sendStringParametersAsUnicode = false?

Question

Used net.sourceforge.jtds.jdbc.Driver as my driver from MSSQL for all my applications. I had performance issues in the prepared statement, and I found out that sendStringParametersAsUnicode = false should fix this problem. Unfortunately, I cannot force the driver to accept the value. I can get the Microsoft com.microsoft.sqlserver.jdbc.SQLServerDriver driver to accept the parameter just fine:

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

works in persistence.xml and in my ds.xml. Prepared applications go fast, 100 in 22 seconds.

However, I cannot get the same performance boost from JTDS. It still hangs around the prepared statement, taking a few seconds at each iteration.

I tried several line options and saw 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 

All that I read suggests that the Microsoft driver is slower, and my company had problems with it in the past. I would really like to use JTDS, if possible, but I can’t wait for the prepared statement within 10 seconds!

Anyone got any ideas?

thanks

Answer 1

The documentation for sendStringParametersAsUnicode states:

Determines whether string parameters are sent to the SQL Server database in Unicode or to the default encoding of the default database. This seriously affects SQL Server 2000 performance because it does not automatically use types (as 7.0 does), which means that if the index column is Unicode and the row is sent using the default encoding (or vice versa), SQLServer will perform an index scan instead of index search. For Sybase, determines whether strings that cannot be encoded in server encoding will be sent as unicode strings. There is a performance hit for coding logic, so set this parameter to false if unitext or univarchar data types are not used or if charset is utf-8.

So, if including this request in your request degrades your performance, it means that it is not suitable for your request, and you definitely see the problems that the documentation warns about.

If you see performance improvements in the MS driver, it is possible that sendStringParametersAsUnicode has a subtly different value in JTDS than in the MS driver.

What is the performance of each driver, with or without an option? What version of SQL Server are you using? What profiles your query for these queries? What is your query and what are the types of fields used?