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