Have you ever written a query that just seems to take forever? You have looked it over time and time again, and it seems to be perfect! It’s straightforward, has an appropriate index, and still takes 5, 10, 15 seconds… or even minutes?.

A common cause in this scenario is “Implicit Data Type Conversion.” I ran into this again today helping a colleague out. I have seen this literally bring a system to its knees by causing a deadlock-prone state during high load. In short, we can blame this for the fall of the Roman Empire.

Consider the following stored procedure:

Create Procedure WidgetInfoGet @WidgetID VarChar(20)
As
Select WidgetID, Descr, CurrentPrice, Qty
From WidgetMaster
Where WidgetID = @WidgetID

That looks innocent enough, right? However, a statement like this has the potential to make demons and children cry if that table has several hundred thousand rows in it.

Here’s the deal. Have you ever seen a system where in some tables an ID is stored as a Character string, but in others it’s stored as an Integer? Sure, we all have. Developers and even DBAs get confused or distracted and write a procedure with the wrong data type. Say for example that our table actually has WidgetID definted as an Int, but the developer was confused and wrote the procedure as if the WidgetID column was a VarChar(20).

Now, every time SQL Server has to look for a Widget, it has to convert @WidgetID from a VarChar to an Int. This is an implicit conversion of data type. Internally, SQL Server uses a convert function to do this. When this happens, SQL Server cannot use an index effectively because any (well, any Non-deterministic) time it has to hand something to a function, it cannot be certain of the result of the function. Therefore, it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.

The fix? Simple! Strangle the person who started mixing data types in the system. Once they are dead, alter your procedure to convert the value first:

Create Procedure WidgetInfoGet @WidgetID VarChar(20)
As
Declare @intWidgetID Int
Set @intWidgetID = Convert (Int, @WidgetID)

Select WidgetID, Descr, CurrentPrice, Qty
From WidgetMaster
Where WidgetID = @intWidgetID

…and you will now retire a millionaire because you single-handedly rescued the world.

Quick Note #1: The purest in me also wants to mention that rewriting the header of the procedure to accept an Int rather than a VarChar is better form, but that option has the notable side-effect of causing more work by development staff to change the calling code.

Quick Note #2: This posting is part of a series on performance tuning. Click here to check out the rest.