Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Actual Exec plan not recommended missing index? Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 6:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:58 AM
Points: 1,061, Visits: 3,020

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



  Post Attachments 
gadwise_plan.sqlplan (8 views, 1.25 MB)
Post #1350339
Posted Monday, August 27, 2012 6:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:11 AM
Points: 39,968, Visits: 36,328
Query, table definitions and index definitions please.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1350341
Posted Monday, August 27, 2012 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 12,901, Visits: 32,136
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1350343
Posted Monday, August 27, 2012 6:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:58 AM
Points: 1,061, Visits: 3,020
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


  Post Attachments 
index details.xlsx (2 views, 16.59 KB)
Post #1350351
Posted Monday, August 27, 2012 6:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:11 AM
Points: 39,968, Visits: 36,328
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 2008, MVP
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

Post #1350358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse