Sending right string type from JDBC to Sql Server

In Java, the char data type is 16-bit, so it can handle Unicode characters.

In a mixed environment where Windows and *nix (Unix, Linux, OpenBSD, Solaris, etc.) co-exist, when an app server needs to talk to Sql Server, it is pretty common that the app server is written in Java, and the connection is done via JDBC. The prevalent JDBC drivers seem to come from either BEA’s Weblogic or Microsoft’s own JDBC driver.

In both cases, by default, the driver will send string data as Unicode to Sql Server. Depending on your environment and data volume, this can have serious performance consequences. This is especially true in the North American market, because a lot of data in North America in Sql Server is stored as varchar or char.

The problem with data type inconsistency is two-fold: firstly the engine has to do a cast/convert operation, which is unnecessary in this case; secondly, probably more importantly, it will throw off query optimizer, in that a potential index seek becomes index scan, because data type have to be converted for comparison.

To verify this problem, you can use Profiler to capture some results, and you will find snippet like below in the TextData column:

exec sp_prepexec @P1 output, N’@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000)

Note that the parameter is nvarchar, the Unicode data type. The difference is subtle, but important.

To change this, you will need to reconfigure JDBC so all code will be sent in as ASCII. In both drivers mentioned above, there is a setting called SendStringParametersAsUnicode. You will need to set it to false.

When that is done, to confirm the change is in effect, run Profile again, and you will see things similar below in the TextData column:

exec sp_prepexec @P1 output, N’@P0 varchar(8000),@P1 varchar(8000),@P2 varchar(8000)

Depending on the volume of your operation, you could see significant performance boost because of this simple configuration change.

Of course, if your character data in Sql Server is stored as Unicode characters, then this should not be a concern.

In addition, I am interested in your experience setting up JDBC connection pool to Sql Server. You could sharing your experience setting that up by commenting below. Thanks.

2 thoughts on “Sending right string type from JDBC to Sql Server”

  1. Thanks for the explanation. I am having trouble with unicode data in SQL Server 2005.

    We are using SAP XI and connecting it to SQL Server 2005 using JDBC. Unicode data is received into SQL server as “????”.

    The destination columns are nvarchar and store unicode data correctly.
    External updates from Excel/text files are being processed correctly.

    Additionally. the information is stored and dispalyed in SAP XI correctly.

    Can you let me know what other settings are to be checked.

    Regards,
    Sriram.

  2. Sorry for the late reply, Sriram. My comment notification is not working at the moment.

    No, I don’t know any other settings to check at the moment.

    Good luck. And share it here if you find something useful.

    Thanks!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.