topic Re: select stmt taking long time. in Application Development and Automation Discussions

select stmt taking long time.

Former Member — Thu, 18 May 2006 18:18:30 GMT

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 kna1_{kunnr = VBAK}KUNNR

INTO TABLE ITAB_VBAK

WHERE VBAK~ERDAT IN SERDAT

AND VBAK~AUART IN SAUART.

Thanks

Veni.

Re: select stmt taking long time.

LucianoBentiveg — Thu, 18 May 2006 18:22:27 GMT

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.

Re: select stmt taking long time.

RichHeilman — Thu, 18 May 2006 18:24:57 GMT

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

Re: select stmt taking long time.

Former Member — Thu, 18 May 2006 18:25:43 GMT

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

Re: select stmt taking long time.

Former Member — Thu, 18 May 2006 18:37:11 GMT

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

Re: select stmt taking long time.

Former Member — Thu, 18 May 2006 19:18:50 GMT

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