Fixing slow queries running on SQL server using JPA (Hibernate) and jTDS

Submitted by Jochus on Thu, 01/05/2014 - 07:40 | Posted in: Java
Java



Some JPA queries, especially the ones with a LIKE operator, can run very slow on a SQL server using jTDS.

The issue is in the way the jTDS drivers send the parameter strings to the SQL server. Apparently Java will attempt to send the parameters Unicode by default, and SQL server will translate it to ASCII. It can take some seconds because it changes each column value to Unicode before comparing against your parameter. This means it can't take full advantage of any index on the field leading to much slower performance.

If you add the following parameter sendStringParametersAsUnicode=false to your connection string in the datasource, this conversion step can be skipped and it can speed up the performance of your queries:

jdbc:sqlserver://localhost\SQLEXPRESS;DatabaseName=TESTDB;sendStringParametersAsUnicode=false

Add new comment

The content of this field is kept private and will not be shown publicly.
About text formats

Full HTML

  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.