CONVERT_IMPLICIT found in Execution Plan (SQL SERVER 2008) Being used by an index

Hello All,

I am working on reading execution plans for several stored procedures in a database. While looking at one, I see that the first item on the rightmost section of the execution plan has 80% of the load of the execution plan. The Properties of this node indicate the following:

CONVERT_IMPLICIT(VARCHAR(12),[Database_Name].[dbo].[Table_Name].[Column_Name]as[C.ColumnName], 0) LIKE @InputParameter

This is then followed by an output list. The Input Parameter is of  VARCHAR data type, and the column it is comparing to is of is part of a composite key and is of data type INT. I think the difference in data types between these 2 may be causing this CONVERT_IMPLICIT line but I am unsure. What is the cause of this line, and what can I do to remedy it  so that the index that is being used can be used properly and use an index seek instead of an index scan which is what is currently happening?
LVL 1
DB-ahaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cheers4beersCommented:
Check out this blog post on MSDN, it explains what is going on with the implicit conversion, why it is not a good idea, and ways to fix it:

http://blogs.msdn.com/b/craigfr/archive/2008/06/05/implicit-conversions.aspx
0
cheers4beersCommented:
Here is another, more recent post on implicit conversions from the same author:

http://blogs.msdn.com/b/craigfr/archive/2010/01/20/more-on-implicit-conversions.aspx
0
Anthony PerkinsCommented:
>>What is the cause of this line, and what can I do to remedy it  so that the index that is being used can be used properly and use an index seek instead of an index scan which is what is currently happening? <<
Please post an example of the specific query that is generating this execution plan.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DB-ahaAuthor Commented:
The code is quite long, but singling out the one item that caused the initial Convert_Implicit down to the specific table and colum, actually generates the same node entry in the execution plan and this query took nearly a minute to return all results:

DECLARE
      @ipPVersion                  VARCHAR(200)
SET      @ipPVersion                  ='%'

SELECT
      C.PVersion
FROM QnPHistory C WITH(NOLOCK)
WHERE
      ISNULL(C.Version, '') LIKE @ipVersion
0
LowfatspreadCommented:
with a like clause a scan is the best you can hope for....

if c.version is part of a primary key ... it can never be null so the ISNULL should be removed...

(is that where the convert to character arises from....)

what is the datatype of c.version?

why do a like search on an integer... what patterns will be used...?

0
jogosCommented:
And what datatype has Version in the QnPHistory table?

0
DB-ahaAuthor Commented:
if c.version is part of a primary key ...
C.Version is part of a composite key and does not accept nulls.

what is the datatype of c.version?
In the table, the Version is an INT in the history table.

if c.version is part of a primary key ... it can never be null so the ISNULL should be removed...

What benefit is served by removing the ISNULL from the beginning of the comparison in the WHERE?

The LIKE is used on an integer because a user may not know the full value. Lets say that they know that the value begins with a 7 but don't have the rest, they can just search by 7 and get all the results and look manually for the one they are looking for. It needs to allow for that kind of flexibility.

My question as of now is, can I force a conversion on the input? I tried to change the VARCHAR(200) in the code that declares an input and data type but that didn't work and won't allow a search on '%'


0
LowfatspreadCommented:
functions inhibit the selection and use of indexes ... they make the underlying columns non sargable (in general)

so since ISNULL  is not required remove it....

to remove the implicit conversion then ...

you need to add a persisted computed column to your table which does the conversion from integer to character and index that....











0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DB-ahaAuthor Commented:
That sounds like a good suggestion but what would be the alternative in a situation where a schema change is not allowed by the gods above? Is there any way to do this dynamically when the sp is executed?
0
LowfatspreadCommented:
how complex can the search string get?

e.g. just  71%

or would   7[1-3]_4%¬[4-6]%        also  be valid?


if its simplex like the first example 71%  then maybe you could try generating the search ranges...

version = 71 or version between 710 and 719
or version between 7100 and 7199
or version between 71000 and 71999
or version between 710000 and 719999
...

but i suspect that a scan may be the identified "best" option still ...

even the computed column  index will tend towards scan rather than seek usage ...


 
0
LowfatspreadCommented:
is it a "true" version or a composite of major version, minor version, patch number where each portion is expected to be only 2-3 digits in length...

0
DB-ahaAuthor Commented:
Thanks for your input. The persisted calculated column being added makes the most sense and keeps it simple.  I have yet to implement this idea but rather than leave this abandoned until resolved, I am marking this as the solution for simplicity in my particular case.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.