Implicit Data Type Conversion

Hello all,

Yesterday by accident I ran into a query running slow. In this case the query is generated by Nhibernate. After looking at the query and execution plan I figured out that it was the implicit data type conversion.

The data in the table is stored in column of varchar data type. The column is a primary key with clustered index. However the query is issued against the table with a parameter passed in and it's declared as NVARCHAR. Do you see where this is going? So when the query is executed with nvarchar parameter sql server does an index scan on the clustered key and converts each column data to nvarchar and then compares the result against the parameter passed in. As we all know clustered index scan is equal to table scan.

I changed the query and declared the parameter as varchar and ran it. It worked as expected. There was no need to do a data type conversion so SQL Server used Clustered Index Seek operator and completed the operation 4 times faster than the slow query. In my case the table I am talking has 199K rows. Imagine how much slower it could be if it had millions of rows.

I talked to principal architect and the solution was to update Nhibernate mappings. He said that Nhibernate by default maps to nvarchar data type and if you're not using nvarchar for unicode characters in your table you definitely want to map Nhibernate to your tables properly using correct data type.

HTH,

MileHighDBA