topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Sat, 08 Mar 2008 20:22:31 GMT

Hi all,

I wrote logic like this.But it getting performance problem.

Can anybody tell me how to avoid this.

SELECT * INTO TABLE wknvv

FROM knvv

WHERE vkorg IN sales_or

AND vtweg IN dist_cha

AND spart IN div

AND vkbur IN sales_of .

LOOP AT wknvv .

SELECT vbeln auart zzmax_cpi INTO (wlist-vbeln,wlist-auart,max)

FROM vbak

WHERE

vbtyp = 'G'

AND kunnr = wknvv-kunnr

AND auart IN contr_ty.

max1 = max.

IF sy-subrc = 0 AND max1 IS NOT INITIAL .

SELECT * INTO wvbap FROM vbap

WHERE vbeln = wlist-vbeln

AND abgru = ''.

IF sy-subrc = 0.

SELECT SINGLE * FROM veda INTO wveda

WHERE vbeln = wlist-vbeln

AND vposn = wvbap-posnr

AND vkuegru = ''

AND venddat IN dates .

IF sy-subrc = 0.

MOVE-CORRESPONDING wvbap TO wlist .

MOVE-CORRESPONDING wveda TO wlist .

MOVE-CORRESPONDING wknvv TO wlist .

APPEND wlist.

ENDIF .

ENDIF.

ENDSELECT.

CLEAR wlist.

ENDIF.

ENDSELECT .

ENDLOOP.

regards,

rakesh.

Re: select query

Former Member — Sun, 09 Mar 2008 23:45:53 GMT

Instead of using SELECT SINGLE and SELECT .. ENDSELECT for each record from table wknvv use SELECT FOR ALL ENTRIES. For example, this:


SELECT vbeln auart zzmax_cpi INTO (wlist-vbeln,wlist-auart,max)
FROM vbak
WHERE
vbtyp = 'G'
AND kunnr = wknvv-kunnr
AND auart IN contr_ty.

replace with this:


" this code should be after the first select
" but outside the loop
IF wknvv[] IS NOT INITIAL.
  SELECT vbeln auart zzmax_cpi 
    INTO TABLE lit_wlist "table lit_wlist defined earlier somewhere in the code
    FROM vbak
    FOR ALL ENTRIES IN wknvv
    WHERE vbtyp = 'G'
      AND kunnr = wknvv-kunnr
      AND auart IN contr_ty.
ENDIF.

" then in the loop use READ TABLE
READ TABLE lit_wlist 
  INTO lwa_wlist
  WITH KEY kunnr = wknvv-kunnr.

" or LOOP
LOOP AT lit_wlist 
  INTO lwa_wlist
  WHERE kunnr = wknvv-kunnr.
ENDLOOP.

This way you will read each record from the memory, not from the harddisk. Also apply this to other SELECTs.

You can define internal tables with keys so that reading and looping would be even faster.

Also, check if there is any index on VBAK that you could use.

Hope this helped you.

Rgds

Mat

Re: select query

Former Member — Mon, 10 Mar 2008 05:37:30 GMT

Hi Rakesh,

The main thing which would have affected the performance of your coding is-

1. Use of all fields in select statements instead of using specific fields.You should specify all the fields which have to retrieved from the table KNVV if you know them.For example you may use the codes as follows.This is only the example-

SELECT kunnr vkorg INTO TABLE wknvv

FROM knvv

WHERE vkorg IN sales_or

AND vtweg IN dist_cha

AND spart IN div

AND vkbur IN sales_of .

The generalised way is as follows-

*SELECT field1 field2 field3 .... INTO TABLE itab FROM dbtab

WHERE condition.*

2. The second thing which is affecting the performance most is the use of select statements within the LOOP.Thst reduces the performance very much.Because for each loop the data is fetched from the database table and it takes time.

So better to use select quieries with the addition FOR ALL ENTIRIES.For example-

*SELECT field1 field2 field3 .... INTO TABLE itab1

FROM dbtab1

WHERE condition.*

*SELECT field11 field 12 field13.....INTO TABLE itab2

FROM dbtab2

FOR ALL ENTRIES IN itab1

WHERE field of dbtab = itab1-field1.*

The use of above codes will definately improve the performance and always try to avoid the SELECT quieries within the LOOP.

Ok Take Care and I expect solution given by me may remove your performance problem.

BYE.

Re: select query

Former Member — Mon, 10 Mar 2008 05:52:14 GMT

hi,

The main performance issue is that u have looped the database table.... transfer the data that u want into the internal table and then use loop the internal table with another internal table.

Eg.

select * from vbak into itab1.

select * from vbap into itab2.

Loop at itab1.

read table itab2 with key f1 = itab1-f1.

endloop.

Thanks,

Reward if Useful.

Arunprasad.P

Re: select query

Former Member — Mon, 10 Mar 2008 14:04:00 GMT

The other answers are generally helpful, but don't solve your main problem. You are doing two SELECTS without using an index.

The first against KNVV uses all primary key fileds except KUNNR which is the first key field, so the index won't be used.

The second against VBAK doesn't try to use an index and is within a loop.

So, run it in th ebackground.

Rob