Implicit Data Conversion: The “Hidden” Bottleneck

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 seconds to come back.

Well, there can be a number of causes. Today, we are going to discuss “Implicit Conversion.” I ran into this again today helping a colleague out. I have seen this literally bring a system to its knees by causing deadlocks 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 demon 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 and write a procedure with the wrong data type all the time in that scenario. 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 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.