performance issue in this query

[Former Member]

hi all.

i am getting the performnace issue in the query for vbrk,vbpa and knc1.because the key field are not used for the linking tables.infact not getting any ouput from the fetch of knc1.

kindly reply with suggestio

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.

endif.

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

if not it_kna1[] is initial.

 select vbeln
        kunrg
        fkdat
        kkber
        from vbrk
        into table it_vbrk
        for all entries in it_kna1
        where kunrg = it_kna1-kunnr.

if sy-subrc = 0.

sort it_vbrk descending by fkdat vbeln.
 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 kunnr = it_vbrk-kunrg
        and parvw = 'VE'.

 if sy-subrc = 0.
 sort it_vbpa by vbeln.
 endif.
 endif.

 if sy-subrc = 0.

 select kunnr
        kkber
        sbgrp
        from knkk
        into table it_knkk
        for all entries in it_kna1
        where kunnr = it_kna1-kunnr.

 if sy-subrc = 0.
 sort it_knkk by kunnr.
 endif.
 endif.

 if not it_knkk[] is initial.

 select kunnr
        zterm
        from knvv
        into table it_knvv
        for all entries in it_knkk
        where kunnr = it_knkk-kunnr.

if sy-subrc = 0.
sort it_knvv by kunnr.

endif.
endif.

CLEAR: v_year.

read table it_vbrk index 1.

write it_vbrk-fkdat to v_year dd/mm/yyyy.

v_year = v_year+6(4).

if sy-subrc = 0.

select kunnr
       bukrs
       gjahr
       UM01U
       UM02U
       UM03U
       UM04U
       UM05U
       UM06U
       UM07U
       UM08U
       UM09U
       UM10U
       UM11U
       UM12U
       from knc1
       into table it_knc1
*       for all entries in it_vbrk
*       where kunnr = it_vbrk-kunrg
       for all entries in it_knb1
       where bukrs = it_knb1-bukrs
       and   gjahr = v_year.

if sy-subrc = 0.
sort it_knc1 descending  by gjahr.
endif.

 endif.

Edited by: Julius Bussche on Jan 12, 2009 11:54 AM

[Former Member]

hi all.

i am getting the performnace issue in the query for vbrk,vbpa and knc1.because the key field are not used for the linking tables.infact not getting any ouput from the fetch of knc1.

kindly reply with suggestio

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.

endif.

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

if not it_kna1[] is initial.

 select vbeln
        kunrg
        fkdat
        kkber
        from vbrk
        into table it_vbrk
        for all entries in it_kna1
        where kunrg = it_kna1-kunnr.

if sy-subrc = 0.

sort it_vbrk descending by fkdat vbeln.
 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 kunnr = it_vbrk-kunrg
        and parvw = 'VE'.

 if sy-subrc = 0.
 sort it_vbpa by vbeln.
 endif.
 endif.

 if sy-subrc = 0.

 select kunnr
        kkber
        sbgrp
        from knkk
        into table it_knkk
        for all entries in it_kna1
        where kunnr = it_kna1-kunnr.

 if sy-subrc = 0.
 sort it_knkk by kunnr.
 endif.
 endif.

 if not it_knkk[] is initial.

 select kunnr
        zterm
        from knvv
        into table it_knvv
        for all entries in it_knkk
        where kunnr = it_knkk-kunnr.

if sy-subrc = 0.
sort it_knvv by kunnr.

endif.
endif.

CLEAR: v_year.

read table it_vbrk index 1.

write it_vbrk-fkdat to v_year dd/mm/yyyy.

v_year = v_year+6(4).

if sy-subrc = 0.

select kunnr
       bukrs
       gjahr
       UM01U
       UM02U
       UM03U
       UM04U
       UM05U
       UM06U
       UM07U
       UM08U
       UM09U
       UM10U
       UM11U
       UM12U
       from knc1
       into table it_knc1
*       for all entries in it_vbrk
*       where kunnr = it_vbrk-kunrg
       for all entries in it_knb1
       where bukrs = it_knb1-bukrs
       and   gjahr = v_year.

if sy-subrc = 0.
sort it_knc1 descending  by gjahr.
endif.

 endif.

Edited by: Julius Bussche on Jan 12, 2009 11:54 AM

[Former Member]

In the first select statement you have used into corresponding statement ...this is a problem here i guess.

Insted you can used

select kunnr

bukrs

from knb1

into table it_knb1

where bukrs IN s_ccode.

Provided it_knb1 has a structure of only kunnr and bukrs fields.

[Former Member]

hi,

put a break-point in each select query and check which select query is taking too much time..

and let us know, so that we can say what we need to change..

regards

vijay

[Former Member]

Hi,

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

1. Here you are not checking whether IT_KNB1 is filled with some records or not. SO it wil not check for

the KUNNR value.

2. if sy-subrc = 0.

sort it_kna1 by kunnr.

Better to use delete adjacent duplicates.

if you want all the records, the copy the contents into a local internal table and then delete

the adjacent duplicates and use this in your select query.

endif.

Regards,

Venkatesh

[dev_parbutteea]

Hi,

after first select, create a temp internal table with same structure as it_knb1 .

i_tmp [ ] = it_knb1 [ ] .

sort i_tmp by kunnr.

delete adjacent duplicates from i_tmp comparing kunnr.

select kunnr

name1

land1

regio

ktokd

from kna1

into table it_kna1

for all entries in i_tmp

where kunnr = i_tmp -kunnr

and ktokd = '0003'.

For select from KNVV, use iternal table IT_KNA1 instead

select kunnr

zterm

from knvv

into table it_knvv

for all entries in it_kna1

where kunnr = it_kna1-kunnr.

This will speedup your retrieval.

Regards,

Dev.

[former_member404244]

Hi,

In the first select query , avoid statement into corresponding and before the second select query u have to use

if it_knb1[] is not 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'.

endif.

aslo in the below query , u have to change the partner type.instead of VE u have to give WE...Plz check

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 kunnr = it_vbrk-kunrg

and parvw = 'WE'.

if sy-subrc = 0.

sort it_vbpa by vbeln.

endif.

endif.

Regards,

Nagaraj

[Former Member]

Hi Tanisha,

I would have written a JOIN query in this case.

Do let me know if i shud be putting efforts on the queries to JOIN them together.