Performance select inside loop

[Former Member]

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 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.

[mmgc_riel]

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

[Former Member]

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

[matt]

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.

[former_member188724]

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

[former_member226225]

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.

[Former Member]

Raghunadh,

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

[Jelena_Perfiljeva]

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!

[matt]

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

[Former Member]

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

[Former Member]

This message was moderated.

[fcorodriguezl]

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.

[matt]

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.