topic Re: Performance select inside loop in Application Development and Automation Discussions

Performance select inside loop

Former Member — Fri, 10 Oct 2014 13:09:24 GMT

Hi Experts,

I have a tuning program that is causing me some trouble, I am new to tuning so please help me.

Here is my problem :

LOOP AT ti_chave_completa.

l_centro_de = ti_chave_completa-rprctr_de.

l_centro_at = ti_chave_completa-rprctr_ate.

l_conta_de = ti_chave_completa-racct_de.

l_conta_at = ti_chave_completa-racct_ate.

SELECT rprctr racct hsl sgtxt budat refdocnr blart

FROM glpca

APPENDING TABLE ti_glpca_docreal

WHERE kokrs = p_kokrs

AND ryear = v_ano_low

AND ( rprctr BETWEEN l_centro_de AND l_centro_at )

AND rldnr = c_8a

AND rvers = '000'

AND rbukrs = p_bukrs

AND poper >= v_mes_low

AND ( rrcty = c_zero OR rrcty = c_dois )

AND ( racct BETWEEN l_conta_de AND l_conta_at ).

***** Then the program does another loop and select if there is a v_ano_high in the parameters-***********

IF v_ano_low NE v_ano_high.

IF NOT v_ano_high IS INITIAL.

LOOP AT ti_chave_completa.

l_centro_de = ti_chave_completa-rprctr_de.

l_centro_at = ti_chave_completa-rprctr_ate.

l_conta_de = ti_chave_completa-racct_de. "<--Keys

l_conta_at = ti_chave_completa-racct_ate.

SELECT rprctr racct hsl sgtxt budat refdocnr blart

FROM glpca

APPENDING TABLE ti_glpca_docreal

WHERE rldnr = c_8a

AND ( rrcty = c_zero OR rrcty = c_dois )

AND rvers = '000'

AND ryear = v_ano_high

AND poper <= v_mes_high

AND rbukrs = p_bukrs

AND ( rprctr BETWEEN l_centro_de

AND l_centro_at )

AND kokrs = p_kokrs

AND ( racct BETWEEN l_conta_de

AND l_conta_at ).

*****I tried to remove the select from the loop but the ti_chave_completa keys are causing me problems.

Any suggestion to help solve this would be great.

Re: Performance select inside loop

mmgc_riel — Fri, 10 Oct 2014 13:13:47 GMT

Hi,

If the content of the table glpca is not too big, < 100MB
You could consider to read all records of glpca in an internal table

and then use that internal table instead of select.

greetings.

Maarten

Re: Performance select inside loop

former_member188724 — Fri, 10 Oct 2014 13:30:55 GMT

Hi,

As Riel suggested please select records from glpca into itab using

for all entries of ti_chave_completa and there by use the internal table to Loop instead of select.

Hope this helps.

Regards,

K.S

Re: Performance select inside loop

former_member226225 — Fri, 10 Oct 2014 13:49:58 GMT

Hi Patrick.

First sort the internal table ti_chave_completa by fields ascending.

check the keys if it is not the primary keys .

create one temporary internal table with same type of ti_chave_completa.

ti_chave_completa_temp[] = ti_chave_completa[].

delete adjacent duplictaes by comparing the fields and writes the select query as follows.

if ti_chave_completa_temp[] is not initial.

SELECT rprctr racct hsl sgtxt budat refdocnr blart

FROM glpca

INTO TABLE ti_glpca_docreal

for all entries in ti_chave_completa_temp

WHERE kokrs = p_kokrs

AND ryear = v_ano_low

AND ( rprctr BETWEEN ti_chave_completa_temp-rprctr_de AND ti_chave_completa_temp-rprctr_ate )

AND rldnr = c_8a

AND rvers = '000'

AND rbukrs = p_bukrs

AND poper >= v_mes_low

AND ( rrcty = c_zero OR rrcty = c_dois )

AND ( racct BETWEEN ti_chave_completa_temp-racct_de.AND ti_chave_completa_temp-racct_ate. ).

endif.

I hope this will work . Please test the query like this please get back to us if you have any issues.

Thanks & Regards,

Raghunadh Kodali.

Re: Performance select inside loop

Former Member — Fri, 10 Oct 2014 13:51:05 GMT

If you are on Netweaver 7.0 EHP2 then you can do in depth analysis using tcode SAT. It will help you to pinpoint where is the performance degrading.

Pl do a search on SDN for the setup of SAT tcode and try it out. I am sure you will be able to resolve the performance issue yourself.

Regards

Re: Performance select inside loop

Former Member — Fri, 10 Oct 2014 14:01:50 GMT

This message was moderated.

Re: Performance select inside loop

Former Member — Fri, 10 Oct 2014 19:04:50 GMT

Raghunadh,

I tried your suggestion and I get the error cannot use Between, like and in for FOR ALL ENTRIES.

Re: Performance select inside loop

Former Member — Fri, 10 Oct 2014 19:06:41 GMT

Hi Maarten,

Can you explain what you mean by "You could consider to read all records of glpca in an internal table

and then use that internal table instead of select" please?

Thanks,

Patrick

Re: Performance select inside loop

fcorodriguezl — Fri, 10 Oct 2014 19:54:59 GMT

Hi Patrick,

Is bad practices apply a select in loop instruction. For this cases, yo need use for all entries instruction. For exam.

SELECT rprctr racct hsl sgtxt budat refdocnr blart

INTO TABLE ti_glpca_doc_real

FROM glpca

FOR ALL ENTRIES ti_chave

WHERE rprctr IN ti_glpca_doc_real-rprctr AND

kokrs = p_kokrs AND

ryear = lv_ano_low AND

etc..

IF sy-subrc EQ 0.

SORT ti_chave BY rpctr.

ENDIF.

In your loop.

LOOP ti_chave INTO wa_chave.

READ TABLE ti_glpca_doc_real INTO wa_glpca_docreal WITH KEY rpctr = wa_chave-rpctr

BINARY SEARCH.

IF sy-subrc EQ 0.

'assign your fields.

ENDIF:

ENDLOOP.

You can use too a interna table for low and other for high.

I hope helps.

Re: Performance select inside loop

Jelena_Perfiljeva — Fri, 10 Oct 2014 20:59:09 GMT

This fragment looks like a hot mess, quite honestly. To really improve it I think you might need to check where the data in ti_chave_completa is coming from and what does it mean.

Depending on what this program does exactly and data volume, it might be more efficient to disregard some criteria in SELECT and then remove the unneeded records from the resulting internal table. But one would need to be very careful with this. I'd really look beyond improving just one specific fragment in this program.

Good luck!

Re: Performance select inside loop

matt — Sat, 11 Oct 2014 04:24:30 GMT

He means "Read all the records of glpca into an internal table. And then use the internal table instead of select". Without teaching you basic ABAP, I'm not sure how he can make that clearer.

Re: Performance select inside loop

matt — Sat, 11 Oct 2014 04:37:41 GMT

The BETWEEN also prevents using an INNER JOIN - which are of course to be preferred over FOR ALL ENTRIES

Re: Performance select inside loop

matt — Sat, 11 Oct 2014 04:40:32 GMT

It's already been pointed out that the connection between the two reads is using BETWEEN,

Apart from that, SORTING a table and using BINARY SEARCH is such old school. Rather use a HASHED table - or a SORTED table if you have to.