performance issue with joins on table VBAK, VBEP, VBKD and VBAP

[Former Member]

hi all,

i have a report where there is a join on all 4 tables VBAK, VBEP, VBKD and VBAP.

the report is giving performance issues because of this join.

all the key fields are used for the joining of tables. but some of the non-key fields like vbap-vstel, vbap-abgru and vbep-wadat are also part of select query and are getting filled.

because of these there is a performance issue.

is there any way i can improve the performance of the join select query?

i am trying "for all entries" clause...

kindly provide any alternative if possible.

thanks.

[Former Member]

Hi,

Pls perform some of the below steps as applicable for the performance improvement:

a) Remove join on all the tables and put joins only on header and item (VBAK & VBAP).

b) code should have separate select for VBEP and VBKD.

c) remove the non key fields from the where clause. Once you retrieve data from the database into the internal table, sort the table and delete the entries which are not part of the non-key fields like vstel, abgru and wadat.

d) last option is you can create index in the VBAP & VBEP table with respect to the fields vstel, abgru & wadat ( not advisable)

e) buffering option on database tables also possible.

f) select only the fields into the internal table that are applicable for the processing logic and also the select query should contaian the field names in the same order as mentioned in the database table.

Hope this helps.

Regards

JLN

[Former Member]

Have you explored using Dynamic SQL statements ?

For instance, let us assume that the user is querying on vbep-wadat. What you can do is first perform a Join between VBAP and VBEP first and then try linking other tables. Additionally based on your requirments fine tune the where clause(dynamic where clause) as well.

[Former Member]

Hello,

There will be prformance issues with join queries for these tables, as they conatin hell lot of data. Even if it is solved now, in future there will be issues.

You need to have an alternative query in place of join query which is a :

SELECT query with FOR ALL ENTERIES IN <itab>

like:

SELECT <fields req> FROM vbak INTO gt_itab

WHERE some conditions

IF SY_SUBRC EQ 0.

SELECT <fields req> FROM VBEP INTO gt_itab1

FOR ALL ENTERIES OF gt_itab

WHERE some conditions

ENDIF.

Now append both internal table into your target internal table by looping both internal table.

Hope this will solve problem.

Reward if useful.