Select LIPS bad performance

[oliver_am]

Hello,

This Select from LIPS table takes too long, actually ends in a TIME OUT in production system.

LIPS table has only about 140.000 records.

Internal tabla LT_AUX has only 3 records ! It's not empty...

We are accesing using the key fields:

I did the same select in SE16 and its very fast.

I've made a ST05 trace in quality system and see the following:

Why it's doing these nested loops??

Thanks.

[former_member1716]

oliver.am,

The images attached seems to be broken, could you please update it again.

[FredericGirod]

As satishkumarbalasubramanian said, the attached images are broken.

Could you give these information:

- Database system

- in ST05, Explain screenshot

- The corresponding ABAP command (using the CODE button)

[oliver_am]

Ah, sorry,
The select is a simple FAE:

 SELECT

 lips~vbeln

 lips~posnr

 likp~lfdat

 likp~kunnr

 lips~lfimg

 lips~charg

 lips~matnr

 INTO CORRESPONDING FIELDS OF TABLE lt_lips

 FROM lips

 INNER JOIN likp

 ON likp~vbeln EQ lips~vbeln

 FOR ALL entries IN lt_aux

 WHERE lips~vbeln EQ lt_aux-vbeln

 AND lips~posnr EQ lt_aux-posnn

 AND likp~autlf EQ abap_true.

Images:

[oliver_am]

I've splited this select in two and now it's working fast.
But, what was the problem with the previous JOIN ?

[FredericGirod]

You forget about the database, is it Oracle ?

the CPU cost for LIKP~0 is good, but for LIPS~0 is bad.

~0 means the primary index --> the key of the table.

You do something really hard for a program: FOR ALL ENTRIES + INNER JOIN.

For all entries, will split the select by block of request. You could see it in the ST05. It cost a lot of time, because, if you split in 1 000, you will have 1 000 access.

If it is an Oracle database, the rebuild of the index, and the stat calculation will help you a lot.

But, there is another simple solution: look the table VLPMA. It will miss only the batch number

[Youssef3]

Hello,

Could you show me what did you do to solve the problem plz ?

[Sandra_Rossi]

You did it with SE16 and you found out that it was very fast. Do a SQL trace on SE16 and you'll understand that it's not the same execution plan.

The issue with FOR ALL ENTRIES is that it's converted into multiple NATIVE SELECT, and the database optimizes it without considering that there are many other same SELECT after that. In your case, your database considers that the NATIVE SELECT accesses 5 records so it will do the quickest access for 5 records. But if you multiply by the number of NATIVE SELECT, it costs a lot.

Now, if you transform your ABAP SELECT so that there's only one NATIVE SELECT, the database will maybe opt for another strategy, like a FULL SCAN if you query at least 10% of records (it's just an approximation, the database algorithm is much more complex). It's why nowadays it's advised to not use FOR ALL ENTRIES so that the system is more scalable.

[oliver_am]

Well, in se16 I've only did a selection from LIPS, in se16 cannot do a JOIN with more tables.
I've split the select in two, mantaining the FAE in each select and its working fast.

I've done this a lot of times with other tables like ekko and ekpo; a join with a FAE, and this is not happening. In this client LIPS table has only about 140.000 records, in others cases I'm doing similar selects in database tables with millons of records and is working fast.

I know that FAE is converted in multiple native selects. I take care of this, deleting duplicates from the table before doing the select, checking the table is not empty, etc...

I did not see in st05 a full scan access.

Could you suggest how can I build a proper select for this case? or How to select from data I have in an internal table without using a FAE? I would like to improve my abap skills.
Thanks

[venkateswaran_k]

Hi

This one option without using for all entries (as you asked). Use Appending clause.

LOOP AT LT_AUX INTO WA_AUX.
 SELECT LIPS~VBELN LIPS~POSNR LIKP~LFDAT LIKP~KUNNR LIPS~LFIMG LIPS~CHARG LIPS~MATNR
    APPENDING CORRESPONDING FIELDS OF TABLE LT_LIPS
 FROM LIPS INNER JOIN LIKP ON LIKP~VBELN EQ LIPS~VBELN
 WHERE LIPS~VBELP EQ WA_AUX~VBELN AND
       LIPS~POSNR EQ WA_AUX~POSNN AND
       LIKP~AUTLF EQ ABAP_TRUE 
ENDLOOP.

Since your lt_aux is only 3 records, this should give quicker results.

** This may not be a right solution, however you can try with this and see the performacne in ST05 and decide.

Regards,

Venkat

[MateuszAdamus]

Hello

I would definitely not recommend (for a lack of stronger word) running an SQL statement in a LOOP.

If one query with JOIN is not an option, then select data into two separate internal tables using two separate SELECT statements and the FOR ALL ENTRIES keyword, then join them in memory with LOOP. Do not select records from DB in LOOP.

Kind regards,
Mateusz

[Sandra_Rossi]

Each SELECT in each SAP installation, is different. I can't argue about why some SELECT are fast and others are slow. There are always good explanations (sometimes bugs in the database engine, but usually there are workarounds to avoid waiting for corrections).

You know what FAE is about, deleting duplicates and checking the table is not empty. Hopefully. I didn't talk about that. If you want to discuss more about what I explained previously, please tell us how many values there are in LT_AUX (in production).

By the way, how did you make sure that the problem really lies in that database access?

Concerning your execution plan, don't use the one in quality, use it the one in production system.

Before changing anything to your code, you should first understand why there is a difference between SE16 and your program. There is a difference, you should find it. If you can't find it, there's a problem.

There have been a number of blog posts about how to avoid FOR ALL ENTRIES (using a JOIN by using a temporary table, a GTT, AMDP, ...).

Concerning your question "why it's doing these nested loops?", here's what the plan does:

• read the index of LIPS corresponding to the selection to determine the row numbers
• retrieve the lines of LIPS corresponding to the row numbers
• read the index of LIKP corresponding to the selection to determine the row numbers
• retrieve the lines of LIKP corresponding to the row numbers
• do nested loops to join the lines between LIPS and LIKP according to VBELN (the nested loop iterates once in your case, because you indicate the full key, so no problem)
• repeat the above process for the five selections (separated with OR)
• concatenate all the results

Read the database documentation for more information about what means each type of operation in the execution plan.

[oliver_am]

Thanks for comment, Sandra

I'll try to research for more info abouts these operations.

LT_AUX in production has only 3 rows. that's why I was so surprised that it caused a timeout

By the moment is solved with two selects, but i'm going to look for more information.

[oliver_am]

I greatly appreciate the time spent explaining these things to me

[gasparerdelyi]

Do you have Oracle Tuning Pack and Oracle Diagnosis Pack licensed?
If yes, it could make sense to find the statement in AWR history or cursor cache and do the explain from there. Then you have the option to ask for the ASH samples.

(See SAP Note 853576)

In case the packs above are licensed the Analysis Report functionality or the SQL statements in SAP Note 1438410 can be helpful, too.

Can you provide the screenshot for example for the explain screen from DBACOCKPIT with the ASH samples requested (from the toolbar) ?