performance issue with the code

[Former Member]

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

[Former Member]

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

[Former Member]

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..

[Former Member]

hi,

can somebody reply me..

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[Former Member]

 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

[Former Member]

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

[Former Member]

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.

[Former Member]

Using two select statements will take more time.

Rather use this sample code:

... what follows are 2 select statements