CONVERT_IMPLICIT SQL Performance Tuning

     SP Running Slow - CONVERT_IMPLICIT intrudes in SQL Execution Plan. Today I was working on a Stored Procedure where I found that everything was written fine but the query was taking 5-8 mins to exeute on the Production Server. I looked into the execution plan of the query and noticed that the node of table EmployeeMaster was showing "Index Scan" though the Primary Key was created on the search key and had a select number of rows, so it should have shown "Index Seek", below is the execution plan.

 And on hovering over the node it was found that in tooltip there was a CONVERT_IMPLICIT function under the Predicate block ...

 Predicate :




[RSBI].[dbo].[EmployeeMaster].[UserID] as 
[BP].[UserID]=isnull([@InUserID]
,CONVERT_IMPLICIT(varchar(12),[RSECBI].[dbo].
[EmployeeMaster].[UserID] as [BP].[UserID],0))




Object :




[RSBI].[dbo].[EmployeeMaster].
[NCLIX_EmployeeMaster_UserID_ParentCode] [BP]




Output List :




[RSBI].[dbo].[EmployeeMaster].UserID|
[RSBI].[dbo].[EmployeeMaster].DesignationCode

     Analysing CONVERT_IMPLICIT I found that the parameter passed @InUserID was of VARCHAR(12), while in table UserID field was of VARCHAR(20).

    So I changed the parameter datatype as VARCHAR(20), and the issue was gone..now the SP running within a fraction to second..

    The same issue could be with other datatypes as well, when your Input parameter is of lower precedence, then it will convert to higher precedence field in the table.

    This is because, When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.



Reference: Govind Badkur(http://sqlserver20.blogspot.com)