performance tuning select on LIPS

[Former Member]

Hi All,

I have a situation where in below query is taking way long time to execute. Please suggest what can be done.

driver table is having 4.5 Lac 450,000 records

IF driver_table[] IS NOT INITIAL.

SELECT vbeln

               posnr

               field3

               field4

                -

                -

                -

                -

              field10

FROM lips

INTO TABLE lt_lips

FOR ALL ENTERIES IN driver_table

WHERE vbeln = driver_table-vbeln

AND      posnr = driver_table-posnr.

ENDIF.

As above, I am selecting data through primery key field's then there should not be a performnce issue. Please suggest if something can be done to improve the performance of above query.

Thanks,

Vikas

Moderator message: please use international metrics only.

Message was edited by: Thomas Zloch

[volker_borowski2]

... , I am selecting data through primery key field's then there should not be a performance issue. ...

Hi,

this is a wrong assumption!

It might be true in general or in many cases, but it tends to get wrong if you need to execute this many, many times. (Like 4,5 Lac times, whatever a Lac is).

The problem is, that a FAE SELECT has no other option to process the join of the DB to the ABAP table as a nested loop (which means it is doing a complete index access to the DB for each record of the drivertable).

But if you join big tables, you will see a DB join at some point switch to a merge or hash join, because the nested loop is too expensive. At that point you have it frequently that a DB join outruns a FAE.

So you need to dig into your data distribution to see, if you really can do anything about this.

- How is the driver table filled?

- Is it possible to specify a DB join, or is building the driver table so complex, that you can not specify it in a single statement

- What is your DB type and what are some stats values from your LIPS table like: total number rows, physical size of the table, physical size of the index, index height, clustering factor of the index.

Without changing to a DB join (for which you can not say yet if it will be a good or a bad thing), the only option seems for me to be worth checking is, if you really need to specify POSNR, or do you select all POSNRs per VBELN anyway? This would change the access for all required records to a range scan per VBELN which might be beneficial depending on your DB type.

Volker