topic Re: Performance Issue SQL statement in Application Development and Automation Discussions

Performance Issue SQL statement

Former Member — Wed, 25 Jul 2007 13:49:00 GMT

Hello Friends,

I am writing this piece of SQL to delete a lot of data in the Data Base z tables .

It is running slow. Please advise as to how I can make this SQL better and faster .

skey is the key field in the ztable but not in the z_table1.

Thanks,

SELECT *

INTO TABLE t_output

FROM ztable

WHERE key IN s_key AND

out_date IN s_dte AND

dkey IN s_dkey.

IF t_output[] IS NOT INITIAL.

SELECT *

APPENDING TABLE t_output1

FROM z_table1

FOR ALL ENTRIES IN t_output

WHERE key IN s_key AND

dkey_in = t_output-dkey.

IF syst-subrc <> 0.

SELECT *

APPENDING TABLE t_output1

FROM z_table1

FOR ALL ENTRIES IN t_output

WHERE key IN s_key AND

dkey_out = t_output-dkey.

ENDIF.

Message was edited by:

Hari G Krishna

Re: Performance Issue SQL statement

Former Member — Wed, 25 Jul 2007 13:52:26 GMT

hi hari

u can probably check from st05 which statement is taking more time and hence modify it accordingly

and 1 more thing you should avoid using select * . instead u can select selecte fields from ztable as it is the biggest performance issue

regards

ravish

b]plz dont forget to reward points if useful

Re: Performance Issue SQL statement

Former Member — Wed, 25 Jul 2007 13:54:07 GMT

Hi,

Follow the following steps to optimise your ABAP Code

Avoid using SELECT...ENDSELECT... construct and use SELECT ... INTO TABLE.

Use WHERE clause in your SELECT statement to restrict the volume of data retrieved.

Design your Query to Use as much index fields as possible from left to right in your WHERE statement

Either enable buffering in your database table or create Indexes to speed up the query.

Use FOR ALL ENTRIES in your SELECT statement to retrieve the matching records at one shot.

Avoid using nested SELECT statement, SELECT within LOOPs.

Avoid using INTO CORRESPONDING FIELDS OF TABLE. Instead use INTO TABLE.

Avoid using SELECT * and Select only the required fields from the table.

Avoid nested loops when working with large internal tables.

Use assign instead of into in LOOPs for table types with large work areas

When in doubt call transaction SE30 and use the examples and check your code

Whenever using READ TABLE use BINARY SEARCH addition to speed up the search. Be sure to sort the internal table before binary search.

Use "CHECK" instead of IF/ENDIF whenever possible.

Use "CASE" instead of IF/ENDIF whenever possible.

Use "MOVE" with individual variable/field moves instead of "MOVE-CORRESPONDING", creates more coding but is more effcient.

Reward if helpful

Re: Performance Issue SQL statement

Former Member — Wed, 25 Jul 2007 13:55:49 GMT

Try this i think it will increase performance

SELECT *

INTO TABLE t_output

FROM ztable

WHERE key IN s_key AND

out_date IN s_dte AND

dkey IN s_dkey.

IF t_output[] IS NOT INITIAL.

SELECT *

into TABLE t_output1

FROM z_table1

FOR ALL ENTRIES IN t_output

WHERE key IN s_key AND

(dkey_in = t_output-dkey or dkey_out = t_output-dkey).

ENDIF.

Reward points if Helpful

Re: Performance Issue SQL statement

Former Member — Wed, 25 Jul 2007 13:57:23 GMT

I know i dont need to use SELECT * but I need to delete all the fields in the table for that entry.

Please advise.

Re: Performance Issue SQL statement

Former Member — Wed, 25 Jul 2007 13:58:43 GMT

HI,

Do you need all the fields in this table ur using * instead use specific fields..

and one small change

SELECT *

APPENDING TABLE t_output1

FROM z_table1

FOR ALL ENTRIES IN t_output

WHERE key eq t_output-key AND

dkey_in = t_output-dkey.

thanks,

Mahesh