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 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:
2) All indexes are actively used during data fetches thereby getting rid of whole table scans.
|WebLogic Type 4 JDBC SQL Server driver||sendStringParametersAsUnicode|