topic Re: Select statement performance improvement. in Application Development and Automation Discussions

Select statement performance improvement.

Former Member — Tue, 16 Dec 2008 11:10:58 GMT

HI Guru's,

I am new to ABAP.

I have the below select stement

000304 SELECT mandt msgguid pid exetimest

000305 INTO TABLE lt_key

000306 UP TO lv_del_rows ROWS

000307 FROM (gv_master)

000308 WHERE

000309 * msgstate IN rt_msgstate

000310 * AND ( adapt_stat = cl_xms_persist=>co_stat_adap_processed

000311 * OR adapt_stat = cl_xms_persist=>co_stat_adap_undefined )

000312 * AND itfaction = ls_itfaction

000313 * AND msgtype = cl_xms_persist=>co_async

000314 * AND

000315 exetimest LE lv_timestamp

000316 AND exetimest GE last_ts

000317 AND reorg = cl_xms_persist=>co_reorg_ini

000318 ORDER BY mandt itfaction reorg exetimest.

Can anyone help me how i can improve the performance of this statement?

Here is the sql trace for the statement:

SELECT

/*+

FIRST_ROWS (100)

"MANDT" , "MSGGUID" , "PID" , "EXETIMEST"

FROM

"SXMSPMAST"

WHERE

"MANDT" = :A0 AND "EXETIMEST" <= :A1 AND "EXETIMEST" >= :A2 AND "REORG" = :A3

ORDER BY

"MANDT" , "ITFACTION" , "REORG" , "EXETIMEST"

Execution Plan

SELECT STATEMENT ( Estimated Costs = 3 , Estimated #Rows = 544 )

4 SORT ORDER BY

( Estim. Costs = 2 , Estim. #Rows = 544 )

Estim. CPU-Costs = 15.671.852 Estim. IO-Costs = 1

3 FILTER

2 TABLE ACCESS BY INDEX ROWID SXMSPMAST

( Estim. Costs = 1 , Estim. #Rows = 544 )

Estim. CPU-Costs = 11.130 Estim. IO-Costs = 1

1 INDEX RANGE SCAN SXMSPMAST~TST

Search Columns: 2

Estim. CPU-Costs = 3.329 Estim. IO-Costs = 0

Do I need to create any new index ? Do i need to remove the Order By clause?

Thanks in advance.

Re: Select statement performance improvement.

Former Member — Tue, 16 Dec 2008 11:28:09 GMT

Hi,

Create a secondary index on EXETIMEST and REORG for the table SXMSPMAST.

Regards,

Suthan

Re: Select statement performance improvement.

Former Member — Tue, 16 Dec 2008 11:41:02 GMT

Hi Suthan,

Thanks for the inputs.

From the ST05 trace can you please confirm if the statement is using an index by name TST?

Thabks.

Re: Select statement performance improvement.

ThomasZloch — Tue, 16 Dec 2008 11:54:56 GMT

> 1 INDEX RANGE SCAN SXMSPMAST~TST

Yes it is using index TST. I cannot find it here, did you create that index yourself? what are the fields? Is the above code your own or SAP standard?.

Thomas

Re: Select statement performance improvement.

Former Member — Tue, 16 Dec 2008 12:12:54 GMT

HI Thomas,

This is a copy of a standard program. I created an index by name TST for another program. It has the below fields :

EXETIMEST

MANDT

PID

ADMINUSER

MSGTYPE

CAn you please suggest me my actions.

Thanks in advance.

Re: Select statement performance improvement.

Former Member — Tue, 16 Dec 2008 12:37:57 GMT

why is there an

UP TO lv_del_rows ROWS

together with an ORDER BY?

The database will find all rows fulfilling the condition but returns only the largest Top lv_del_rows.

Therefore it can take a while.

Your index, always put the client field at first position.

actually I am not really convinced by your logic:

itfaction reorg exetimest.

itfaction is the first in the sort order, so all records with the smallest itfactio will come first, but itfaction is not specified, is this really what you want?

Change the index to mandt reorg exetimest reorg

and change the ORDER BY to mandt reorg exetimest

then it will become fast.


* AND ( adapt_stat = cl_xms_persist=>co_stat_adap_processed
000311 * OR adapt_stat = cl_xms_persist=>co_stat_adap_undefined )
000312 * AND itfaction = ls_itfaction
000313 * AND msgtype = cl_xms_persist=>co_async
000314 * AND
000315 exetimest LE lv_timestamp
000316 AND exetimest GE last_ts
000317 AND reorg = cl_xms_persist=>co_reorg_ini
000318 ORDER BY mandt itfaction reorg exetimest.

Re: Select statement performance improvement.

ThomasZloch — Tue, 16 Dec 2008 12:54:16 GMT

Try removing index TST, it is almost the same as the existing index MON (EXETIMEST, MANDT, PID, MSGSTATE). Too many indexes might "confuse" the optimizer.

Since it is a copy of a standard program, also try searching for SAP notes for known performance problems with the original program.

Thomas