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.