Post reply

index:predicate:converrt_implicit? after changing code performance improved

  • March 12, 2011 at 8:51 am

    #389035

    we had index seek on 12 million records table. The cost was 14 on this table. Mentioned below is the predicate:

    CONVERT_IMPLICIT(int,[MyDB].[dbo].[RTE].[RKey] as [r1].[RKey],0)=[@RId]

    This was joining on a column like this:

    r1.RKey = r.Id

    but after changing to

    r1.RKey = convert(nvarchar(20),r.Id)

    i see the same predicate but cost came down to 1 and query started running faster. I read this type of conversion helps but not sure why exactly it helped. Any thoughts?

  • March 12, 2011 at 10:31 am

    #1298015

    One should always be reluctant when you find an Implicit conversion.

    In this case, the engine determined to convert your nvarchar column to integer.

    It's called "Data Type Precedence" and documented at http://msdn.microsoft.com/en-us/library/ms190309%28v=SQL.90%29.aspx

    Although it doesn't seem to match the document.

    You should compare the plans to see how it uses the indexes.

    If needed, it's best to handle conversions explicit, so everybody that sees your query knows it is expected behaviour.

    Because of the conversion, existing indexes will not be used in an optimal way or may even not be used at all.

    Because you handled the conversion yourself with the modification, it was able to use the index more optimal.

    It is best to avoid the need for conversions.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 12, 2011 at 9:31 pm

    #1298069

    makes sense..thanks

  • March 12, 2011 at 10:01 pm

    #1298071

    sqldba_icon (3/12/2011)

    we had index seek on 12 million records table. The cost was 14 on this table. Mentioned below is the predicate:

    CONVERT_IMPLICIT(int,[MyDB].[dbo].[RTE].[RKey] as [r1].[RKey],0)=[@RId]

    This was joining on a column like this:

    r1.RKey = r.Id

    but after changing to

    r1.RKey = convert(nvarchar(20),r.Id)

    i see the same predicate but cost came down to 1 and query started running faster. I read this type of conversion helps but not sure why exactly it helped. Any thoughts?

    In addition to what Johan posted above, looking at both the implicit conversion and the explict conversions you posted, I'd hazard the guess that the r1 table has quite a few more rows than the r table. If so, the math is simple. It's cheaper to do an explicit conversion on a smaller table (r) than it is to do an implicit conversion on a larger table (r1). Since the implicit conversion on the larger table may also, as Johan pointed out, keep and index from being used on the larger table, the problem gets even worse.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 14, 2011 at 1:10 pm

    #1298540

    Jeff you are absolutely correct."r" has about 52K records and "r2" 16 million:).

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply