topic Re: performance issue in selection criteria in Application Development and Automation Discussions

performance issue in selection criteria

Former Member — Tue, 24 Feb 2009 09:36:40 GMT

hi all.

the following are the lines of code in which my execution is taking lot of time and execution speed is being stucked.

please suggest if i can do anything for this regard.especially in all the select * from cdhdr and cdpos and also in select from vbrk.

thanks in adv.

SELECT kunnr

bukrs

FROM knb1

INTO CORRESPONDING FIELDS OF TABLE it_knb1

WHERE bukrs IN s_ccode.

IF sy-subrc = 0.

SORT it_knb1 DESCENDING BY kunnr.

DELETE ADJACENT DUPLICATES FROM it_knb1 COMPARING kunnr.

ENDIF.

IF NOT it_knb1[] IS INITIAL.

SELECT kunnr

name1

land1

regio

ktokd

FROM kna1

INTO TABLE it_kna1

FOR ALL ENTRIES IN it_knb1

WHERE kunnr = it_knb1-kunnr

AND ktokd = '0003'.

IF sy-subrc = 0.

SORT it_kna1 BY kunnr.

DELETE ADJACENT DUPLICATES FROM it_kna1 COMPARING kunnr.

ENDIF.

if sy-subrc = 0.

SELECT vbeln

kunrg

fkdat

kkber

bukrs

FROM vbrk

INTO TABLE it_vbrk

FOR ALL ENTRIES IN it_kna1

WHERE kunrg = it_kna1-kunnr

AND fkdat IN s_indate

AND fkdat IN s_chdate

AND bukrs IN s_ccode.

and bukrs = it_knb1-bukrs.

IF sy-subrc = 0.

SORT it_vbrk DESCENDING BY fkdat.

ENDIF.

endif.

IF NOT it_vbrk[] IS INITIAL.

SELECT vbeln

parvw

pernr

kunnr

FROM vbpa

INTO TABLE it_vbpa

FOR ALL ENTRIES IN it_vbrk

WHERE vbeln = it_vbrk-vbeln

AND parvw = 'VE'.

IF sy-subrc = 0.

SORT it_vbpa BY vbeln.

ENDIF.

IF sy-subrc = 0.

SELECT vkorg

bukrs

FROM tvko

INTO TABLE it_tvko

WHERE bukrs IN s_ccode.

ENDIF.

if sy-subrc = 0.

LOOP AT it_knb1.

itemp-kunnr = it_knb1-kunnr.

itemp-kunnr1 = it_knb1-kunnr.

APPEND itemp.

ENDLOOP.

IF NOT itemp[] IS INITIAL.

SELECT * FROM cdhdr

INTO TABLE i_cdhdr

FOR ALL ENTRIES IN itemp

WHERE objectclas = 'DEBI'

AND objectid = itemp-kunnr

AND ( tcode = 'VD02'

OR tcode = 'XD02' ).

*and udate in s_indate.

SORT i_cdhdr DESCENDING BY objectclas objectid udate utime.

ENDIF.

clear wa_cdhdr.

loop at i_cdhdr into wa_cdhdr.

wa_itab-objectclas_1 = wa_cdhdr-objectclas.

wa_itab-objectid_1 = wa_cdhdr-objectid.

wa_itab-udate_1 = wa_cdhdr-udate.

append wa_itab to itab.

endloop.

delete itab where udate_1 not in s_chdate.

IF NOT i_cdhdr[] IS INITIAL.

SELECT * FROM cdpos INTO TABLE i_cdpos

FOR ALL ENTRIES IN i_cdhdr

WHERE objectclas = i_cdhdr-objectclas

AND objectid = i_cdhdr-objectid

AND tabname = 'KNVV'

AND fname = 'ZTERM'.

ENDIF.

IF NOT itemp[] is initial.

select * from cdhdr

into table i_cdhdr2

for all entries in itemp

where objectclas = 'KLIM'

AND objectid = itemp-kunnr

AND tcode = 'FD32'.

sort i_cdhdr2 descending by objectclas objectid udate utime.

ENDIF.

clear wa_cdhdr2.

loop at i_cdhdr2 into wa_cdhdr2.

wa_itab2-objectclas_2 = wa_cdhdr2-objectclas.

wa_itab2-objectid_2 = wa_cdhdr2-objectid.

wa_itab2-udate_2 = wa_cdhdr2-udate.

append wa_itab2 to itab2.

endloop.

delete itab2 where udate_2 not in s_chdate.

IF not i_cdhdr2[] is initial.

select * from cdpos into table i_cdpos2

for all entries in i_cdhdr2

