topic Reduce execution time with selects in Application Development and Automation Discussions

Reduce execution time with selects

Former Member — Thu, 13 Jul 2006 07:47:50 GMT

Hi,

I have to reduce the execution time in a report, most of the consumed time is in the select query.

I have a table, gt_result:

DATA: BEGIN OF gwa_result,

tknum LIKE vttk-tknum,

stabf LIKE vttk-stabf,

shtyp LIKE vttk-shtyp,

route LIKE vttk-route,

vsart LIKE vttk-vsart,

signi LIKE vttk-signi,

dtabf LIKE vttk-dtabf,

vbeln LIKE likp-vbeln,

/bshm/le_nr_cust LIKE likp-/bshm/le_nr_cust,

vkorg LIKE likp-vkorg,

werks LIKE likp-werks,

regio LIKE kna1-regio,

land1 LIKE kna1-land1,

xegld LIKE t005-xegld,

intca LIKE t005-intca,

bezei LIKE tvrot-bezei,

bezei1 LIKE t173t-bezei,

fecha(10) type c.

DATA: END OF gwa_result.

DATA: gt_result LIKE STANDARD TABLE OF gwa_result.

And the select query is this:

SELECT k_{tknum k}stabf k_{shtyp k}route k_{vsart k}signi

k~dtabf

l_{vbeln l}/bshm/le_nr_cust l_{vkorg l}werks n_{regio n}land1 o_{xegld o}intca

t_{bezei tt}bezei

FROM vttk AS k

INNER JOIN vttp AS p ON k_{tknum = p}tknum

INNER JOIN likp AS l ON p_{vbeln = l}vbeln

INNER JOIN kna1 AS n ON l_{kunnr = n}kunnr

INNER JOIN t005 AS o ON n_{land1 = o}land1

INNER JOIN tvrot AS t ON t_{route = k}route AND t~spras = sy-langu

INNER JOIN t173t AS tt ON tt_{vsart = k}vsart AND tt~spras = sy-langu

INTO TABLE gt_result

WHERE k_{tknum IN s_tknum AND k}tplst IN s_tplst AND k~route IN s_route AND

k~erdat BETWEEN s_erdat-low AND s_erdat-high AND

l~/bshm/le_nr_cust <> ' ' "IS NOT NULL

AND k~stabf = 'X'

AND k_{tknum NOT IN ( SELECT tk}tknum FROM vttk AS tk

INNER JOIN vttp AS tp ON tk_{tknum = tp}tknum

INNER JOIN likp AS tl ON tp_{vbeln = tl}vbeln

WHERE l~/bshm/le_nr_cust IS NULL )

AND k~tknum NOT IN ( SELECT tknum FROM /bshs/ssm_eship )

AND ( o~xegld = ' '

OR ( o~xegld = 'X' AND

( ( n~land1 = 'ES'

AND ( n_{regio = '51' OR n}regio = '52'

OR n_{regio = '35' OR n}regio = '38' ) )

OR n~land1 = 'ESC' ) )

OR o_{intca = 'AD' OR o}intca = 'GI' ).

Does somebody know how to reduce the execution time ?.

Thanks.

Re: Reduce execution time with selects

dani_mn — Thu, 13 Jul 2006 07:50:55 GMT

You are joining to many tables.

Try to restrict the joins to 1 level only ie only for 2 tables.

Try to avoid joins and use FOR ALL ENTRIES.

Regards,

Wasim Ahmed

Re: Reduce execution time with selects

Former Member — Thu, 13 Jul 2006 07:51:13 GMT

It is not advisable to use more than 3 joins in one select statement.

Create more than one internal table.

Select using 3 inner join and put the record in one internal table.Again use another select statement with 3 inner joins , use for all entries 'first internal table'.

Re: Reduce execution time with selects

former_member183804 — Thu, 13 Jul 2006 08:06:38 GMT

Hello Mario,

avoid any join for extra data. Any join is bypassing the SAP table buffering. For the inner select use for all entries as alternative.

Best Regards

Klaus

Re: Reduce execution time with selects

Former Member — Thu, 13 Jul 2006 08:17:32 GMT

Hi,

Try to remove the join. Use seperate selects as shown in example below and for the sake of selection, keep some key fields in your internal table.

Then once your final table is created, you can copy the table into GT_FINAL which will contain only fields you need.

data : begin of it_likp occurs 0,

vbeln like likp-vbeln,

/bshm/le_nr_cust like likp-/bshm/le_nr_cust,

vkorg like likp-vkorg,

werks like likp-werks,

kunnr likr likp-kunnr,

end of it_likp.

data : begin of it_kna1 occurs 0,

kunnr like...

regio....

land1...

end of it_kna1 occurs 0,

Select tknum stabf shtyp route vsart signi dtabf

from VTTP

into table gt_result

WHERE tknum IN s_tknum AND

tplst IN s_tplst AND

route IN s_route AND

erdat BETWEEN s_erdat-low AND s_erdat-high.

select vbeln /bshm/le_nr_cust

vkorg werks kunnr

from likp

into table it_likp

for all entries in gt_result

where vbeln = gt_result-vbeln.

select kunnr

regio

land1

from kna1

into it_kna1

for all entries in it_likp.

similarly for other tables.

Then loop at gt result and read corresponding table and populate entire record :

loop at gt_result.

read table it_likp where vbeln = gt_result-vbeln.

if sy-subrc eq 0.

move corresponding fields of it_likp into gt_result.

gt_result-kunnr = it_likp-kunnr.

modify gt_result.

endif.

read table it_kna1 where kunnr = gt_result-vbeln.

if sy-subrc eq 0.

gt_result-regio = it-kna1-regio.

gt_result-land1 = it-kna1-land1.

modify gt_result.

endif.

endloop.

Re: Reduce execution time with selects

andreas_mann3 — Thu, 13 Jul 2006 08:41:41 GMT

hi,

never seen such a select .:)

it's an artist's work but very "performant" -i think

so:

1) select all CU and master tables into sorted / hashed tables and use later read table...

2) use a join only for likp and vttp

and unknot your where-clause

hope that helps

Andreas

Re: Reduce execution time with selects

Former Member — Thu, 13 Jul 2006 13:34:14 GMT

Wow - that's a pretty complicated select. I think the first thing I would do is simplify it as much as possible. Remove some of the joins and subqueries and test them separately. If any of the where clauses (on the larger tables) aren't using a key, that will slow down the entire statement. Also check to make sure that none of the select-options are empty.

Rob