SQl Stored Procedure - View Tuning
-
Hi,
We have a stored procedure which calling view which contains bunch of tables in sql 2014 and 2017 and it's slow. View contains roughly 100K records.
I was looking view and based on joins and where clause i have created index but nothing gain as performance it's same.
I tried following:
Created Index based on Execution plan and sql Tuning advisor
Created and Updated statistics
2A) Removed Fragmentation and Rebuilt Indexes
I have also changed Fill Factor from default to 90 as query has DML activities
Also changed PAD_Index to ON
Changed MAXDOP to 4 (equal to Processor)
Tried Stored Procedure with RECOMPILE
STored Procedure, we are also using SET NOCOUNT ON
Stored Procedure is doing only INSERT and UPDATE
in beginning, we are declaring variable as TABLE
View is using FULL OUTER JOIN and then all others tables are join with LEFT JOIN, no other Where conditions.
After doing all, Performance and Timing is same when i execute the procedure so no change.
When i do Sql count(*) for View, it's also taking little longer time.
Any suggestions, appreciated!
Thank You!
-
Have a look at the (actual) query plan after the execution of the stored procedure to show what SQL is executed.
Brent Ozar is currently presenting a tuning week online (til 1st april) which is an excellent start
-
Thank you JO.
I checked Execution plan and one recommended index also created.
Execution plan mostly having Index seek, Index Scan (for PK).
It is in break down for each Insert and Update and select, select is mostly from view.
Based on WHERE clause and order by, i have also created index
-
If you want help with such a performance issue, please see the 2nd link in my signature line below for how to present it in the best way possible to get help.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Thank you for your response and suggesting about other details, i am totally agreed with you.
i have link for the plan, please see the following link if you don't mind.
https://www.brentozar.com/pastetheplan/?id=SytUhZQLU
- pdsqsql wrote:
Jeff,
Thank you for your response and suggesting about other details, i am totally agreed with you.
i have link for the plan, please see the following link if you don't mind.
Yeah...no... Brent's execution plans are really pretty and all but they leave out a whole lot. For example, there's no sign of predicate information. Please attach the sqlplan and the code you posted on Brent's site.
Also, I count 25 Clustered Index Scans in your view and the bloody thing also has a sort. If you're going to use that view, someone needs to spend a little time fixing that view. Like I said, Brent' stuff is nice but most of us are going to need a real execution plan from SSMS and not one from some third party filtered stuff.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. Since we're actually trying to help you tune the view, it would be helpful if you actually posted the code for the view, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff.
I appreciate your tips.
Please see the view.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW vw_ORDTun
AS
SELECT b.ID AS OrgID
,b.OrgCd
,b.OrgNm
,b.OrgTLA
,a.OrdNo
,CASE WHEN COALESCE(BID,'') != '' THEN sh.PrtCd ELSE b.OrgCd END AS ShpCd
,CASE WHEN COALESCE(BID,'') != '' THEN sh.PrtNm ELSE b.OrgNm END AS ShpNm
,CASE WHEN COALESCE(BID,'') != '' THEN sh.Prt1 ELSE b.Addr1 END AS Shp1
,CASE WHEN COALESCE(BID,'') != '' THEN sh.Prt2 ELSE b.Addr2 END AS Shp2
,CASE WHEN COALESCE(BID,'') != '' THEN sh.PrtCT ELSE b.City END AS ShpCT
,CASE WHEN COALESCE(BID,'') != '' THEN fn_StCd(sh.PrtSTId) ELSE fn_StCd(b.STId) END AS ShpST
,CASE WHEN COALESCE(BID,'') != '' THEN sh.Prtzp ELSE b.Zip END AS ShpZP
,a.CustOrdNo
,a.DelNo
,a.3PrtRef
,CASE COALESCE(a.OrdNo,CONVERT(NVARCHAR(32),s.SavTktFlg)) WHEN '1' THEN 'T' ELSE a.sts END AS sts
,CASE COALESCE(a.OrdNo,CONVERT(NVARCHAR(32),s.SavTktFlg)) WHEN '1' THEN 'TktOnl' ELSE z1.Name END AS StatDesc
,CAST(COALESCE(SIGN(CAST(CAST(BID AS VARBINARY) AS BIGINT)),0) AS BIT) AS Stfgflg
,a.OrgAlCar
,c.Cod AS PrdCd
,c.ID AS PrdID
,c.ShrtNm AS PrdShrtNm
,c.Name AS PrdNm
,c.Desc AS PrdDesc
,c.Den AS PrdDen
,c.SpGrvt AS PrdSpGrvt
,f.HazFlg AS PrdHazFlg
,c.PodReqFlg AS PodReqFlg
,COALESCE(Fn_DispCnfg('prd.fld.fntclr',c.Cod),'DEFAULT') AS PrdClr
,f.Cls AS PrdCls
,f.ID AS PrdClsID
,f.CnBlndFlg AS PrdBlnd
,f.CnBlndFlg AS ClsBlnd
,f.CnSpltFlg AS PrdSplt
,f.CnSpltFlg AS ClsSplitable
,f2.EnblFlg
,f.HazFlg AS ClsHaz
,f.HazFlg AS ClsHaz1
,a.ShpLokFlgAS CnChgDestFlg
,Fn_PrdClsWt(f.Cls,'Min') AS ClsMin
,Fn_PrdClsWt(f.Cls,'Max') AS ClsMax
,Fn_PrdClsWt(f.Cls,'MinGrs') AS ClsMinGrs
,Fn_PrdClsWt(f.Cls,'MaxGrs') AS ClsMaxGrs
,Fn_PrdClsWt(f.Cls,'TrlThrsHld') AS MltThrshld
,Fn_PrdClsWt(f.Cls,'mltMin') AS ClsMultiMin
,Fn_PrdClsWt(f.Cls,'mltMax') AS ClsMultiMax
,Fn_PrdClsWt(f.Cls,'MltMinGrs') AS ClsMltMinGrs
,Fn_PrdClsWt(f.Cls,'MltMxGrs') AS ClsMltMxGrs
,COALESCE(CONVERT(BIT,Fn_SitCnfg('st.2.trl.alwd')),0)&f2.DblTrlFlg AS DblTrlAlwd -- needs true for site and Cls
,CASE WHEN (COALESCE(c1.ConReqFlg,'') != '') THEN c1.ConReqFlg ELSE f2.ConReqFlg END AS ConReqFlg
,CASE WHEN (COALESCE(c1.TmpReqFlg,'') != '') THEN c1.TmpReqFlg ELSE f2.TmpReqFlg END AS TmpReqFlg
,CASE WHEN (COALESCE(c1.PhReqFlg,'') != '') THEN c1.PhReqFlg ELSE f2.PhReqFlg END AS PhReqFlg
,f2.LdStReqFlg
,f2.0WtFlg
,f2.0WtWrngFlg AS 0trWrngFlg
,COALESCE(v1.PrtCd,a.Sld2Cd) AS Sld2Cd
,COALESCE(Fn_DispCnfg('cst.fld.fntClr',COALESCE(v1.PrtCd,a.Sld2Cd)),'DEFAULT') AS Sld2Clr
,COALESCE(v1.PrtNm,g1.PrtNm) AS Sld2Nm
,CASE WHEN CHARINDEX(COALESCE(v2.PrtCd,a.Shp2Cd),Fn_SitCnfg('dflt.shp2.c2a'),1)>0 OR COALESCE(v2.PrtNm,g2.PrtNm,'') = '' THEN '' ELSE COALESCE(v2.PrtCd,a.Shp2Cd) END AS Shp2Cd
,COALESCE(Fn_DispCnfg('destination.field.fontcolor',COALESCE(v2.PrtCd,a.Shp2Cd)),'DEFAULT') AS Shp2Clr
,CASE WHEN CHARINDEX(COALESCE(v2.PrtCd,a.Shp2Cd),Fn_SitCnfg('dflt.shp2.c2a'),1)>0 OR COALESCE(v2.PrtCd,a.Shp2Cd,'') = '' OR COALESCE(v2.PrtNm,g2.PrtNm,'') = '' THEN 'ACTUAL' ELSE COALESCE(v2.PrtNm,g2.PrtNm) END AS Shp2Nm
,CASE WHEN v2.PrtCd IS NULL THEN g2.Prt1 ELSE v2.Prt1 END AS Shp2Adr1
,CASE WHEN v2.PrtCd IS NULL THEN CASE WHEN COALESCE(g2.AltCity,'') = '' THEN g2.PrtCT ELSE g2.AltCity END ELSE v2.PrtCT END AS Shp2Ct
,CASE WHEN v2.PrtCd IS NULL THEN g2.PrtCT ELSE Fn_BasCt(v2.PrtCT) END AS BaseShp2Ct
,CASE WHEN v2.PrtCd IS NULL THEN fn_StCd(g2.PrtSTId) ELSE fn_StCd(v2.PrtSTId) END AS Shp2ST
,CASE WHEN v2.PrtCd IS NULL THEN g2.Prtzp ELSE v2.Prtzp END AS Shp2Zp
,CASE WHEN v2.PrtCd IS NULL THEN Fn_Cntr(DEFAULT,g2.PrtSTId) ELSE Fn_Cntr(DEFAULT,v2.PrtSTId) END AS Shp2Cntr
,CASE WHEN CHARINDEX(COALESCE(v2.PrtCd,a.Shp2Cd),Fn_SitCnfg('dflt.shp2.c2a'),1)>0 OR COALESCE(v2.PrtCd,a.Shp2Cd,'') = '' OR COALESCE(v2.PrtNm,g2.PrtNm,'') = '' THEN 1 ELSE 0 END AS C2AFlag
,CASE WHEN CHARINDEX(a.Shp2Cd,Fn_SitCnfg('dflt.shp2.c2a'),1)>0 OR COALESCE(g2.PrtCd,'') = '' OR COALESCE(g2.PrtNm,'') = '' OR COALESCE(g2.ShpFlg,1) = 0 THEN '' ELSE a.Shp2Cd END AS OrdShp2Cd
,COALESCE(Fn_DispCnfg('destination.field.fontcolor',a.Shp2Cd),'DEFAULT') AS OrdShp2Clr
,CASE WHEN CHARINDEX(a.Shp2Cd,Fn_SitCnfg('dflt.shp2.c2a'),1)>0 OR COALESCE(g2.PrtCd,'') = '' OR COALESCE(g2.PrtNm,'') = '' OR COALESCE(g2.ShpFlg,1) = 0 THEN 'CHANGE TO ACTUAL' ELSE g2.PrtNm END AS OrdShp2Nm
,CASE COALESCE(g2.ShpFlg,1) WHEN 0 THEN '' ELSE g2.Prt1 END AS OrdShp2Adr1
,CASE COALESCE(g2.ShpFlg,1) WHEN 0 THEN '' ELSE CASE WHEN COALESCE(g2.AltCity,'') = '' THEN g2.PrtCT ELSE g2.AltCity END END AS OrdShp2Ct
,CASE COALESCE(g2.ShpFlg,1) WHEN 0 THEN '' ELSE g2.PrtCT END AS BaseOrdShp2Ct
,CASE COALESCE(g2.ShpFlg,1) WHEN 0 THEN '' ELSE fn_StCd(g2.PrtSTId) END AS OrdShp2ST
,CASE COALESCE(g2.ShpFlg,1) WHEN 0 THEN '' ELSE g2.Prtzp END AS OrdShp2Zp
,CASE COALESCE(g2.ShpFlg,1) WHEN 0 THEN '' ELSE Fn_Cntr(DEFAULT,g2.PrtSTId) END AS OrdShp2Cntr
,CASE WHEN CHARINDEX(a.Shp2Cd,Fn_SitCnfg('dflt.shp2.c2a'),1)>0 OR COALESCE(g2.PrtCd,'') = '' OR COALESCE(g2.PrtNm,'') = '' OR COALESCE(g2.ShpFlg,1) = 0 THEN 1 ELSE a.C2AFlag END AS OrdC2AFlag
,g2.Cert AS Cert --
,CASE WHEN ((f.Cls = 'NH3' AND Fn_Cntr(DEFAULT,COALESCE(v2.PrtSTId,g2.PrtSTId)) = 'CAN') OR f.Cls = 'AN') AND Fn_DestCert(g2.Cert,f.Cls) = 0 THEN 1 ELSE 0 END AS Restrctd
,s.MnlFlg
,a.Shp2LkFlg
,CASE WHEN f.DestCertReqFlag = 1 AND ((f.Cls = 'NH3' AND Fn_Cntr(DEFAULT,COALESCE(v2.PrtSTId,g2.PrtSTId)) = 'CAN') OR f.Cls = 'AN') THEN 1 ELSE 0 END AS DestCertReqFlag
,a.FrtPyrCod
,COALESCE(v3.PrtNm,g3.PrtNm) AS FrtPyrNm
,d.Cod AS Div
,e.Cod AS Mode
,a.FOB
,a.FreightCod
,a.OrdQty
,a.OrdUOM
,a.OrdDt
,a.ReqShpDt
,f.HazFlg AS HazFlg
,COALESCE(u1.Value,j1.Value,'') AS Ins1
,COALESCE(u2.Value,j2.Value,'') AS Ins2
,COALESCE(u3.Value,j3.Value,'') AS Ins3
,COALESCE(j1.Value,'') AS OrdIns1
,COALESCE(j2.Value,'') AS OrdIns2
,COALESCE(j3.Value,'') AS OrdIns3
,CASE
WHEN b.OrgCompCod='600001' AND DATEDIFF(MONTH, 0, SYSDATETIMEOFFSET())<>DATEDIFF(MONTH, 0, a.ReqShpDt) THEN 1
WHEN b.OrgCompCod='600001' AND a.UnShpDT='2030-07-04' THEN 1
WHEN b.OrgCompCod='600001' AND a.UnShpDT='2999-12-31' THEN 1
WHEN b.OrgCompCod<>'600001' AND a.UnShpDT >= SYSDATETIMEOFFSET() THEN 1
WHEN b.OrgCompCod<>'600001' AND DATEDIFF(DAY,SYSDATETIMEOFFSET(),a.ReqShpDt) > 3 THEN 1
ELSE 0
END AS UnShpFlg
,a.RevLkDT
,a.RevNo
,a.ARCDT
,s.ID AS TktID
,s.TktNo
,CASE COALESCE(CONVERT(BIT,Fn_SitCnfg('sit.corp.mir')),0) WHEN 1 THEN COALESCE(s.ChkIDT,Fn_ActvtTS('DB','PostShmnt','Chk-In',a.OrdNo)) ELSE s.ChkIDT END AS ChkIDT
,CASE COALESCE(CONVERT(BIT,Fn_SitCnfg('sit.corp.mir')),0) WHEN 1 THEN COALESCE(s.ChkODT,Fn_ActvtTS('DB','PostShmnt','Chk-Out',a.OrdNo)) ELSE s.ChkODT END AS ChkODT
,s.OpCrir
,s.Trac
,s.Trail
,s.LdStation
,l.ID AS LdID
,l.Name AS LdNm
,l.Desc LdDesc
,CASE WHEN e.Cod IN ('K','P') THEN 0 ELSE (CASE WHEN s.ID IS NULL THEN Fn_PrdClsWt(f.Cls,'reqGrs') ELSE s.ReqGrsQty END) END AS ReqGrsQty
,w1.Qty AS TareQty
,w1.ManFlg AS ManFlg
,w2.Qty AS GrossQty
,w2.ManFlg AS ManGrsFlg
,s.NetQty
,'LB' AS DisUOM
,s.ShpUOM
,t.ID AS OpID
,t.Name AS OpNm
,s.BlndFlag
,s.BlndDT
,s.SpltFlg
,s.SpltDT
,s.SavTktFlg
,s.SavTktDT
,s.PrkFlag
,s.PrkDT
,Fn_SclID(s.SclInCod) AS SclInID
,s.SclInCod
,s.SclDT
,Fn_SclID(s.SclOCod) AS SclOID
,s.SclOtCod
,s.SclOtDT
,s.Seals
,s.OWtFlg
,s.PrntQFlg
,s.MTrlFlg
,s.EdDataFlg
,s.Temp
,s.Concent
,s.pHLvl
,s.CabCardNo
,s.PODCod
,s.ADAcct
,s.McNam
,si.Desc AS SclInNm
,so.Desc AS SclOtNm
,s.ICPNFlg
,a.BID
,s.GubncPrc
,s.PrcUOM
,s.DrvName
,s.DrvMob
,s.ETAHrs
,s.HzVislFlg
,s.OrgalDstFlg
,s.PGSntFlag
,s.PGSntDT
,a.TxID
,f2.BarCodFlg
,a.CustSoldTo
,s.POfEID
,s.PAPSNo
,a.OrdTyp
,c.CASNo
,c.Usage
FROM Ord a
FULL OUTER JOIN Ship s
ON s.OrgID = a.OrgID
AND s.OrdNo = a.OrdNo
LEFT JOIN Org b
ON b.ID = COALESCE(a.OrgID,s.OrgID)
LEFT JOIN Prd c
ON c.ID = a.PrdID
LEFT JOIN PrdCnfig c1
ON c1.PrdID = a.PrdID
LEFT JOIN Div d
ON d.ID = a.DivID
LEFT JOIN TrnsMod e
ON e.ID = a.ModeID
AND e.Dir = 'O'
LEFT JOIN PrdCls f
ON f.ID = c.ClsID
LEFT JOIN PrdClsConfigs f2
ON f2.ID = c.ClsID
LEFT JOIN Party g1
ON g1.PrtCd = a.Sld2Cd
AND g1.PrtTyp = 'SOLDTO'
LEFT JOIN Party g2
ON g2.PrtCd = a.Shp2Cd
AND g2.PrtTyp = 'SHIP2'
LEFT JOIN Party g3
ON g3.PrtCd = a.FrtPyrCod
AND g3.PrtTyp = 'FrtPyr'
LEFT JOIN Party sh
ON sh.PrtCd = RTRIM(SUBSTRING(a.BID,2,6))
AND sh.PrtTyp = 'SLD2'
LEFT JOIN OrdIns j1
ON j1.OrgID = a.OrgID
AND j1.OrdNo = a.OrdNo
AND j1.Posi = 1
LEFT JOIN OrdIns j2
ON j2.OrgID = a.OrgID
AND j2.OrdNo = a.OrdNo
AND j2.Posi = 2
LEFT JOIN OrdIns j3
ON j3.OrgID = a.OrgID
AND j3.OrdNo = a.OrdNo
AND j3.Posi = 3
LEFT JOIN LodStatn l
ON l.Cod = s.LodStatn
LEFT JOIN Users t
ON t.ID = s.OpID
LEFT JOIN ShpIns u1
ON u1.OrgID = a.OrgID
AND u1.OrdNo = a.OrdNo
AND u1.Position = 1
LEFT JOIN ShpIns u2
ON u2.OrgID = a.OrgID
AND u2.OrdNo = a.OrdNo
AND u2.Posi = 2
LEFT JOIN ShpIns u3
ON u3.OrgID = a.OrgID
AND u3.OrdNo = a.OrdNo
AND u3.Position = 3
LEFT JOIN ShpPart v1
ON v1.ShpID = s.ID
AND v1.PartyType = 'SOLDTO'
LEFT JOIN ShipParties v2
ON v2.ShipID = s.ID
AND v2.PartyType = 'SHIPTO'
LEFT JOIN ShipParties v3
ON v3.ShipID = s.ID
AND v3.PartTyp = 'FrtPyr'
LEFT JOIN ShpWt w1
ON w1.ShpID = s.ID
AND w1.WtTyp = 'TAR'
LEFT JOIN ShpWet w2
ON w2.ShpID = s.ID
AND w2.WtTyp = 'Grs'
LEFT JOIN StatDesc z1
ON z1.Cod = a.sts
LEFT JOIN Scl si
ON si.Cod = s.SclInCod
LEFT JOIN Scales so
ON so.Cod = s.SclOtCod -
We need the actual execution plan and the original code that you published on Brent's site, as well, please.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It would also be beneficial if you provided the execution plan from 2014 and the execution plan from 2017. The comparison will probably show that the 2017 version is much different - which is most likely due to the changes in the cardinality estimator.
A couple of quick points:
- The code inserts at least 100K rows into a table variable.
- The code then updates the table variable twice...the first time updates the StateName column which isn't included in the insert into the table variable. The second time updates multiple columns where some of the columns are included in the insert - but others are not included.
I would recommend trying to rewrite the query without a table variable, moving the OUTER APPLY's into the primary select statement (you could even rewrite the the 6 outer apply's as a single cross-tab/pivot - or pre-aggregate that section into a temp table). If that doesn't help much - the next option would be to change from a table variable to a temp table...
Either way...this query needs to be rewritten as is...and I suspect the view will need to be modified quite a bit also.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffery,
Thank so much for valuable suggestions.
Right now i am not worried much about 2017 as i'm trying to fix in 2014 as it's not like 2014 having an issue and 2017 doesn't but both having issue so i am thinking other tuning i tried and didn't help much so probably code (view or stored procedure) needs to be tuned?
You are right, looks like view is having issue and probably not written efficiently.
-
Jeff,
Any idea how i can post better way the execution plan here as it's big and lengthy and in three part?
View code i have already posted, i am also posting stored procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE USP_LdTkt
(@OrdNo varchar(32) = 'ALL'
,@tkn nvarchar(256) = 'A1B2C3')
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET @OrdNo = COALESCE(@OrdNo,'ALL')
SET @tkn = @tkn
DECLARE @crlf varchar(16) = CHAR(13)+CHAR(10)
,@lf varchar(16) = CHAR(10)
,@tab varchar(16) = CHAR(9)
,@nl varchar(16) = '|'
DECLARE @LdTkd as TABLE
(OrdNo nvarchar(32)
,Stts nchar(1)
,CustOrdNo nvarchar(64)
,3PrtRef nvarchar(64)
,OpCarr nvarchar(64)
,Trac nvarchar(16)
,Tral nvarchar(16)
,ChkInDT datetime2
,LdSt nvarchar(2)
,Sld2Nm nvarchar(64)
,Sld2Clr nvarchar(32)
,PrdID integer
,PrdCd nvarchar(16)
,PrdClr nvarchar(32)
,PrdNm nvarchar(64)
,PrdDen numeric(16,4)
,Shp2Cd nvarchar(32)
,Shp2Clr nvarchar(32)
,Shp2Nm nvarchar(64)
,Shp1 nvarchar(128)
,Shp2 nvarchar(32)
,Shp2CT nvarchar(32)
,Shp2St nchar(2)
,Shp2Nm nvarchar(32)
,Shp2Zp nvarchar(32)
,OrdQty numeric(16,4)
,ReqGrsQty integer
,OrdUOM nvarchar(4)
,TreQty integer
,ShpUOM nvarchar(4)
,TktNo integer
,Ins1 nvarchar(64)
,Ins2 nvarchar(64)
,Ins3 nvarchar(64)
,ReqShpDt date
,StrgHandFlg bit
,BltzTyp nchar(1)
,PrdDisp1 nvarchar(64)
,Qty1 integer
,UOM1 nvarchar(4)
,Den1 numeric(16,4)
,SG1 numeric(16,6)
,PrdDis2 nvarchar(64)
,Qty2 integer
,UOM2 nvarchar(4)
,Den2 numeric(16,4)
,SG2 numeric(16,6)
,PrdDisp3 nvarchar(64)
,Qty3 integer
,UOM3 nvarchar(4)
,Den3 numeric(16,4)
,SG3 numeric(16,6)
,PrdDisp4 nvarchar(64)
,Qty4 integer
,UOM4 nvarchar(4)
,Den4 numeric(16,4)
,SG4 numeric(16,6)
,PrdDisp5 nvarchar(64)
,Qty5 integer
,UOM5 nvarchar(4)
,Den5 numeric(16,4)
,SG5 numeric(16,6)
,PrdDisp6 nvarchar(64)
,Qty6 integer
,UOM6 nvarchar(4)
,Den6 numeric(16,4)
,SG6 numeric(16,6)
,SBCod bit
,Seals nvarchar(128))
INSERT INTO @LdTkd
(OrdNo
,Stts
,CustOrdNo
,3PrtRef
,OpCarr
,Trac
,Tral
,ChkInDT
,LdSt
,Sld2Nm
,Sld2Clr
,PrdID
,PrdCd
,PrdClr
,PrdNm
,PrdDen
,Shp2Cd
,Shp2Clr
,Shp2Nm
,Shp1
,Shp2
,Shp2CT
,Shp2St
,Shp2Zp
,OrdQty
,ReqGrsQty
,OrdUOM
,TreQty
,ShipUOM
,TktNo
,Ins1
,Ins2
,Ins3
,ReqShpDt
,StrgHandFlg
,BltzTyp
,PrdDisp1
,PrdDis2
,PrdDisp3
,PrdDisp4
,PrdDisp5
,PrdDisp6
,SBCod
,Seals)
SELECT OrdNo
,Stts
,CustOrdNo
,3PrtRef
,OpCarr
,Trac
,Tral
,CONVERT(datetime2,ChkInDT) as ChkInDT
,LdSt
,Sld2Nm
,Sld2Clr
,PrdID
,PrdCd
,PrdClr
,PrdNm
,PrdDen
,Shp2Cd
,Shp2Clr
,Shp2Nm
,Shp1
,'' as Shp2
,Shp2CT
,Shp2St
,Shp2Zp
,OrdQty
,ReqGrsQty
,OrdUOM
,TreQty
,CASE WHEN (Fn_SitConf('sit.uom') = 'MET' AND OrgCod NOT IN ('17','106','170','107','117')) THEN 'KG' ELSE 'LB' END as ShpUOM
,TktNo
,Ins1
,Ins2
,Ins3
,ReqShpDt
,StrgHandFlg
,CASE WHEN SpltFlg = 1 OR (SpltDT is NOT NULL AND Stts = 'O') THEN 'S'
WHEN BlndFlg = 1 OR (BlndDT is NOT NULL AND Stts = 'O') THEN 'B'
ELSE 'X'
END as BltzTyp
,'X' as PrdDisp1
,'X' as PrdDis2
,'X' as PrdDisp3
,'X' as PrdDisp4
,'X' as PrdDisp5
,'X' as PrdDisp6
,o.BarCodeFlag
,o.Seals
FROM VW_OrdTun o
WHERE (o.OrdNo = @OrdNo OR (@OrdNo = 'ALL' AND CONVERT(date,o.ChkInDT)=CONVERT(date,sysdatetime()) AND o.Stts = 'I'))
AND (o.Stts in ('I','S') OR (SpltDT is NOT NULL AND Stts = 'O'))
AND o.Mode = 'T'
ORDER BY ChkInDT desc
UPDATE @LdTkd
SET Shp2Nm = s.Name
FROM @LdTkd l
CROSS APPLY (SELECT Name FROM Stat WHERE Code = l.Shp2St) s
UPDATE @LdTkd
SET PrdDisp1=COALESCE(z1.PrdDisp,'X'), Den1=z1.Den, SG1=z1.SpGravt, Qty1=z1.Qty, UOM1=z1.UOM
,PrdDis2=COALESCE(z2.PrdDisp,'X'), Den2=z2.Den, SG2=z2.SpGravt, Qty2=z2.Qty, UOM2=z2.UOM
,PrdDisp3=COALESCE(z3.PrdDisp,'X'), Den3=z3.Den, SG3=z3.SpGravt, Qty3=z3.Qty, UOM3=z3.UOM
,PrdDisp4=COALESCE(z4.PrdDisp,'X'), Den4=z4.Den, SG4=z4.SpGravt, Qty4=z4.Qty, UOM4=z4.UOM
,PrdDisp5=COALESCE(z5.PrdDisp,'X'), Den5=z5.Den, SG5=z5.SpGravt, Qty5=z5.Qty, UOM5=z5.UOM
,PrdDisp6=COALESCE(z6.PrdDisp,'X'), Den6=z6.Den, SG6=z6.SpGravt, Qty6=z6.Qty, UOM6=z6.UOM
FROM @LdTkd l
OUTER APPLY (SELECT COALESCE(p.Code + ' - ' + p.Name,'S') as PrdDisp, p.Den, p.SpGravt, b.Qty, b.UOM FROM BltzComp b LEFT JOIN Prd p ON p.ID = COALESCE(b.PrdID,l.PrdID) WHERE OrdNo = l.OrdNo AND Position = 1) z1
OUTER APPLY (SELECT COALESCE(p.Code + ' - ' + p.Name,'S') as PrdDisp, p.Den, p.SpGravt, b.Qty, b.UOM FROM BltzComp b LEFT JOIN Prd p ON p.ID = COALESCE(b.PrdID,l.PrdID) WHERE OrdNo = l.OrdNo AND Position = 2) z2
OUTER APPLY (SELECT COALESCE(p.Code + ' - ' + p.Name,'S') as PrdDisp, p.Den, p.SpGravt, b.Qty, b.UOM FROM BltzComp b LEFT JOIN Prd p ON p.ID = COALESCE(b.PrdID,l.PrdID) WHERE OrdNo = l.OrdNo AND Position = 3) z3
OUTER APPLY (SELECT COALESCE(p.Code + ' - ' + p.Name,'S') as PrdDisp, p.Den, p.SpGravt, b.Qty, b.UOM FROM BltzComp b LEFT JOIN Prd p ON p.ID = COALESCE(b.PrdID,l.PrdID) WHERE OrdNo = l.OrdNo AND Position = 4) z4
OUTER APPLY (SELECT COALESCE(p.Code + ' - ' + p.Name,'S') as PrdDisp, p.Den, p.SpGravt, b.Qty, b.UOM FROM BltzComp b LEFT JOIN Prd p ON p.ID = COALESCE(b.PrdID,l.PrdID) WHERE OrdNo = l.OrdNo AND Position = 5) z5
OUTER APPLY (SELECT COALESCE(p.Code + ' - ' + p.Name,'S') as PrdDisp, p.Den, p.SpGravt, b.Qty, b.UOM FROM BltzComp b LEFT JOIN Prd p ON p.ID = COALESCE(b.PrdID,l.PrdID) WHERE OrdNo = l.OrdNo AND Position = 6) z6
WHERE BltzTyp in ('S','B')
SELECT * FROM @LdTkd
SET NOCOUNT OFF;
RETURN;
END;
GO -
downloading the plan from the link does have all the info as far as I can see, including predicates.
and from what I can see it is only the view that is the problem - after that the update of the table variable is quite fast.
but the view... 22 table scans. space for improvement - and query probably can be partially rewritten to deal with the multiple accesses to the same table.
and those function calls ...
-
Thank you Frederico.
Completely agreed with that view will be the issue as joining multiple tables and using some function which slows down the process.
But didn't not much fully understood as someone written the query and procedure who is no longer, i am new and whenever they need they have modified.
Instead of rewriting the query and i am not much expert so i have tried tuning level to clean up fragmented indexes, updating stats, adding new indexes based on query, also added covering index but didn't quite help.
Couldn't get it when you say "Multiple accesses to the same table"?
Could you please pointed out?
- frederico_fonseca wrote:
downloading the plan from the link does have all the info as far as I can see, including predicates.
That would explain it... I didn't download the plan.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply