Post reply

Explain Plain

  • March 20, 2012 at 10:18 am

    #254991

    Hi,

    I am tuning a query and execution plan is telling me the implicit conversion. I think this conversion is the cause of index scan rather seek.

    "[dbo].[tblEcomConfiguration].[SiteId] as [this_].[SiteId]=CONVERT_IMPLICIT(int,[@1],0)"

    SiteId is a reference attribute from tblSite. I have verified that on both table datatype is same.

    Query has no explicit conversion function.

    How to handle this situation ?

    --== below is a complete XML plan (predicate section only)

    <Predicate>

    <ScalarOperator ScalarString="[database].[dbo].[tblEcomConfiguration].[SiteId] as [this_].[SiteId]=CONVERT_IMPLICIT(int,[@1],0)">

    <Compare CompareOp="EQ">

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[database]" Schema="[dbo]" Table="[tblEcomConfiguration]" Alias="[this_]" Column="SiteId" />

    </Identifier>

    </ScalarOperator>

    <ScalarOperator>

    <Identifier>

    <ColumnReference Column="ConstExpr1002">

    <ScalarOperator>

    <Convert DataType="int" Style="0" Implicit="true">

    <ScalarOperator>

    <Identifier>

    <ColumnReference Column="@1" />

    </Identifier>

    </ScalarOperator>

    </Convert>

    </ScalarOperator>

    </ColumnReference>

    </Identifier>

    </ScalarOperator>

    </Compare>

    </ScalarOperator>

    </Predicate>

  • March 20, 2012 at 10:21 am

    #1461757

    is the datatype of the parameter passed also int, or is there an ISNULL(@param,0) used anywhere in the code?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 20, 2012 at 10:26 am

    #1461763

    this is great Lowell. it was not in my consideration .

    I will ask developer.

  • March 20, 2012 at 10:26 am

    #1461765

    Can you post the entire execution plan please? Save it, zip and attach to your post.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 20, 2012 at 10:57 am

    #1461779

    Hi GilaMonster,

    I have attached the explain plan.

    I have tuned another query and successfully convert index scan to index seek. But when I have index seek, keyLookup also came . This keylookup saying SeekPredicate are (indexed column) ........

    So this keylookup is acceptable or have to remove ?

  • March 20, 2012 at 11:28 am

    #1461794

    I'm guessing that because the command sent was ...

    WHERE [this_].[SiteId] = 5506

    the 5506 has to be implicitly converted to the approriate type(the column it is compared to)

    if you declared a parameter and used that, you'd loose the implicit conversion.,

    if [this_].[SiteId] was a smallint, it would be implicitly converted to a small int, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 20, 2012 at 12:04 pm

    #1461804

    true Lowell.

    I have tested it on TSQL, when I declare variable as varchar , it has implicit conversion as int it has not but start showing other predicate 🙁

  • March 20, 2012 at 12:08 pm

    #1461808

    does a plan for this look any better?

    Declare @param int

    SET @param=5506

    SELECT [this_].[EcomConfigurationId] [EcomConf1_3_0_],

    [this_].[EcomEmailAddress] [EcomEmai2_3_0_],

    [this_].[SecondryEcomEmailAddress] [Secondry3_3_0_],

    [this_].[Partner] [Partner3_0_],

    [this_].[MerchantLogin] [Merchant5_3_0_],

    [this_].[UserName] [UserName3_0_],

    [this_].[Password] [Password3_0_],

    [this_].[SiteId] [SiteId3_0_]

    FROM [tblEcomConfiguration] [this_]

    WHERE [this_].[SiteId] = @param

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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