Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

select stmt taking long time.

Former Member
0 Kudos

Hi,

This stmt is taking long time to execute. Is there any way, we can modify it to perform well.

SELECT kna1~NAME1

vbak~ERNAM

vbak~erdat

vbak~KUNNR

vbak~VBELN

vbak~AUART

FROM VBAK

INNER JOIN kna1 on kna1kunnr = VBAKKUNNR

INTO TABLE ITAB_VBAK

WHERE VBAK~ERDAT IN SERDAT

AND VBAK~AUART IN SAUART.

Thanks

Veni.

1 ACCEPTED SOLUTION

former_member188685
Active Contributor
0 Kudos

HI veni,

change your code, and use for all entries.

data: begin of itab_vbak occurs 0,
      vbeln
       erdat
       ERNAM
       erdat
       KUNNR
       AUART
kunnr
name1
      end of itab_vbak.
data: l_tabix type sy-tabix.
SELECT vbeln
       erdat
       ERNAM
       erdat
       KUNNR
       AUART
FROM VBAK
INTO TABLE ITAB_VBAK
WHERE ERDAT IN SERDAT
AND  AUART IN SAUART.
if sy-subrc = 0.
select kunnr
       name1
      from kna1
      into table it_kna1
      for all entries in itab_vbak
     where kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
loop at itab_vbak.
l_tabix = sy-tabix.
read table it_kna1 with key kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
itab_vbak-name1 = it_kna1-name1.
modify itab_vbak index l_tabix.
endif.
endloop.
endif.
endif.

regards

vijay

5 REPLIES 5

LucianoBentiveg
Active Contributor
0 Kudos

You are using indexes:

VBAK Secondary index 'ERD': MANDT ERDAT (can use full index)

KNA1 Primary key: MANDT KUNNR (can use full primary key)

The problem now is in user selection.

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

I guess it really depends on what you put in your select options. There is an index over the ERDAT field in VBAK, so if you put something in that select-option is should run fairly fast. This code is working good for me when filling a value for S_ERDAT.


report zrich_0001 .

data: begin of ivbak occurs 0,
      vbeln type vbak-vbeln,
      auart type vbak-auart,
      erdat type vbak-erdat,
      ernam type vbak-ernam,
      kunnr type kna1-kunnr,
      name1 type kna1-name1,
      end of ivbak.

select-options: s_erdat for ivbak-erdat,
                s_auart for ivbak-auart.

select vbak~vbeln vbak~auart vbak~erdat
       vbak~ernam kna1~kunnr kna1~name1
         from vbak
             inner join kna1
               on kna1~kunnr = vbak~kunnr
                  into table ivbak
                      where vbak~erdat in s_erdat
                        and vbak~auart in s_auart.


loop at ivbak.
  write:/ ivbak-vbeln, ivbak-auart,
          ivbak-kunnr, ivbak-name1.

endloop.

Regards,

Rich Heilman

Former Member
0 Kudos

This should be OK so long as:

SERDAT is not empty.

All of the entries in SERDAT are simple (ie EQ, BT) and are (I)nclude.

Also - if it's BT, make sure the range isn't too large; otherwise, it may or may not be able to use the index, but it will be looking at so much data, that it won't matter.

Rob

Message was edited by: Rob Burbank

former_member188685
Active Contributor
0 Kudos

HI veni,

change your code, and use for all entries.

data: begin of itab_vbak occurs 0,
      vbeln
       erdat
       ERNAM
       erdat
       KUNNR
       AUART
kunnr
name1
      end of itab_vbak.
data: l_tabix type sy-tabix.
SELECT vbeln
       erdat
       ERNAM
       erdat
       KUNNR
       AUART
FROM VBAK
INTO TABLE ITAB_VBAK
WHERE ERDAT IN SERDAT
AND  AUART IN SAUART.
if sy-subrc = 0.
select kunnr
       name1
      from kna1
      into table it_kna1
      for all entries in itab_vbak
     where kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
loop at itab_vbak.
l_tabix = sy-tabix.
read table it_kna1 with key kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
itab_vbak-name1 = it_kna1-name1.
modify itab_vbak index l_tabix.
endif.
endloop.
endif.
endif.

regards

vijay

Former Member
0 Kudos

Seems like you may have customer number so table VAKPA indexes orders by customer. Other index tables are valuable, too, like VAPMA (material).