topic performance issue with the code in Application Development and Automation Discussions

performance issue with the code

Former Member — Wed, 19 Aug 2009 13:49:46 GMT

hi,

i have below code.for printing it is taking enough time..

which way i can improve the performance of below piece of code and what changes i will do for improving performance??

kindly help me..

form get_komgd.

tables: kotd994. "kondd

data : tfill_auswahl type i.

data: begin of auswahl occurs 10,

kappl like kotd994-kappl ,

kschl like kotd994-kschl ,

vkorg like kotd994-vkorg ,

vtweg like kotd994-vtweg ,

spart like kotd994-spart ,

kvgr1 like kotd994-kvgr1 ,

matwa like kotd994-matwa ,

datbi like kotd994-datbi ,

datab like kotd994-datab ,

knumh like kotd994-knumh ,

smatn like kondd-smatn,

meins like kondd-meins,

sugrd like kondd-sugrd,

end of auswahl.

tables: kondd.

select * from kondd

where smatn = ltap-matnr.

select * from kotd994

where kappl = 'V'

and kschl = vbak-kschl

and vkorg = vbak-vkorg

and vtweg = vbak-vtweg

and spart = vbak-spart

and kvgr1 = vbak-kvgr1

and matwa = vbak-matwa

and datbi >= sy-datum

and datab <= sy-datum

and knumh = kondd-knumh .

endselect.

if sy-subrc = 0.

and kotd994-kvgr1(1) = 'Z'.

move-corresponding kotd994 to auswahl.

move-corresponding kondd to auswahl.

append auswahl .

endif. " sy-subrc = 0.

write: / auswahl.

endselect.

describe table auswahl lines tfill_auswahl.

if tfill_auswahl = 1.

komgd-matwa = auswahl-matwa.

else.

clear komgd-matwa.

endif. " tfill_auswahl = 1.

endform. " ZHX_GET_COSTUMER_NR

Re: performance issue with the code

Former Member — Wed, 19 Aug 2009 13:59:58 GMT


select * 
           from kondd
           where smatn = ltap-matnr.
 
    select *
              from kotd994
              where kappl = 'V'
* and kschl = vbak-kschl
              and vkorg = vbak-vkorg
              and vtweg = vbak-vtweg
* and spart = vbak-spart
              and kvgr1 = vbak-kvgr1
* and matwa = vbak-matwa
              and datbi >= sy-datum
              and datab <= sy-datum
              and knumh = kondd-knumh .
   endselect.
  if sy-subrc = 0.
* and kotd994-kvgr1(1) = 'Z'.
     move-corresponding kotd994 to auswahl.
     move-corresponding kondd to auswahl.
     append auswahl .
   endif. " sy-subrc = 0.

* write: / auswahl.
endselect.

That is a typical nested loop, you should change it into a JOIN or use at least a FOR ALL ENTRIES.

Please change also the header line coding, it is hard to understand what is done.

Siegfried

Re: performance issue with the code

Former Member — Wed, 19 Aug 2009 14:04:08 GMT

hi,

u r absolutely right ..can u analysis and tell me what should i right??

please tell me the what changes i will do.?

how to break and use for all entries which u mentioned??

if u provide details, then may be i can really impliment the logic.

awaiting for ur reply..

Re: performance issue with the code

Former Member — Wed, 19 Aug 2009 14:59:10 GMT

hi,

can somebody reply me..

Re: performance issue with the code

Former Member — Wed, 19 Aug 2009 15:49:39 GMT

Hi,

Two important point to keep in mind before you write any select statement in ABAP:

1. Keep the database access less

2. Keep the amount of data transfer small

Well, to go with what has been suggested, you can make a local buffer of the kondd table and then you can run the 2nd select statement based on all the entries of the first table. But you need to look into the tables and need to decide which will be the best approach in this case. There are plenty of example available as to how to write for all entries. Also, you need to use the field symbols to speed up the processing of the statements.

Also, use a proper header line as this will make the code much more cleaner and easy to understand.

Hope this helps.

Thanks,

Samantak.

