More on Implicit Conversion issues
Posted by decipherinfosys on December 22, 2008
In one of our blog posts, we had covered how the implicit conversion creates an issue in the CASE statement. In that case, one actually catches it before it becomes an issue since it will give an error when you try to run it. Another issue that can arise is in scenarios like the one shown below:
/*Create a Dummy Table with 2 columns and create an index on the second column*/
create table dbo.testcase (col1 int identity, colx varchar(10))
create index testcase_ind_1 on testcase (colx)
/*Create a Million records*/
declare @i int
set @i = 1
while (@i <= 1000000)
insert into dbo.testcase (colx) values (‘ABC’ + cast(@i as varchar(7)))
set @i = @i + 1
Now, check the execution plan of this code:
declare @par1 nvarchar(10)
set @par1 = N’ABC100000′
select * from dbo.testcase where colx = @par1
Note that the parameter has been declared as Nvarchar(10) instead of varchar(10) as it is in the table. Now, that value is unique so we are really selecting 1 single record out of 1 million and we even have an index on that filter column so it should jump right at it and use the index. However, it will have to do an implicit conversion in order to convert the varchar column into nvarchar and then do a comparison. When a function gets applied on the indexed column, the index will not get used as we have demonstrated before in our posts (unless you have a computed column which uses that function and then you have an index on that computed column).
|–Table Scan(OBJECT:([DECIPHERTEST].[dbo].[testcase]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[DECIPHERTEST].[dbo].[testcase].[colx],0)=[@par1]))
If we were to declare the parameter as varchar(10), then this is what the execution plan would look like:
|–Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|–Index Seek(OBJECT:([DECIPHERTEST].[dbo].[testcase].[testcase_ind_1]), SEEK:([DECIPHERTEST].[dbo].[testcase].[colx]=[@par1]) ORDERED FORWARD)
|–RID Lookup(OBJECT:([DECIPHERTEST].[dbo].[testcase]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
with the seek operation done correctly. This kind of an issue would actually be a bug in the application but we did see this happening in one of the vendor applications at one of our client sites when a patch that was delivered was using unicode parameters against a non-unicode data type schema and all performance went down the drain after the patch install. A simple flag option was set wrongly in the configuration file of their install (application configuration file) which caused this so it was fixed easily but we wanted to post it out so that you are aware of this as well in case you ever face this.
In addition, other types of such implicit conversions can cause some serious performance issues so make sure that when doing joins or when you are applying filter conditions, implicit conversions do not take place.