where objectclas = i_cdhdr2-objectclas

AND objectid = i_cdhdr2-objectid

AND tabname = 'KNKK'

AND fname = 'KLIMK'.

sort i_cdpos2 descending by objectclas objectid tabname fname.

endif.

Re: performance issue in selection criteria

jyothi_anagani — Tue, 24 Feb 2009 09:40:55 GMT

Hi,

Please dont use INTO CORRESPONDING FIELDS and dont use select *,select required fields otherwise it will check all the entries that will be problem for u.

Regards

Re: performance issue in selection criteria

Former Member — Tue, 24 Feb 2009 09:47:18 GMT

Hi,

Can you please look in debugger in whihc select query it is taking lot of time.

You can also run SQL Trace and chk if their are any listings in Red color, please specify that Select query. So that we can go ahead index few fields which will fix your issue.

Go to ST05-->
Click on The Activate Trace-->Execute your program-->Deactivate trace and click on Display Trace.
This will display list of SQL Querys...check which one is in Red color.

Regards,

Kittu

Re: performance issue in selection criteria

Former Member — Tue, 24 Feb 2009 09:53:11 GMT

yeah...have checked it and the performance is very low in select * on cdhdr and cdpos,also in sleect on table vbrk.

plz suggest.

Re: performance issue in selection criteria

Former Member — Tue, 24 Feb 2009 10:27:47 GMT

Hi,

For VBRK table select statement pls follow the following Code.

SELECT vbeln
       fkdat
       bukrs
       kunrg
       kkber
FROM vbrk
INTO TABLE it_vbrk
FOR ALL ENTRIES IN it_kna1
WHERE kunrg = it_kna1-kunnr.

IF sy-subrc EQ 0.
  DELETE it_vbrk WHERE fkdat NOT IN s_indate.
  DELETE it_vbrk WHERE fkdat NOT IN s_chdate.
  DELETE it_vbrk WHERE bukrs NOT IN s_ccode.
  SORT it_vbrk DESCENDING BY fkdat.
ENDIF.

And regarding CDHDR and CDPOS pls try to retrive corresponding fields what ever are necessary.

If not please let me know about selection of CDHDR and CDPOS . Like what do u require from those

tables. Please let me know if any queries regarding this.

Thanks & Regards,

Syed.

Re: performance issue in selection criteria

Former Member — Tue, 24 Feb 2009 10:41:07 GMT


SELECT vbeln kunrg fkdat kkber bukrs
             FROM vbrk
             INTO TABLE it_vbrk
             FOR ALL ENTRIES IN it_kna1
             WHERE kunrg = it_kna1-kunnr
             AND fkdat IN s_indate
             AND fkdat IN s_chdate
             AND bukrs IN s_ccode.

which index is that supposed to use???

Thomas will answer that question, but I can try use VRKPA this table connects KUNNR and VBELN.


SELECT * 
              FROM cdhdr
              INTO TABLE i_cdhdr
              FOR ALL ENTRIES IN itemp
              WHERE objectclas = 'DEBI'
              AND objectid = itemp-kunnr
              AND ( tcode = 'VD02' OR tcode = 'XD02' ).

this should work, use IN-List tcode IN ( 'VD02' , 'XD02' )

Siegfried

Re: performance issue in selection criteria

Former Member — Tue, 24 Feb 2009 10:42:31 GMT

> For VBRK table select statement pls follow the following Code.

....

NEVER !!!!

Re: performance issue in selection criteria

ThomasZloch — Tue, 24 Feb 2009 12:25:41 GMT

Without checking in all detail, it's probably possible to combine all the selects from KNB1 down to VBPA into one JOIN select, applying all selection criteria at once, also introducing table VRKPA for quick access to VBRK data via partner number, as Siegfried pointed out.

CDHDR and CDPOS are very huge tables normally, there is not much you can do, maybe replace "*" with the field list that you require for further processing.

Thomas

Re: performance issue in selection criteria

Former Member — Tue, 24 Feb 2009 12:33:01 GMT

Hi!

Create a new index for the table VBRK, using SE11 transaction, for the MANDT and KUNRG fields. That will significantly improve the performance, but will consume some extra storage capacity...

Regards

Tamá

Re: performance issue in selection criteria

ThomasZloch — Tue, 24 Feb 2009 12:44:15 GMT


> Hi!
> 
> Create a new index for the table VBRK, using SE11 transaction, for the MANDT and KUNRG fields. That will significantly improve the performance, but will consume some extra storage capacity...
> 
> Regards
> Tamás

Not necessary, please have a look at table VRKPA!

Thomas