Optimization not enough

  • Hi,

    In my execution plan i can see some warning like:

    1. "Type conversion in expression (CONVERT_IMPLICIT(varchar(40),[PAIntel].[ActivationDateTime],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(varchar(24),[Machine].[MachineKey],0)) may affect "CardinalityEstimate" in query plan choice "

    2. I am not using any order by clause in my query but still its having an sort with warning as

    Operator used tempdb to spill data during execution with spill level 2

    3. I have a Key look up (clustred) with high cost how do we remove this.

    I am doing join with 2 views (there is no indexed view)

    and one table is having 84 million records

    Any help much appreciated.

  • Query, table definitions, index definitions please.

    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
  • 1 st tabel is having 48 Lacks recoprds

    the 5 th table is having 84 million records

  • Wow!

    The script name have nothing to identify the table in question.

    You really like to provide puzzles, do you?

    Also, please provide the code of DML.GetRetailOrOnlineIndicator(), which seems to be a function you're calling in your insert script.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • CREATE FUNCTION [DML].[GetRetailOrOnlineIndicator](

    @GrpID INT

    ,@ChanID INT

    ,@SeqNo INT

    )

    RETURNS NVARCHAR(10)

    AS

    BEGIN

    DECLARE @RetailOrOnlineIndicator NVARCHAR(10)

    IF EXISTS (SELECT 1

    FROM DML.ExtKeyWestRetailOnlineInfo

    WHERE GrpID = @GrpID AND SeqNo = CONVERT( BIGINT, CAST(@ChanID AS VARCHAR(3)) + RIGHT('000000'+ CAST(@SeqNo AS VARCHAR(6)),6) ))

    SET @RetailOrOnlineIndicator = 'Online'

    ELSE

    SET @RetailOrOnlineIndicator = 'Retail'

    RETURN @RetailOrOnlineIndicator

    END

    GO

  • May I ask for the table definition involved?

    You provided a view definition. But we'd need the table def. Especially for [ExtOLSMachine].



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • i dodn't have any table with name as ExtOLSMachine

  • The View [DML].[ExtOLSMachineEntitlement] refers to a table DML.ExtOLSMachine.

    You might want to take the effort to have a look at your execution plan... -> The table is indeed referenced.

    I guess I'm out of this challenge...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi Can you pls help me to study and understand the execution plan properly, i have not done this work earlier but not sure where to look,what to look so posted all query.

  • First thing I see in the plan is Query 6 which shows up as 99% of the cost.

    Within Query 6, there is a section that accounts for 96% of the plan cost

    I suspect (from the estimated and actual rowcounts and the predicates present in the Index Seek on ExtPAIntelActivationEvent.NCIX_ISOCountrycode which includes a condition; ISOCountryCode <> N'' which does not appear anywhere in the query text) that you have another view, perhaps within the Scalar function, perhaps this "DML.ExtKeyWestRetailOnlineInfo"

    I would inspect that view to see if it is optimised for the purpose of this query.

    I would also rewrite the Scalar function GetRetailOrOnlineIndicator as a Table Valued Function (see here : http://www.sqlservercentral.com/articles/T-SQL/91724/[/url] )

    Once that is done, test Query 6 from the plan without the INSERT to see if the row estimates are any better and to see if performance has improved.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

Viewing 10 posts - 1 through 9 (of 9 total)

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