Performance issues with JDBC drivers’ for MS SQL Server

When writing JDBC clients for Microsoft SQL Server, we need to be fully aware of the default string conversions made by the JDBC drivers which can drastically hit the performance of the application/client.

SQL Server differentiates its data types that support Unicode from the ones that just support ASCII. For example, the character data types that support Unicode are nchar, nvarchar, longnvarchar where as their ASCII counter parts are char, varchar and longvarchar respectively. By default, all Microsoft’s JDBC drivers send the strings in Unicode format to the SQL Server, irrespective of whether the datatype of the corresponding column defined in the SQL Server supports Unicode or not. In the case where the data types of the columns support Unicode, everything is smooth. But, in cases where the data types of the columns do not support Unicode, serious performance issues arise especially during data fetches. SQL Server tries to convert non-unicode datatypes in the table to unicode datatypes before doing the comparison. Moreover, if an index exists on the non-unicode column, it will be ignored. This would ultimately lead to a whole table scan during data fetch, thereby slowing down the search queries drastically.

MS SQL JDBC drivers specify a configuration property called sendStringParametersAsUnicode which helps in getting rid of this unicode conversion. This property defaults to 'true’ which makes the driver send every string in Unicode format to the database by default. If the database tables use only the ASCII data types, we MUST disable this property to ensure the following:

1) All parameters of the JDBC statements are sent to the database in ASCII format thereby getting rid of Unicode conversion overhead.

2) All indexes are actively used during data fetches thereby getting rid of whole table scans.

To disable this property, just append the property with its value as 'false' to the jdbc connection URL as shown below:

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

This performance degradation is quite possible with other driver’s for Microsoft SQL Server also as the default behaviour in the well-known drivers for MS SQL Server is to send strings as Unicode by default. However, all these drivers have a similar property to tweak the default behaviour. Here is a list of these of properties by vendor:

Vendor	Parameter
JSQLConnect	asciiStringParameters
JTDS	sendStringParametersAsUnicode
DataDirectConnect	sendStringParametersAsUnicode
Microsoft JDBC	sendStringParametersAsUnicode
WebLogic Type 4 JDBC SQL Server driver	sendStringParametersAsUnicode