Performance Issue SQL statement

[Former Member]

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

[Former Member]

hi hari

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

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

</b>

regards

ravish

b]plz dont forget to reward points if useful</b>

[Former Member]

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

Please advise.

[Former Member]

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.

<b>Reward if helpful</b>

[Former Member]

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

[Former Member]

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

<b>WHERE key eq t_output-key AND</b>

dkey_in = t_output-dkey.

thanks,

Mahesh