Need Help Tuning Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help Tuning Query

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-10-20 : 12:51:47

In the following query, the execution plan shows a large hit (43%) on this join:

left outer join CRS.CustNo_B_CIS_SAP_V Cust on
	K2.KUNNR = Cust.SAP_KUNNR and
	KNVV.SPART = Cust.SAP_SPART and
	KNVV.VTWEG = Cust.SAP_VTWEG and
	KNVV.VKORG = Cust.SAP_VKORG

Any ideas to improve this would be appreciated!

select 
K3.KTOKD + ' --->' as 'BIN', 
ZLOC.LMS_LOC_NBR as 'LMS Loc', 
K3.KUNNR, 
K3.NAME1, 
 case when K3.KTOKD = 'ZLOC' then K3.ZZLOCTYPE + ' - BIN'
	else ZCMMDPLANT.ZFLUSLOCTP end as LOCTYPE,
KNA1.KTOKD + ' --->' as 'Route', 
KNA1.KUNNR as 'SAP Route', 
KNA1.KUNNR as 'Transp. Route', 
RIGHT(KNA1.KUNNR, LEN(KNA1.KUNNR)-1) as 'Leg Route',
KOTG932.ZZRTE_TYPE + ' - ' + RTE.ZZDESC_RTE as 'Route Type',
K2.KTOKD + '--->' as 'Sold To', 
Cust.CIS_CUST_NMBR, 
K2.KUNNR, 
K2.NAME1, 
isnull(KNVH2.HITYP,'') as 'Chain',
isnull(ZE.PARVW,'') as 'EDI',
K4.KTOKD + '--->' as 'RSR', 
Z4.KUNN2, 
K4.NAME1, KNVV.VKORG + '/' + KNVV.VTWEG + '/' + KNVV.SPART as 'Div_SalesChannel',
KOTG940.MATNR as 'AuthorizedBinMaterial',
MAKT.MAKTX
from U11.KNA1					/*Route*/
left outer join U11.KNVV RteKNVV on /* Route Sales Org */
	KNA1.KUNNR = RteKNVV.KUNNR
left outer join U11.KNVH on		/* Route to Hierarchy */
	KNA1.KUNNR = KNVH.HKUNNR 
left outer join U11.KNVV KNVV on /* ZSLD Sales Org */
	KNVH.KUNNR = KNVV.KUNNR and
	RteKNVV.VKORG = KNVV.VKORG and
	RteKNVV.VTWEG = KNVV.VTWEG and
	RteKNVV.SPART = KNVV.SPART 
left outer join U11.KNA1 K2 on	/* Hierarchy KUNN2 to ZSLD*/
	KNVV.KUNNR = K2.KUNNR 
left outer join U11.KNVH KNVH2 on /*ZSLD Chain Determination */
	K2.KUNNR = KNVH2.KUNNR 
	and KNVH2.HITYP = 'C' 
left outer join U11.KNVP ZPAY on /* ZSLD to ZPAY */
	K2.KUNNR = ZPAY.KUNNR and
	KNVV.VKORG = ZPAY.VKORG and
	KNVV.VTWEG = ZPAY.VTWEG and
	ZPAY.PARVW = 'RG'
left outer join U11.KNVP ZE on /* ZPAY to EDI */
	ZPAY.KUNN2 = ZE.KUNNR and	
	KNVV.VKORG = ZE.VKORG and
	KNVV.VTWEG = ZE.VTWEG and
	ZE.PARVW = 'ZE'
left outer join U11.KOTG932 on	/* Route to Route Type attribue */
	KNA1.KUNNR = KOTG932.KUNNR
left outer join U11.ZVRTE_TYPE_T RTE on /* Route Type Description */
	KOTG932.ZZRTE_TYPE = RTE.ZZRTE_TYPE
left outer join U11.KOTG910 Z2 on /* Route to BIN */
	KNA1.KUNNR = Z2.KUNNR
	and Z2.PARVW = 'Z2'
left outer join U11.KOTG910 Z4 on /* Route to Driver */
	KNA1.KUNNR = Z4.KUNNR
	and Z4.PARVW = 'Z4'
Left outer join U11.KNA1 K4 on /* Driver Name */
	Z4.KUNN2 = K4.KUNNR	
left outer join U11.KNA1 K3 on /* BIN Name */
	Z2.KUNN2 = K3.KUNNR
left outer join U11.ZCMMDPLANT on /* SAP Plant Attributes */
	right(K3.KUNNR,4) = ZCMMDPLANT.WERKS
left outer join crs.LocToPlant_B_LMS_SAP_V LOC on 
	right(K3.KUNNR,4) = LOC.SAP_WERKS
left outer join CRS.CustNo_B_CIS_SAP_V Cust on
	K2.KUNNR = Cust.SAP_KUNNR and
	KNVV.SPART = Cust.SAP_SPART and
	KNVV.VTWEG = Cust.SAP_VTWEG and
	KNVV.VKORG = Cust.SAP_VKORG
left outer join CRS.CustNo_I_SAP_LMS_V ZLOC on
	K3.KUNNR = ZLOC.SAP_KUNNR
left outer join U11.KOTG940 on	/* Route to Material Authorization */
	K3.KUNNR = KOTG940.ZZBIN 
inner join U11.MAKT on /* Material Description Table */
	KOTG940.MATNR = MAKT.MATNR
where 
/* Filter for Route */
KNA1.KUNNR in ('R92557') and
/* Filter for ZSLD */
K2.KTOKD = 'ZSLD'
order by K3.KUNNR, 'SAP Route', K2.KUNNR, AuthorizedBinMaterial

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 13:10:29

i see lots of left outer joins. Is it really required?
Also I see K2 involved in left join and then you're using its column in filter so effectively it reduces to inner join. Is this intentional?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-10-21 : 04:06:45

I wouldn't be too worried about some of the % values. What indices support the joins would be more important.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-21 : 08:29:44

is CRS.CustNo_B_CIS_SAP_V a view? if so, need to have a look at the query plan for the view since the join to it is costing the most according to your initial statement.

also, this sort of a join can't use indexes: on right(K3.KUNNR,4) = LOC.SAP_WERKS

it's like asking for all the entries in the phone book where the last 4 chars in the last name are 'mith'. You have to scan the whole book to do that, because Aaasmith, Smith, and Zzzmith all satify.

is U11.KNA1 a large table? because you'll be scanning it more than once due to the joins on it that are forcing scans. that is unless you add a computed column to KNA1 that is right(KUNNR,4) and then further index that computed column, and finally replace the join predicate with K3.ComputedColumnName = LOC.SAP_WERKS

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources