MSSQL, jTDS, NVARCHAR and Slow Indexes

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]

Mr. Slow
Mr. Slow by Vox

An application I’ve built is going into production soon. It’s the first application I’ve been involved with which will be using MSSQL server in production, I have some learning about MSSQL to do. After some research, I ended up using the jDTS JDBC driver instead of the Microsoft JDBC driver (which is feature incomplete and has some serious open issues).

We recently began performance testing and saw some odd behavior. Initially the application was performing well. However after few runs of the stress test the performance went from good to awful. The main web service call went from 600 ms to 23,000 ms. The database server’s CPU was pinned, and the app servers were barely loading, spending all their time waiting for the database server to return queries. Stranger still, my local instance (running against PostgreSQL) performed well with the same code and same stress tests. Luckily a smart MSSQL DBA was able to figure out why the database was burning so much CPU and responding so slowly.

One of the primary queries is against a table which has been growing. The select query is simple and had an indexed column in the WHERE clause. Even as the table grew to over a million rows, it should have been a very quick query. Unfortunately it was taking several seconds to complete. My local instance had over 30 million rows in the same table in PostgreSQL and the query was lightening fast. The DBA discovered that the query execution was converting the indexed varchar column into nvarchar values for comparison with the query parameter used in the WHERE clause which was inexplicably coming over as an nvarchar. This datatype mismatch between the column definition and the query parameter meant that MSSQL was doing a scan of the million+ record index instead of the almost instant seek it should have been doing.

It turns out that jTDS sends String parameters over as Unicode (nvarchar) by default. It’s easily fixed by adding this property to your connection-url:

[/fusion_builder_column][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][plain]
sendStringParametersAsUnicode=false
[/plain]

That immediately fixed the performance issues.

So, if you’re using jTDS and are using indexed varchar columns in your queries, you should add the property above to avoid your indexes being wasted and your queries running slowly.[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]


Posted

in

,

by

Comments

5 responses to “MSSQL, jTDS, NVARCHAR and Slow Indexes”

  1. John Shin Avatar

    Can you elaborate on the SQLServer configuration (Version of DB, version of OS and 32/64bit)?

    1. Devon Avatar

      John,

      I think it was MSSQL 2005 on Windows Server 2003, 32 bit I think. However I believe the issue is not version specific, it’s simply a matter of how jTDS handles string params.

      Devon

  2. Andy Avatar

    Very cool article; we actually experienced the same issue with a production application using MSSQL server and _the Microsoft JDBC_ driver.

    So, it seems like the issue is not specific to the jTDS driver.

    1. Devon Avatar

      Andy,

      good to know! Glad you solved it, as it can a bear to figure out:)

      Devon

  3. Jacob Avatar
    Jacob

    yes, another solution is just to change the datatype to nvarchar in the database.

Leave a Reply to Devon Cancel reply

Your email address will not be published. Required fields are marked *

PHP Code Snippets Powered By : XYZScripts.com