View- Stored Procedure Tuning

pdsQsql · March 20, 2020, 1:43am

Hello,
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.

ScottPletcher · March 20, 2020, 1:41pm

I don't think we can tune a query without seeing it. I'm not sure what you expect us to tell you just based on what you've posted. We need the full query text and the table DDL, including all existing indexes and constraints.

pdsQsql · March 20, 2020, 5:45pm

Thanks Scott.
I didn't post the script as it was big and stored proc and view so i thought initial my steps i should share and other guidance might help me to move forward.
I can post view and stored proc to look into?

ScottPletcher · March 20, 2020, 5:58pm

Yes. We can't do anything significant without code to look at. Don't forget all table and index definitions.

pdsQsql · March 21, 2020, 3:53am

Thank you Scott.
How about this IO Stats can help to troubleshoot?

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#BB7B422E'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WF'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WK'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 3598, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SP'. Scan count 3, logical reads 39183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SH'. Scan count 1, logical reads 5253, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Ord'. Scan count 1, logical reads 3864, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TM'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orig'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Prod'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PConfig'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Div'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PClas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PClasConfig'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Part'. Scan count 2, logical reads 971, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OI'. Scan count 3, logical reads 17979, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LS'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SHi'. Scan count 3, logical reads 29904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SHW'. Scan count 2, logical reads 12636, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SCL'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 14688 ms, elapsed time = 14968 ms.
Table '#BB7B422E'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'State'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#BB7B422E'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WK'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#BB7B422E'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 14688 ms, elapsed time = 14969 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

yosiasz · March 21, 2020, 4:08am

I think @ScottPletcher wanted you to post actual database scripts that show both the schema of the tables and the actual views and procedures?

are you connecting to a remote server database?
are you doing this query via openquery or linkedserver?

just throwing a wall of info will help very little.

pdsQsql · March 21, 2020, 3:08pm

Thanks Yosiasz.
Thanks for suggesting but this not a OpenQuery or through Linked server.
I will post the view and stored procedure.

jeffw8713 · March 21, 2020, 4:20pm

If I am reading this correctly - you have a query that runs fine on 2014 but is now taking a lot longer to execute on 2017. This is most likely related to the changes that were introduced in 2016 and you are now getting a sub-optimal execution plan.

You can try forcing the legacy cardinality:

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

You should also get the actual execution plans from both systems - and compare them to see where the differences occur. If forcing the legacy cardinality improves the performance then you really need to review the code further and fix the code.

pdsQsql · March 21, 2020, 4:24pm

Thanks Jeff.
It's not that actually problem in both servers. I have already compared the plan and both are same as same code we are using,both suggesting missing index so i have also tried to create index on 2014 but no impact.

pdsQsql · March 21, 2020, 4:34pm

Please see the following view as looks like as it's having base problem also count(*) taking little longer.
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