topic Re: SQL Select performance in Application Development and Automation Discussions

SQL Select performance

Former Member — Wed, 22 Mar 2006 16:20:43 GMT

Hi everyone

I have written a program that includes the following SQL select statement:

SELECT vbeln erdat erzet auart kunnr

FROM vbak

INTO CORRESPONDING FIELDS OF TABLE itab_2

FOR ALL ENTRIES IN itab_1

WHERE kunnr = itab1-reg_no

AND spart = '20'.

The program takes quite a while to run and some basic run-time analysis suggests that this statement is causing a bottleneck that accounts for about 99% of the run time. Is there anything I can do to make this bit of code more efficient? I wondered about creating a new index for VBAK using kunnr and spart but am reluctant to go down that route due to the possilbe adverse consequences. Any help/advise would be greatly appreciated.

Kind regards

Andy

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 16:25:33 GMT

Hi Andrew,

Definitely there will be effect if you are not considering the key fields in a select where condition.The next best thing is create an index as you mentioned & use it.I dont think it will have that much of a problem.

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 16:35:09 GMT

Hi,

if you use "into corresponding fields of table",it takes a lot if time and increases database usage.

declare the fields in itab_2 also in the same order and instead of "into corresponding fields of table",use "into table itab_2".

in case there are other fields in itab_2 besides vbeln erdat erzet auart kunnr, then first declare another internal table ,say itab_3 with only vbeln erdat erzet auart kunnr fields in the same order and do the following:

SELECT vbeln erdat erzet auart kunnr

FROM vbak

INTO TABLE itab_3

FOR ALL ENTRIES IN itab_1

WHERE kunnr = itab1-reg_no

AND spart = '20'.

itab_2-vbeln = itab_3-vbeln.

itab_2-erdat = itab_3-erdat.

itab_2-erzet = itab_3-erzet.

itab_2-auart = itab_3-auart.

itab_2-kunnr = itab_3-kunnr.

append itab_2.

You can also do one more enhancement. In the where clause, mention spart first and then mention kunnr since spart comes before according to the data dictionary order.

So, the where clause will be:

FOR ALL ENTRIES IN itab_1

WHERE spart = '20'

AND kunnr = itab1-reg_no.

Hope this will be useful.

Regards,

Divya

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 16:45:19 GMT

Hi Andrew,

do not use kunnr and spart which are not key fields in the where clause..

when using FOR ALL ENTRIES ..

do the following check

if itab_1[] is not initial.

SELECT vbeln erdat erzet auart kunnr 
  FROM vbak
  INTO TABLE itab_2
  FOR ALL ENTRIES IN itab_1
  where <key field>.

LOOP AT itab_1.
  DELETE itab_2 where  kunnr NE itab1-reg_no
                  AND spart NE '20'.
ENDLOOP.

this shud improve the performance..

regards

satesh

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 17:00:40 GMT

Hi Divya

Thanks for this - however, these changes seem to increase the run-time rather than decrease it - very bizarre...

Kind regards

Andrew

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 17:07:37 GMT

Thanks Satesh

I will have a proper look at this tomorrow (its time to go home here!) - The <key field> is presumably the key field of table VBAK in the dictionary i.e. VBELN. However, I don't have data for the VBELN field in order to restrict the record set.

Kind regards

Andrew

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 17:18:44 GMT

Do a check for existance of some records in itab_1. 
CHECK NOT itab_1[] IS INITIAL.

Sort the table itab_1 by fields used in the where clause.
SORT itab_1 BY reg_no.

Instead of using INTO CORRESPONDING, try using INTO if
your itab_2 has just the fields that you are selecting.

Use 'VAKPA' table instead of VBAK as this is an index
table. But try to add more key fields of this table in 
your where clause.

Hope this helps,

Srinivas

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 17:44:06 GMT

Hi Andrew,

you can consider few options in optimizing this querry using <i>RANGES</i>.

here is the sample codes.


RANGES : lr_regno FOR vbak-kunnr.         
  lr_regno-sign = 'I'.                             
  lr_regno-option = 'EQ'.
 *** similar ranges for the other two fields            
 
    LOOP AT itab_1 INTO wa. 
      lr_regno-low = wa-reg_no.   
      APPEND lr_regno.      
** entries for the other fields                 
    ENDLOOP.                                  
 
    SELECT vbeln erdat erzet auart kunnr
      from vbak
 appending table itab          
            WHERE kunnr IN lr_regno
              and spart = '20'.

you might find this code helpful specially when the no. of entries in itab1 are not too many.

do let me know if you have any further queries.

there is a similar post some time back where a problem of similar kind was discussed. Have a look at the following

regards,

Kinshuk Saxena

Re: SQL Select performance

Former Member — Wed, 22 Mar 2006 23:01:32 GMT

When I get a problem like this, I find it a good idea to go to something similar that does what I want. In this case, if you go to transaction VA03 and do F4 on the order, you'll find that there is a search help for orders by customer. If you have a performance trace on when you do the search, you can see that it looks at M_VMVAE. This is a view for the table that Srinivas mentioned VAKPA. Armed with this, you can change your program:


DATA: BEGIN OF itab1a OCCURS 0,
        reg_no LIKE vbak-kunnr,
      END   OF itab1a.

DATA: BEGIN OF itab3 OCCURS 0,
        vbeln LIKE vbak-vbeln,
      END   OF itab3.

CHECK NOT itab1[] IS INITIAL.
LOOP AT itab1.
  itab1a-reg_no = itab1-reg_no.
  APPEND itab1a.
ENDLOOP.
SORT itab1a.
DELETE ADJACENT DUPLICATES FROM itab1a.

SELECT vbeln
  FROM m_vmvae
  INTO TABLE itab3
  FOR ALL ENTRIES IN itab1a
  WHERE kunde = itab1a-reg_no.

CHECK NOT itab3[] IS INITIAL.
SELECT vbeln erdat erzet auart kunnr
  FROM vbak
  INTO TABLE itab_2
  FOR ALL ENTRIES IN itab3
  WHERE vbeln = itab3-vbeln
  AND   spart = '20'.

This should be much quicker.

Rob

Re: SQL Select performance

Former Member — Thu, 23 Mar 2006 01:16:02 GMT

HI Andrew,

you can also try creating indexes for the selection fields ..

Or in this try giving appropriate key fields which fall in your selection criteria..

it shud be a little faster than previous selection

itab_1[] is not initial.
  SELECT 
  vbeln erdat erzet auart kunnr 
   FROM vbak 
   APPENDING TABLE itab_2
   FOR ALL ENTRIES IN itab_1
   where <key field>.
   LOOP AT itab_1. 
   DELETE itab_2 where 
   kunnr NE itab1-reg_no AND spart NE '20'.
   ENDLOOP.

regards

satesh

Re: SQL Select performance

Former Member — Thu, 23 Mar 2006 11:28:27 GMT

Wow, "much quicker" is an understatement! This approach has reduced the run time by a factor of about 100. Thanks Rob and thanks to everyone else for your replies.

Kind regards

Andrew

Re: SQL Select performance

Former Member — Thu, 23 Mar 2006 14:46:56 GMT

Glad to help, Andrew. In performance tuning, the most important things to do are:

Use an index effectively when working with large database tables.

Avoid nested loops when working with large internal tables.

Other methods can improve performance as well, but I think these are the two to remember.

Rob