Post reply

Why is there an implicit conversion between two varchars

  • September 26, 2012 at 2:13 pm

    #145705

    I have some SQL (SQL 2008) that I inherited and am trying to find out why some of queries are running really slow. In the Actual Execution Plan I have three clustered index scans which are costing me 19%, 21% and 26%, so this seems to be the source of my problem.

    The contents of the fields (job numbers) are usually numeric (but some job numbers have an alpha prefix)

    The database design (vendor supplied) is pretty poor. The max length of a job number in their application is 12 chars, but in the tables that are joined it is defined as varchar(50) in some places and varchar(15) in others. My parameter is a varchar(12), but I get same thing if I change it to a varchar(50)

    The node contains this:

    Predicate: [Live_Costing].[dbo].[TSTrans].[JobNo] as [sts1].[JobNo]=CONVERT_IMPLICIT(varchar(50),[@JobNo],0)

    sts1 is detrived table, but the table it pulls jobno from is a varchar(50)

    I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?

    I'm fairly new to the execution plan, so I have a few more questions:

    Is there an easy way to figure out which node in the exc plan relates to which part of the query?

    Is the predicate, the join clause?

    Regards

    Mark

  • September 26, 2012 at 2:58 pm

    #1542623

    mark 4643 (9/26/2012)

    I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?

    Yep.

    Is there an easy way to figure out which node in the exc plan relates to which part of the query?

    Is the predicate, the join clause?

    Not... exactly.

    If you take a look at the second link in my signature for indexes and tuning, it'll walk you through what we'll need to help you decipher what the problems are with your schema and queries. It gets a bit involved.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 26, 2012 at 3:00 pm

    #1542624

    Evil Kraig F (9/26/2012)

    mark 4643 (9/26/2012)

    I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?

    Yep.

    Is there an easy way to figure out which node in the exc plan relates to which part of the query?

    Is the predicate, the join clause?

    Not... exactly.

    If you take a look at the second link in my signature for indexes and tuning, it'll walk you through what we'll need to help you decipher what the problems are with your schema and queries. It gets a bit involved.

    Thansk Craig

  • September 26, 2012 at 3:08 pm

    #1542625

    Attached is the exec plan and stats

  • September 28, 2012 at 2:00 pm

    #1543620

    Apologies Mark, I've been heavily tied up the last few days and didn't have time to really dig into the information and virus-scan the .zip. I'd hoped someone else would have had time to swing through. Am I correct in that you'd still like assistance with this?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 28, 2012 at 8:01 pm

    #1543657

    This is a pretty large "all in one" query that needs to be divided up. If you look for the large arrow in the bottom right section of the execution plan, you find it contains a row count of more than 17 million rows which is quite a bit larger than any of the tables involved. That means that you have a many-to-many join going on similar to a small cross join.

    One you find the hash join that arrow is coming out of, look at the "residual probe" in the properties window for the hash join. It'll let you know what tables are involved in the join which will help you isolate the code.

    My recommendation is to determine what the core of this query actually should be and to quickly isolate that much smaller result set in a Temp Table and then join to that instead of trying to do this query all at once. "Divide'n'Conquer".

    --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)

  • October 2, 2012 at 5:42 pm

    #1544636

    Evil Kraig F (9/28/2012)

    Apologies Mark, I've been heavily tied up the last few days and didn't have time to really dig into the information and virus-scan the .zip. I'd hoped someone else would have had time to swing through. Am I correct in that you'd still like assistance with this?

    Hi Craig

    Yes, still struggling with it

  • October 2, 2012 at 5:43 pm

    #1544637

    Jeff Moden (9/28/2012)

    This is a pretty large "all in one" query that needs to be divided up. If you look for the large arrow in the bottom right section of the execution plan, you find it contains a row count of more than 17 million rows which is quite a bit larger than any of the tables involved. That means that you have a many-to-many join going on similar to a small cross join.

    One you find the hash join that arrow is coming out of, look at the "residual probe" in the properties window for the hash join. It'll let you know what tables are involved in the join which will help you isolate the code.

    My recommendation is to determine what the core of this query actually should be and to quickly isolate that much smaller result set in a Temp Table and then join to that instead of trying to do this query all at once. "Divide'n'Conquer".

    Thanks Jeff

    Will take a look tomorrow

    Regards

    mark

Viewing 8 posts - 1 through 7 (of 7 total)

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