topic Re: Heavy SQL Improve performance in Application Development and Automation Discussions

Heavy SQL Improve performance

Former Member — Wed, 03 Jun 2015 00:33:54 GMT

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.

Re: Heavy SQL Improve performance

EstiV — Wed, 03 Jun 2015 01:02:37 GMT

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.

Re: Heavy SQL Improve performance

former_member186741 — Wed, 03 Jun 2015 01:45:49 GMT

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.

Re: Heavy SQL Improve performance

EstiV — Wed, 03 Jun 2015 01:56:58 GMT

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.

Re: Heavy SQL Improve performance

Former Member — Wed, 03 Jun 2015 02:03:46 GMT

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.

Jay

Re: Heavy SQL Improve performance

Former Member — Wed, 03 Jun 2015 02:05:55 GMT

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.

Jay

Re: Heavy SQL Improve performance

Former Member — Wed, 03 Jun 2015 02:09:09 GMT

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.

Jay

Re: Heavy SQL Improve performance

EstiV — Wed, 03 Jun 2015 03:02:06 GMT

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.

Re: Heavy SQL Improve performance

EstiV — Wed, 03 Jun 2015 03:15:35 GMT

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

Re: Heavy SQL Improve performance

Former Member — Wed, 03 Jun 2015 04:44:55 GMT

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

Re: Heavy SQL Improve performance

former_member186741 — Wed, 03 Jun 2015 04:58:01 GMT

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.

Re: Heavy SQL Improve performance

Former Member — Wed, 03 Jun 2015 06:03:35 GMT

Hi Ajeet,

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

Jay