SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Actual Exec plan not recommended missing index?


Actual Exec plan not recommended missing index?

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6787 Visits: 3501
Query takes too much time for getting 90 rows also I have attached Actucal Execution plan, could you suggestion me how to increase performance?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223482 Visits: 46297
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


Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71420 Visits: 40930
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!
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6787 Visits: 3501
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223482 Visits: 46297
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search