Monday, December 3, 2007

Performance tuning using GROUPBY

************************************************************************
* Performance tuning using GROUPBY
*
* Extracts from program ZFAL2002
************************************************************************


************************************************************************
* START-OF-SELECTION

SELECT KALNR KALKA BWVAR MEEHT SUM( MENGE )
INTO TABLE I_CKIS FROM CKIS
WHERE KADKY <= SY-DATUM
AND TVERS = '01'
AND KALKA IN ('01','Z1','Z2')
AND BWVAR IN ('Z01','Z02','Z03','Z04','Z07')
* and kkzst = ' '
AND KKZMA = ' '
AND TYPPS = 'E'
AND ( KSTAR <> 800040 OR KSTAR <> 800050 )
AND ( MEEHT = 'MIN' OR MEEHT = 'H' )
GROUP BY KALNR KALKA BWVAR MEEHT.

* Perform actual processing
Perform get_associated_labour_hours.


*----------------------------------------------------------------------*
FORM GET_ASSOCIATED_LABOUR_HOURS.
* Determine start position and then process single record for given key
* i_ckis is sorted on kalnr kalka bwvar meeht with menge summated.
READ TABLE I_CKIS WITH KEY KALNR = W_KEKO-KALNR
KALKA = W_KEKO-KALKA
BWVAR = W_KEKO-BWVAR
MEEHT = 'H' BINARY SEARCH.
IF SY-SUBRC = 0.
D_LAB_HRS = I_CKIS-MENGE * 60.
ENDIF.

READ TABLE I_CKIS WITH KEY KALNR = W_KEKO-KALNR
KALKA = W_KEKO-KALKA
BWVAR = W_KEKO-BWVAR
MEEHT = 'MINS' BINARY SEARCH.
IF SY-SUBRC = 0.
D_LAB_HRS = D_LAB_HRS + I_CKIS-MENGE.
ENDIF.

D_LAB_HRS = D_LAB_HRS / W_KEKO-LOSGR.
D_LAB_HRS = D_LAB_HRS / 60. "Convert from mins into hours
ENDFORM. " GET_LABOUR_HOURS_ASSOCIATED

No comments:

Blog Archive