Heavy SQL Improve performance

[Former Member]

Hi Dear experts,

I have some codes leading to bad performance, Hope you could give me any hints. Here are the codes, thank you so much.

SELECT valfr valto instance ibase FROM ibin AS ib INTO ls_ibin2
     WHERE valto = ibxx_utc_high
     AND   ibase = p_ls_comp_ibase
   AND   NOT EXISTS ( SELECT * FROM ibst
                        WHERE instance = ib~instance
                        AND   valto    = ib~valto )
   ORDER BY ibase instance.

ENDSELECT.

[EstiV]

You can try to select into an internal table in stead of using SELECT... ENDSELECT.  Also limit the field list in your subquery.

Something like:

select valfr valto instance ibase from ibin into table lt_ibin
     where valto = ibxx_utc_high
      and  ibase = p_is_comp_ibase
      and  not exists ( select mandt from ibst
                          where instance = ibin~instance
                          and   valto    = ibin~valto )
 order by ibase instance.

Also how many times is this statement called?  Is it called within a loop?  If so, is there any way you can turn the multiple selects into a single select statement?

Are you familiar with transaction SAT?  You can measure the performance of your program using the tool, but it also has a very useful Tips & Tricks section if you are struggling with performance.

[former_member186741]

I agree with Esti, use into table...not sure about the subquery....are instance and valto key fields of IBST??

also, "order by" is often very time-expensive. Try it without and sort the table after the select.

[EstiV]

Also as there is no index on valto, but there is one on ibase, you may try to swap around the order of ibase and valto in the WHERE clause.

[Former Member]

Hi Esti,

Thank you for comments. The select statement is nested in an other loop. So I think we need to use for all enties statemnt. What's your opinion.

BR

Jay

[Former Member]

Hi Woodruff,

I'm not aware of that. I will have a test by add 'Order By' in the select statment and after the select.Thank you so much.

BR

Jay

[Former Member]

Hi Esti,

Seems you are familiar with CRM development.

I don't understand by 'ou may try to swap around the order of ibase and valto in the WHERE clause.'. Could you please explain in details??  Thank you so much.

BR

Jay

[EstiV]

I will never admit to be familiar with CRM development , but that is not really relevant here, it's just basic SQL.

What I mean is just this:

select valfr valto instance ibase from ibin into table lt_ibin  
     where ibase = p_is_comp_ibase   "<--where statement for ibase first
      and    valto = ibxx_utc_high 
      etc.....

The reason I suggest this is that sometimes it helps the database to pick the correct index to use.   Of course what I don't know is whether valto is in fact a lot more limiting than ibase (i.e. more records would be filtered out by using valto first), you would need to test it to know for sure.

You could also run an SQL trace in ST05 to check whether the correct index is used.

[EstiV]

That will likely help.  Remember to check that your for all entries table is not empty before doing the select!

[former_member186741]

of course you can define the table as a 'sorted' one (be careful whether to make it unique keys or not) and then no explicit sort is required.

[Former Member]

Hello,

Please try the below code snippet. The code lines are much more than what you have written, but definitely improve the runtime of the code because of less number of hits on database.

DATA:

lt_ibin TYPE TABLE OF ibin,

lt_not_exist TYPE TABLE OF ibst.

FIELD-SYMBOLS: <lfs_ibin> TYPE ibin.

*--First of all select all the entries from IBIN and skip the existnace

*  check as of now

SELECT valfr valto instance ibase FROM ibin INTO TABLE lt_ibin

   WHERE valto = ibxx_utc_high

     AND ibase = p_ls_comp_ibase.

IF sy-subrc EQ 0.

*--Now fetch all the entries from IBST table with the same instance

*  and valto present in IBIN table  

   SORT lt_ibin BY ibase instance.

   SELECT * FROM ibst

     INTO TABLE lt_not_exist

       FOR ALL ENTRIES IN lt_ibin

         WHERE instance = lt_ibin-instance

           AND valto    = lt_ibin-valto.

   IF sy-subrc EQ 0.

*--Sort table for read with binary search   

     SORT lt_not_exist BY instance valto.

   ENDIF.

*--Check which all instance and valto are present in LT_NOT_EXIST table

*  the ones which are present have to be deleted to handle the NOT EXIST

*  check

   LOOP AT lt_ibin ASSIGNING <lfs_ibin>.

     READ TABLE lt_not_exist TRANSPORTING NO FIELDS

     WITH KEY instance = <lfs_ibin>-instance

              valto    = <lfs_ibin>-valto

     BINARY SEARCH.

     IF sy-subrc EQ 0.

*--If the entry is present, means it has to be excluded from final table     

       "clearing the field symbol will also clear the table field value.

       CLEAR: <lfs_ibin>-instance.

     ENDIF.

   ENDLOOP.

*  Since we have cleared the field value of instance, we can delete using

*  the below statement

   DELETE lt_ibin WHERE instance = space.

ENDIF.

Hope this helps.

Regards,

Ajeet

[Former Member]

Hi Ajeet,

Thank you for your comments. You have put all the codes.

BR

Jay