Actual Exec plan not recommended missing index?

Question

Actual Exec plan not recommended missing index?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 27, 2012 at 6:03 am

#263348

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSC Guru Points: 323518 More actions · Answer 2

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 One Orange Chip Points: 29472 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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