One example of query performance problem due to data type conversion

In many RDBMS systems, if the value(s) passed to a query for filtering/matching is of different data type than the column data type it is comparing against, an implicit data conversion occurs. This conversion can render the index defined on said column(s) less useful or entirely useless. I encountered one such problem on SQL Server today.

Here is the relevant “WHERE CLAUSE”:

WHERE DomainNTLogin = SUSER_NAME()

Column DomainNTLogin is defined as varchar(100), but SQL Server’s SUSER_NAME() returns nvarchar(128). Note the letter “n” in nvarchar: the difference is subtle but significant. nvarchar is used for languages such as Arabic, Hebrew, Chinese, Japanese, and others. Because of the data type difference, the index defined on DomainNTLogin is not used, one would see a CONVERSION_IMPLICIT in the query’s execution plan:

CONVERT_IMPLICIT(nvarchar(100),[DbName].[SchemaName].[TableName].[ColumnName],0)=SUSER_NAME()

What that tells us is that the query engine needs to get all values in ColumnName, which is a scan operation, convert that to nvarchar, before the comparison can be made. Ideally, we’d like to see a seek, instead of a scan!

Fortunately, this problem is easy to fix. Instead of letting the query engine doing the implicit conversion, we do the conversion for it:

WHERE DomainNTLogin = CAST(SUSER_NAME() AS VARCHAR(100))

This change turns a table scan into an index seek, decreasing this query’s execution time from around 16 minutes to less than 1 second!

Years ago, I seem to remember that a JDBC driver for SQL Server from a certain vendor turns all varchar values to nvarchar by default, and I believe/hope there is a setting to turn that off. Inform me, dear reader, if this is the case and you know how to adjust that setting, as needed.

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.