Performance issue because of the multiple selects in the loop statements

[Former Member]

Hi,

In my code there are two select statements inside the loop.code is as shown below.

Because of these selects in the loop its causing the performance problem.

Please let me know how can I avoid the selects in the loop and improve the performace.

LOOP AT gt_invlist WHERE lifex IS initial.

SELECT SINGLE vbelv posnv

INTO (ld_vbelv, ld_posnv)

FROM vbfa

WHERE vbeln = gt_invlist-vbeln

AND posnn = gt_invlist-posnr

AND vbtyp_n IN ('M', 'N', '5', '6', 'O', 'P')

AND vbtyp_v = 'J'.

IF sy-subrc EQ 0.

SELECT SINGLE zzptdloadid INTO ld_zzptdloadid

FROM lips

WHERE vbeln = ld_vbelv

AND posnr = ld_posnv.

IF sy-subrc EQ 0.

gt_invlist-lifex = ld_zzptdloadid+0(10).

MODIFY gt_invlist.

ENDIF.

ENDIF.

ENDLOOP.

Thanks in advance

[Former Member]

Hi,

Move your select statements outside the loop and do a read table inside the loop.

gt_invlist_tmp = gt_invlist.

delete gt_invlist_tmp where lifex is initial.

if not gt_invlist_tmp is initial.

select ...FROM vbfa

for all entries in gt_invlist_tmp where...

endif.

similarly for select on table lips.

Within the loop, do a READ table and populate the values.

Hope this helps.

Regards,

Shyam

[yuri_ziryukin]

Hello Anwar,

it looks like you try to find the preceeding delivery for the list of invoices and select one field from the delivery item table.

Let me propose you the following:

1. Check what idexes exist in your system for VBFA table. This table may be quite large, so I don't want to advise you creating a new index for this particular select if there are other indexes that may be extended.

The ultimate solution would be to have the following index: vbeln, posnn, vbtyp_n, vbtyp_v, vbelv, posnv.

In this case only index access (w/o table) will be performed and the DB performance will be optimal.

To further optimize the performance please use "for all entries" addition and run the select only once filling an internal table with necessary entries.

2. Secondly, for the additional field from LIPS you make a table access. The selection fields are the two fields from primary key and I don't like the idea of defining additional index with all key fields + 1 more. Although this might help further.

As an alternative for two selects I would propose trying a join:

SELECT vbfa_{vbeln vbfa}posnn lips~zzptdloadid

INTO lt_zzptdloadid

FROM lips

INNER JOIN vbfa ON

lips_{vbeln = vbfa}vbelv AND

lips_{posnr = vbfa}posnv

FOR ALL ENTRIES IN gt_invlist

WHERE vbfa~vbeln = gt_invlist-vbeln

AND vbfa~posnn = gt_invlist-posnr

AND vbfa~vbtyp_n IN ('M', 'N', '5', '6', 'O', 'P')

AND vbfa~vbtyp_v = 'J'

Then you should get your table with required entries. So you'll only have to loop over gt_invlist and read table lt_zzptdloadid to get your entry.

I hope this will improve your performance.

regards,

Yuri

[Former Member]

Thanks yuri.Hope it will work well with my code.

[former_member182566]

Hi.

You should not read VBFA using the "to" fields (vbeln, posnn), only when you have the "from" fields (vlebv, posnv).

Usually you should not create indexes in VBFA to hide these bad accesses.

From OSS Note 185530, 4 a):

In table VBFA only the preceeding document is used to search for the 
subsequent document (for example, delivery for order). 
Searching the other way makes no sense with this table since the 
preceding documents (for example, order for delivery) are stored directly 
in the document tables. Thus reading in table VBFA is a one-way street.
Correct:  SELECT vgbel FROM lips WHERE vbeln = ...; 
  or  SELECT vgbel FROM vbrp WHERE vbeln = ...; 
  or  SELECT aubel FROM vbrp WHERE vbeln = ... 

Regards,

Rui Dantas

[yuri_ziryukin]

Hello Rui,

I wanted to propose this too, but additional selection criteria (vbfa_{vbtyp_n IN ('M', 'N', '5', '6', 'O', 'P') AND vbfa}vbtyp_v = 'J' ) concerned me a little. I was not quite sure if the preceeding document number from LIPS fulfils these additonal criteria. I wanted to be on a safe side from the functional point of view.

Yuri

[former_member182566]

Hi Yuri,

You have to leave also some space for the OT to think. I am more in favor of pointing them in the right direction that to post code that they'll just copy+paste, often without really understanding it.

In this case choosing the right table to read the data from (LIPS if 'J') is definitely the way to go, not to create an additional index in VBFA (the only other option that would really solve the problem).

Regards,

Rui