Foliotek Developer Blog

SQL Server Query Optimization - CPU Impact of Datatype Mismatching

I’ve been identifying “expensive” queries in our database lately and came across one that was particularly interesting. One query showed 1400 CPU, which is certainly high. Why in the world would a query require that much processing. It was a pretty simple query as well:

SELECT [t0].[ItemID], [t0].[UserID], [t0].[ItemTypeID], etc.  
 FROM [dbo].[Items] AS [t0]  
 WHERE (CONVERT(NVarChar, [t0].[ItemID])) IN (@p0, @p1, @p2, @p3)  
 ORDER BY [t0].[Name]  

This just takes a list of Item ID’s and returns the Items. The “Order By” clause doesn’t take much. Notice the CONVERT statement in the WHERE clause. The CONVERT statement does not convert the input data to that of the database column, but rather converts the datatype of the database column to match the datatype of the input variables.

The problem was a datatype mismatch. Item ID is an integer, but, through carelessness, I had passed in a list of strings (in, C#). This query (generated by Linq) appears to have converted every Item ID in the index to an nvarchar–all to retrieve, in this case, four rows from the Item table. This was a very small thing to correct–simply convert the list of strings to a list of integers in C# prior to using that in a Linq query. Failing to match types in the database had huge consequences. A little carelessness resulted in a very substantial CPU load.

After the simple correction, this query didn’t even show up in the SQL Profiler results, even when I reduced the threshold substantially. So, 1400+ CPU to 0 CPU.

For more, information on how I identified this query, see A Beginner’s Guide to SQL Server Query Optimization.