performance improvement of two select queries

[former_member329435]

These two select statement inside the loop this causes performance issues.Could you please let me know what shold be the logic for these two select statement outside the loop.

IF gw_bkpf-awkey NE gw_awkey OR gw_awkey = space.
REFRESH: gt_ce1z001_1.
CLEAR gw_awkey.
gw_awkey = gw_bkpf-awkey.

SELECT vrgar
belnr
posnr
gjahr
wadat
fadat
kndnr
kaufn
kdpos
bukrs
kstar
rbeln
rposn
kunwe
auart
absmg
vvprd
vvrmp
vvrmu
vvcon
vvpur FROM ce1z001 INTO TABLE gt_ce1z001_1
WHERE vrgar IN r_vrgar
* AND paobjnr = gt_bseg-paobjnr
AND rbeln = gw_bkpf-belnr
AND gjahr = gw_bkpf-gjahr
AND bukrs = gw_bkpf-bukrs
AND copa_awtyp = gw_bkpf-awtyp
AND kokrs = 'SH01'.

IF sy-subrc NE 0.
SELECT vrgar
belnr
posnr
gjahr
wadat
fadat
kndnr
kaufn
kdpos
bukrs
kstar
rbeln
rposn
kunwe "Srini
auart
absmg
vvprd
vvrmp
vvrmu
vvcon
vvpur FROM ce1z001 INTO TABLE gt_ce1z001_1
WHERE vrgar IN r_vrgar
* AND paobjnr = gt_bseg-paobjnr
AND rbeln = gw_bseg-kidno
AND gjahr = gw_bkpf-gjahr
AND bukrs = gw_bkpf-bukrs
AND copa_awtyp = gw_bkpf-awtyp
AND kokrs = 'SH01'.
ENDIF.
SORT gt_ce1z001_1.
DELETE ADJACENT DUPLICATES FROM gt_ce1z001_1 COMPARING ALL FIELDS.

[sapna_jgurudutt]

Hi,

Are you using Design Studio or Lumira Designer with regard to this issue?

Can you clarify regarding this for further analysis.

Thanks & Regards,

Sapna

[ShivaKona]

Hi Fayyaz,

You can use this query outside your loop

SELECT vrgar
belnr
posnr
gjahr
wadat
fadat
kndnr
kaufn
kdpos
bukrs
kstar
rbeln
rposn
kunwe
auart
absmg
vvprd
vvrmp
vvrmu
vvcon
vvpur
FROM ce1z001
INTO TABLE gt_ce1z001_1
FOR ALL ENTRIES IN gt_bkpf( or internal table on which you are looping )
WHERE vrgar IN r_vrgar
* AND paobjnr = gt_bseg-paobjnr
AND ( rbeln = gw_bkpf-belnr OR rbeln = gw_bseg-kidno )
AND gjahr = gw_bkpf-gjahr
AND bukrs = gw_bkpf-bukrs
AND copa_awtyp = gw_bkpf-awtyp
AND kokrs = 'SH01'.

and then perform READ TABLE on this result set inside your loop where you want to use.

Regards,

Shiva K

[matt]

Your where clause should compare with gt_bkpf not gw_bkpf.

[ShivaKona]

Thanks, my intention was to use for all entries outside and read table inside..so have not concentrated on that more:)

[matt]

OpenSQL has been part of ABAP from way before 7.40.

The only difference I can see between your SQL and the original, is that you've overly complicated it with the unnecessary ce1z001~ and you've used APPENDING... TABLE instead of INTO TABLE.

[DoanManhQuynh]

below is my suggestion:

DELETE ADJACENT DUPLICATES FROM gt_bkpf COMPARING awkey.

SORT gt_bkpf.

CHECK gt_bkpf IS NOT INITIAL.



SELECT vrgar

 belnr

 posnr

 gjahr

 wadat

 fadat

 kndnr

 kaufn

 kdpos

 bukrs

 kstar

 rbeln

 rposn

 kunwe 

 auart

 absmg

 vvprd

 vvrmp

 vvrmu

 vvcon

 vvpur 

 FROM ce1z001 INTO TABLE gt_ce1z001_1

 FOR ALL ENTRIES IN gt_bkpf

WHERE vrgar IN r_vrgar

* AND paobjnr = gt_bseg-paobjnr

AND ( rbeln = gt_bkpf-belnr

 OR rbeln = gt_bkpf-kidno )

AND gjahr = gt_bkpf-gjahr

AND bukrs = gt_bkpf-bukrs

AND copa_awtyp = gt_bkpf-awtyp

AND kokrs = 'SH01'.

 

SORT gt_ce1z001_1.

DELETE ADJACENT DUPLICATES FROM gt_ce1z001_1 COMPARING ALL FIELDS.

[matt]

If you specified all the fields that make up the primary key of CE1Z000, you should use ORDER BY PRIMARY KEY in the select, instead of the SORT system - it should be faster.

From the documentation:

• In a SELECT statement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY and can only be used to access a single table or view. In this case, all columns of the primary key (except for the client column in client-specific tables) must be in the SELECT list.

[DoanManhQuynh]

i didnt pay attention in that part, just change his original code :). btw, i want to ask should it be SELECT DISTINCT instead of select and delete duplicate?

[ShivaKona]

SELECT DISTINCT will take more time than select and delete duplicate

[former_member329435]

Thanks for your answers.

i wroth the logic as below for two select statemenets

IF gt_bkpf IS NOT INITIAL.
SELECT vrgar
belnr
posnr
gjahr
wadat
fadat
kndnr
kaufn
kdpos
bukrs
kokrs
kstar
rbeln
rposn
copa_awtyp
kunwe
auart
absmg
vvprd
vvrmp
vvrmu
vvcon
vvpur FROM ce1z001
INTO TABLE gt_ce1z001_1
FOR ALL ENTRIES IN gt_bkpf
WHERE vrgar IN r_vrgar
AND rbeln = gt_bkpf-belnr
AND gjahr = gt_bkpf-gjahr
AND bukrs = gt_bkpf-bukrs
AND copa_awtyp = gt_bkpf-awtyp
AND kokrs = 'SH01'.

IF sy-subrc NE 0.
LOOP AT gt_bseg INTO gw_bseg.
gw_kidno-sign = 'I'.
gw_kidno-option = 'EQ'.
gw_kidno-low = gw_bseg-kidno.
APPEND gw_kidno TO r_kidno.
CLEAR gw_kidno.
ENDLOOP.

SELECT vrgar
belnr
posnr
gjahr
wadat
fadat
kndnr
kaufn
kdpos
bukrs
kokrs
kstar
rbeln
rposn
copa_awtyp
kunwe
auart
absmg
vvprd
vvrmp
vvrmu
vvcon
vvpur FROM ce1z001
INTO TABLE gt_ce1z001_1
FOR ALL ENTRIES IN gt_bkpf
WHERE vrgar IN r_vrgar
AND rbeln IN r_kidno
AND gjahr = gt_bkpf-gjahr
AND bukrs = gt_bkpf-bukrs
AND copa_awtyp = gt_bkpf-awtyp
AND kokrs = 'SH01'.

ENDIF.
SORT gt_ce1z001_1.
DELETE ADJACENT DUPLICATES FROM gt_ce1z001_1 COMPARING ALL FIELDS.
ENDIF.