Well known Query performance Killer..... "IMPLICIT CONVERSION "

Last month I encountered an interesting performance issue and after little bit of investigation I got to know that it was due to implicit conversions.I had solved that problem because I was allowed to change the TSQL code but sometimes you will be in a situation where you can't touch the TSQL code. What you can do to solve the same problem in that situation, I just wanted to share it with you guys in this post.

Basically, implicit conversions occur whenever data with two different data types are being compared, based on the data type precedence.  The precedence establishes the hierarchy of of the types, and lower precedence data types will always be implicitly converted up to the higher precedence type. These conversions increase CPU usage for the operation, and when the conversion occurs on a table column can also result in an index scan where an index seek would have been possible without the implicit conversion.

Let me create a Sample database. Then will see the problem.

USE master;
IF DB_ID('Demo') IS NULL
BEGIN
CREATE DATABASE Demo 
END
GO

USE Demo
Go

--------------------------------------------------------------------------------------------------------------
-- Create a Numbers table which will help us to populate data----
--------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.Numbers') IS NOT NULL DROP TABLE dbo.Numbers;
CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);

DECLARE @Maximum AS INT, @counter AS INT;
SET @Maximum = 10000000;
SET @counter = 1;

INSERT INTO Numbers VALUES(1);
WHILE @counter * 2 <= @Maximum
BEGIN
INSERT INTO dbo.Numbers SELECT n + @counter FROM dbo.Numbers;
SET @counter = @counter * 2;
END

INSERT INTO dbo.Numbers
SELECT n + @counter FROM dbo.Numbers WHERE n + @counter <= @Maximum;

------------------------------------------------------------------------------------------------
-- Create table Perfomance
------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.Performance') IS NOT NULL DROP TABLE dbo.Performance;
CREATE TABle Performance
(
ID          INT identity(1,1) not null,
APPID                  varchar(50) null,
CREATEDON datetime
);

------------------------------------------------------------------------------------------------
--Populate the tables with numbers table.
------------------------------------------------------------------------------------------------
INSERT INTO Performance(APPID,CREATEDON)
SELECT 'APPID ' + CAST(n AS VARCHAR(10)), GETDATE() FROM dbo.Numbers WHERE n <= 100000

------------------------------------------------------------------------------------------------
--CREATE INDEXES ON COLUMNS
-----------------------------------------------------------------------------------------------

CREATE CLUSTERED INDEX INDEX_CL_ID on Performance(ID);
CREATE NONCLUSTERED INDEX INDEX_NCL_APPID on Performance(APPID);

--------------------------------------------------------------------------------------------
-- RUN THE BELOW QUERY AND SEE THE EXECUTION PLAN
---------------------------------------------------------------------------------------------
DECLARE @P NVARCHAR(60)
SET @P = N'APPID 10'
SELECT P.APPID FROM PERFORMANCE P WHERE P.APPID =  @P  

IN MY CASE I JUST APPLIED CAST TO PARAMETER AND THE PROBLEM WAS SOLVED.

What if you can not change the TSQL code. Also you can not change the datatype of table. What we need here is a way to change the data type of the column and index, without touching the existing T-SQL code or affecting the current schema configuration. 

To do so these requirements can be achieved via Indexed Computed Column.

-----------------------------------------------------------------------------------------------------

--Now we’ll create a computed column APPID_COMP and will add a non-clustered index to same.

-----------------------------------------------------------------------------------------------------

-- Create computed column using the preferred data type

ALTER TABLE performance ADD APPID_COMP AS (convert(nvarchar(50),APPID));

--Create a non-clustered index on the computed column

CREATE NONCLUSTERED INDEX Index_ncl_APPID_COMP on performance(APPID_COMP);

----------------------------------------------------------------------------------------------------------------------------------------------------------

--Now Run the below Query and see the execution Plan.

----------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @P NVARCHAR(60)

SET @P = N'APPID 10'

SELECT

P.APPID

FROM PERFORMANCE P

WHERE P.APPID = @P

The execution Plan for same is as follows:

We can see that now SQL Server is able to compare the records without implicit conversion. Applying this change means now SQL Server is able to take benefits of new Index, that is the suitable data type for the query, without modifying the query. 

Thanks For Reading this Blog!!!