While diagnosing random poor performance on live servers recently, we discovered that the issue lay with some inefficient query performance in sql server. Upon examining the queries we discovered the issue was due to search parameters being sent as nvarchar (unicode) while the lookup column value was a varchar (ansi). Its a subtle difference with some major impacts.
To do a type comparison, sql server must convert the data types to the same type. In this case, it converts the varchar to an nvarchar (widening) before comparison. When you think about it this has to be the case. When sql server compares the column value to the search parameter it needs to do a bit for bit comparison. If it was to simply narrow the parameter, bits might be lost and the value would be altered rendering the query invalid. This being the case, it has no option but to widen the lookup value of every row in the table. Not only does this cause high cpu usage but it also causes a large number of reads consuming more memory in the process. Not a good state of affairs but at least the fix is easy enough, we need to make the parameter value a varchar instead of an nvarchar. This allows the server to use a seek with no type conversion and a small number of reads giving a drop in resource usage and a signifigant performance gain.
Given we are using nHibernate, it was time to examine some type mappings to see what was causing this behaviour. What I found was this …
Xml: <property Name="myPropName" Column="myLookupColunm" Type="String" />
Fluent: Map(x => x.myPropName);
All looks well so far … or does it? A search for c sharp to sql type mappings for nHibernate reveals that string will map to an nvarchar. There is another type mapping that will instruct nHibernate to use varchar …
Xml: <property Name="myPropName" Column="myLookupColunm" Type="AnsiString" />
Fluent: Map(x => x.myPropName).CustomType("AnsiString");
It seems so simple … and it is, when you know what the problem is. The issue here is that sql server is a little too tolerant of our oversights. It does such a good job of just converting the types and getting on with it that you only notice the issue under some hefty load on large tables if you’re running on production grade metal. Once you’re at this stage, that code was likely written long ago and long forgotten and diagnosing the problem can take some time.
It should be noted that this applies to any and all datatype mismatches, not just strings. If the parameter data type is wider than the column data type, this issue will occur. Remember the bit for bit comparison thing … all data types must be equal!