Actual Exec plan not recommended missing index?

  • Query takes too much time for getting 90 rows also I have attached Actucal Execution plan, could you suggestion me how to increase performance?

  • Query, table definitions and 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
  • I see the execution plan returns 8 columns of data and 90 rows, but had estimated 237,932;

    I see that WR_SCOPE_FITUP is actually a view of other tables; are you sure you need to use the view? can you query the tables directly?

    for me, the first thing I would do is update statistics

    for all the tables that are used in the view.

    I see a lot of Compute Scalar operations in the exection plan, so I suspect th3e view is filled with them;

    my next priority would be to looka t the view, and change the scalar functions to inline table value functions;

    It's a big plan, I'll keep looking at it to see if I see anything obvious.

    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!

  • yes, your correct direction in that execution plan.. i am not witting that code

    one of the devleoper wirtting like this, after that he called me you increase the performance. I m totally confuse

    I have attached index details

    this is one view ( wr_scope_gadwise)

    -------------------

    select t1.GA_Drg_NO, SUM(t1.wr_scope) as wr_scope ,SUM(t1.fitup) as fitup,SUM(t1.weld) as weld,t2.Fitup_SFSNO,t1.JOBCODE,t1.COMPLEXCODE,t1.UNITCODE

    from WR_SCOPE_FITUP as t1

    left join WR_SCOPE_FITUP as t2 on t1.GA_Drg_NO = t2.GA_Drg_NO

    and t2.Fitup_SFSNO is not null

    group by t1.JOBCODE,t1.COMPLEXCODE,t1.UNITCODE,t1.GA_Drg_NO,t2.Fitup_SFSNO

    GO

    this is second view ( WR_Scope_fitup)

    -------------------

    SELECT GA_Drg_NO, JOBCODE, COMPLEXCODE, UNITCODE, Fitup_SFSNO, WR_Scope, fitup, weld, NDT, intermediatecoat, finalcoat, dispatch

    FROM (SELECT DISTINCT t1.GA_Drg_NO, t3.JOBCODE, t3.COMPLEXCODE, t3.UNITCODE, t1.Fitup_SFSNO, t2.item_wt AS WR_Scope,

    (SELECT fitup

    FROM dbo.Fitup_Qty_SFSNO AS tf

    WHERE (Fitup_SFSNO = t1.Fitup_SFSNO) AND (GA_Drg_NO = t1.GA_Drg_NO)) AS fitup,

    (SELECT weld

    FROM dbo.weld_Qty_SFSNO AS tw

    WHERE (Fitup_SFSNO = t1.Fitup_SFSNO) AND (GA_Drg_NO = t1.GA_Drg_NO)) AS weld,

    (SELECT ISNULL(NDE, 0) AS Expr1

    FROM dbo.NDE_Qty_SFSNO AS tut

    WHERE (Fitup_SFSNO = t1.Fitup_SFSNO) AND (GA_Drg_NO = t1.GA_Drg_NO)) AS NDT,

    (SELECT Intermediatecoat

    FROM dbo.Intermediate_Qty_SFSNO AS ic

    WHERE (Fitup_SFSNO = t1.Fitup_SFSNO) AND (GA_Drg_NO = t1.GA_Drg_NO)) AS intermediatecoat,

    (SELECT finalcoat

    FROM dbo.Finalcoat_Qty_SFSNO AS fc

    WHERE (Fitup_SFSNO = t1.Fitup_SFSNO) AND (GA_Drg_NO = t1.GA_Drg_NO)) AS finalcoat,

    (SELECT dispatch

    FROM dbo.Dispatch_Qty_SFSNO AS dis

    WHERE (Fitup_SFSNO = t1.Fitup_SFSNO) AND (GA_Drg_NO = t1.GA_Drg_NO)) AS dispatch

    FROM dbo.Drawing_Inspection_Detail AS t1 INNER JOIN

    dbo.WR_SCope AS t2 ON t1.GA_Drg_NO = t2.GA_Drg_NO INNER JOIN

    dbo.Transmittal_GAD_JOBComplexUnitWise AS t3 ON t3.Drawing_No = t1.GA_Drg_NO

    UNION

    SELECT GA_Drg_NO, JobCode, ComplexCode, UnitCode, NULL AS Fitup_SFSNO, SUM(itmwt) AS WR_Scope, NULL AS fitup, NULL AS weld, NULL

    AS NDT, NULL AS intermediatecoat, NULL AS finalcoat, NULL AS dispatch

    FROM (SELECT DISTINCT

    a.GA_Drg_NO, a.Rev_NO, a.JobCode, a.ComplexCode, a.UnitCode, a.Mark_Rev_No_fab, ISNULL(SUM(CAST(ISNULL(a.Itme_Wt,

    0) AS decimal(18, 3))), 0) AS itmwt

    FROM dbo.CSV_Details AS a INNER JOIN

    dbo.UnAssigned_Markno ON a.GA_Drg_NO = dbo.UnAssigned_Markno.GA_Drg_NO AND

    a.Mark_Rev_No_fab = dbo.UnAssigned_Markno.Mark_Rev_No_fab

    WHERE (a.Rev_NO =

    (SELECT MAX(CAST(Rev_NO AS int)) AS rev_no

    FROM dbo.CSV_Details AS c

    WHERE (GA_Drg_NO = dbo.UnAssigned_Markno.GA_Drg_NO)))

    GROUP BY a.GA_Drg_NO, a.Rev_NO, a.Mark_Rev_No_fab, a.JobCode, a.ComplexCode, a.UnitCode) AS tab1

    GROUP BY GA_Drg_NO, JobCode, ComplexCode, UnitCode) AS tab1

  • Formatted so that it's readable

    SELECT t1.GA_Drg_NO ,

    SUM(t1.wr_scope) AS wr_scope ,

    SUM(t1.fitup) AS fitup ,

    SUM(t1.weld) AS weld ,

    t2.Fitup_SFSNO ,

    t1.JOBCODE ,

    t1.COMPLEXCODE ,

    t1.UNITCODE

    FROM WR_SCOPE_FITUP AS t1

    LEFT JOIN WR_SCOPE_FITUP AS t2 ON t1.GA_Drg_NO = t2.GA_Drg_NO

    AND t2.Fitup_SFSNO IS NOT NULL

    GROUP BY t1.JOBCODE ,

    t1.COMPLEXCODE ,

    t1.UNITCODE ,

    t1.GA_Drg_NO ,

    t2.Fitup_SFSNO

    GO

    SELECT GA_Drg_NO ,

    JOBCODE ,

    COMPLEXCODE ,

    UNITCODE ,

    Fitup_SFSNO ,

    WR_Scope ,

    fitup ,

    weld ,

    NDT ,

    intermediatecoat ,

    finalcoat ,

    dispatch

    FROM ( SELECT DISTINCT

    t1.GA_Drg_NO ,

    t3.JOBCODE ,

    t3.COMPLEXCODE ,

    t3.UNITCODE ,

    t1.Fitup_SFSNO ,

    t2.item_wt AS WR_Scope ,

    ( SELECT fitup

    FROM dbo.Fitup_Qty_SFSNO AS tf

    WHERE ( Fitup_SFSNO = t1.Fitup_SFSNO )

    AND ( GA_Drg_NO = t1.GA_Drg_NO )

    ) AS fitup ,

    ( SELECT weld

    FROM dbo.weld_Qty_SFSNO AS tw

    WHERE ( Fitup_SFSNO = t1.Fitup_SFSNO )

    AND ( GA_Drg_NO = t1.GA_Drg_NO )

    ) AS weld ,

    ( SELECT ISNULL(NDE, 0) AS Expr1

    FROM dbo.NDE_Qty_SFSNO AS tut

    WHERE ( Fitup_SFSNO = t1.Fitup_SFSNO )

    AND ( GA_Drg_NO = t1.GA_Drg_NO )

    ) AS NDT ,

    ( SELECT Intermediatecoat

    FROM dbo.Intermediate_Qty_SFSNO AS ic

    WHERE ( Fitup_SFSNO = t1.Fitup_SFSNO )

    AND ( GA_Drg_NO = t1.GA_Drg_NO )

    ) AS intermediatecoat ,

    ( SELECT finalcoat

    FROM dbo.Finalcoat_Qty_SFSNO AS fc

    WHERE ( Fitup_SFSNO = t1.Fitup_SFSNO )

    AND ( GA_Drg_NO = t1.GA_Drg_NO )

    ) AS finalcoat ,

    ( SELECT dispatch

    FROM dbo.Dispatch_Qty_SFSNO AS dis

    WHERE ( Fitup_SFSNO = t1.Fitup_SFSNO )

    AND ( GA_Drg_NO = t1.GA_Drg_NO )

    ) AS dispatch

    FROM dbo.Drawing_Inspection_Detail AS t1

    INNER JOIN dbo.WR_SCope AS t2 ON t1.GA_Drg_NO = t2.GA_Drg_NO

    INNER JOIN dbo.Transmittal_GAD_JOBComplexUnitWise AS t3 ON t3.Drawing_No = t1.GA_Drg_NO

    UNION

    SELECT GA_Drg_NO ,

    JobCode ,

    ComplexCode ,

    UnitCode ,

    NULL AS Fitup_SFSNO ,

    SUM(itmwt) AS WR_Scope ,

    NULL AS fitup ,

    NULL AS weld ,

    NULL AS NDT ,

    NULL AS intermediatecoat ,

    NULL AS finalcoat ,

    NULL AS dispatch

    FROM ( SELECT DISTINCT

    a.GA_Drg_NO ,

    a.Rev_NO ,

    a.JobCode ,

    a.ComplexCode ,

    a.UnitCode ,

    a.Mark_Rev_No_fab ,

    ISNULL(SUM(CAST(ISNULL(a.Itme_Wt, 0) AS DECIMAL(18, 3))), 0) AS itmwt

    FROM dbo.CSV_Details AS a

    INNER JOIN dbo.UnAssigned_Markno ON a.GA_Drg_NO = dbo.UnAssigned_Markno.GA_Drg_NO

    AND a.Mark_Rev_No_fab = dbo.UnAssigned_Markno.Mark_Rev_No_fab

    WHERE ( a.Rev_NO = ( SELECT MAX(CAST(Rev_NO AS INT)) AS rev_no

    FROM dbo.CSV_Details AS c

    WHERE ( GA_Drg_NO = dbo.UnAssigned_Markno.GA_Drg_NO )

    ) )

    GROUP BY a.GA_Drg_NO ,

    a.Rev_NO ,

    a.Mark_Rev_No_fab ,

    a.JobCode ,

    a.ComplexCode ,

    a.UnitCode

    ) AS tab1

    GROUP BY GA_Drg_NO ,

    JobCode ,

    ComplexCode ,

    UnitCode

    ) AS tab1

    I think you want to start by tuning (rewriting) the second view. Or eliminating it from the query and going straight to the base tables if possible. This isn't a 'add a couple indexes and it'll be fine' job, the code's not optimal (unions, distinct, multiple correlated subqueries, etc)

    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

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

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