Re: performance issue with the code

Former Member — Wed, 19 Aug 2009 19:16:58 GMT


select  .... speifiy need fields
           INTO corresponding fields of table auswahl.
           from kondd as a
           INNER JOIN  kotd994 as b
           ON b~knumh = a~knumh .
           where a~smatn = ltap-matnr.
           AND   b~kappl = 'V'
* and kschl = vbak-kschl
              and vkorg = vbak-vkorg
              and vtweg = vbak-vtweg
* and spart = vbak-spart
              and kvgr1 = vbak-kvgr1
* and matwa = vbak-matwa
              and datbi >= sy-datum
              and datab <= sy-datum.

Check documentation

add b~ and a~ in WHERE and in the fields you need

Re: performance issue with the code

Former Member — Wed, 19 Aug 2009 20:20:22 GMT

Hi Lata,

Try replacing the 2 select statements with the following join.

SELECT  c~kappl
        c~kschl
        c~vkorg
        c~vtweg
        c~spart
        c~kvgr1
        c~matwa
        c~datbi
        c~datab
        c~knumh
        b~smatn
        b~meins
        b~sugrd
  FROM        vbap   AS a
  INNER JOIN kondd   AS b
  ON a~knumh EQ b~knumh
  INNER JOIN kotd994 AS c
  ON c~knumh EQ b~knumh
  INTO TABLE auswahl
  WHERE a~vbeln EQ vbak-vbeln
  AND   a~matnr EQ ltap-matnr
  AND   c~kappl EQ 'V'
  AND   c~vkorg EQ vbak-vkorg
  AND   c~vtweg EQ vbak-vtweg
  AND   c~kvgr1 EQ vbak-kvgr1
  AND   c~datbi GE sy-datum
  AND   c~datab LE sy-datum.

Re: performance issue with the code

Former Member — Thu, 20 Aug 2009 10:58:31 GMT


 vbak-vbeln
  AND   a~matnr EQ ltap-matnr
  AND   c~kappl EQ 'V'
  AND   c~vkorg EQ vbak-vkorg
  AND   c~vtweg EQ vbak-vtweg
  AND   c~kvgr1 EQ vbak-kvgr1

I did not plan to invest much time in this thread, but you should post the more complete coding,

there are still two loop on itap and vbak ... open

Why don't you try all five tables into one big join! If the index relation is o.k., then it will work fine.

Siegfried

Re: performance issue with the code

Former Member — Thu, 20 Aug 2009 13:22:48 GMT

Hi Siegfried,

Lata has only provided us with the code in the subroutine, that is why I could not use VBAK in the query. I am not sure how she is quering VBAK or how the rest of her program is structured. I did not want to confuse her by making assumptions and adding hypothetical code. I merely tried to improve the code in the subroutine because that is what she has shared with us. Probably if she shares the rest of the code with us I could attempt to give a more complete solution.

However you are right. Leaving out VBAK from the join does not really make sense.

Never the less I am sure that this new join should run a lot faster than her original code given the fact that we are pretty much using primary keys in all the 3 tables.

Regards,

Mark

Re: performance issue with the code

Former Member — Fri, 21 Aug 2009 17:59:50 GMT

Hi,

Using two select statements will take more time.

Rather use this sample code:

select * from kondd in to corresponding fields of table auswahl

where smatn = ltap-matnr.

select * from kotd994 into corresponding fields of auswahl

for all entries of auswahl

where kappl = 'V'

and vkorg = vbak-vkorg

and vtweg = vbak-vtweg

and kvgr1 = vbak-kvgr1

and datbi >= sy-datum

and datab <= sy-datum

and knumh = auswahl-knumh .

however, in the structure of auswahl maintain the field of knumh as well, so that we can pass the values in the second select directly.

This will improve the performance very well.

if possible, try and use the primary key combinations in the where clause while extracting the data.

Re: performance issue with the code

Former Member — Sat, 22 Aug 2009 14:51:47 GMT

Using two select statements will take more time.

Rather use this sample code:

... what follows are 2 select statements