Performance turning questions

[former_member295022]

Hi all,

I want to improve my database and abap time. The following code has the performance issue, so who can tell me how to improve my code performance?

<removed by moderator>

Table: VBRK has 10 index:

0: MANDT, VBELN

LOC: MANDT, LCNUM

N: MANDT, ISHFALNR, ISHEINRI

N1: MANDT, ISHSAMNR

Z01: MANDT, XBLNR, KUNRG

Z02: MANDT, ERDAT

Z03: MANDT, RFBSK, FKTYP

Z04: MANDT, KUNAG, FKDAT

Z05: MANDT, VGBEL

Z07: MANDT, FKDAT_RL, ZTERM

Table: VBRP has 6 index:

0: MANDT, VBELN, POSNR

3: MANDT, ISHORGID, ERDAT

OI0: MANDT,OICONTNR, AUBEL, VGBEL, VGPOS

z01: MANDT, WERKS, FKIMG

z02: MANDT, AUBEL, VGBEL

z03: MANDT, VKBUR, ERDAT

Thanks a lot .

Ivy

Moderator message: please post only relevant code parts, observe the maximum of 5000 characters per post to preserve formatting, also please choose more meaningful titles when posting.

Edited by: Thomas Zloch on May 24, 2011 4:00 PM

[yuri_ziryukin]

Hello Ivy,

I don't know if many entries with the same billing_doc appear in your table lt_inv_data.

If yes, please make sure that you don't add duplicate entries to the range table.

Alternatively you may move your selects out of the loop and make "for all entries". This will spare you your trick with the counter to avoid a dump due to the large range table (anyway, make sure there are no duplicate billing_docs in your internal table).

Next typical mistake:

• Delete the records which deleted flag is not null

DELETE lt_vbrk WHERE fksto IS NOT INITIAL.

Always filter out records as soon as possible. Include this condition into your selects above instead of deleting in ABAP.

• Caculate the freight

LOOP AT lt_freight INTO ls_freight

WHERE vbeln = <fs_vbrk>-vbeln.

Define lt_freight as a sorted table with the non-unique key VBELN.

Removethe statement SORT lt_freight BY vbeln.

Now your loop will benefit from the sorted table type.

For the selects:

Definition of a new index on VBRP with fields MANDT, VBELN, KZWI4 will help further, but this should be a last thing to do.

Regards,

Yuri

Edited by: Yuri Ziryukin on May 24, 2011 10:55 AM

[former_member295022]

Hi Yuri,

Originally i used the for all entries in instead of the range table. But the internal table lt_inv_data has about 15,000 records, and the performance for this SQL is very bad.

So i replaced the for all entries in with range table. and limit the length for the range table is 8000 to avoided a dump.

Unfortunately, the performance even worse, because the abap time increased and the database time just a little improvement.

I don't know why......

BTW, the sorted table is very useful, thanks again.

Regards,

Ivy

[brad_bohn]

For the selects:
Definition of a new index on VBRP with fields MANDT, VBELN, KZWI4 will help further, but this should be a last thing to do.

That's not a good choice for an index. It would just be a waste of space. Your other suggestions sound on track but it's hard to tell - the original post is illegible ;-).

@Ivy: On the FOR ALL ENTRIES, did you trace/analyze WHY the performance is bad? There could be several reasons, not related to the use of the FOR ALL ENTRIES...

[yuri_ziryukin]

> That's not a good choice for an index.  It would just be a waste of space.  Your other suggestions sound on track but it's hard to tell - the original post is illegible ;-).

Brad, this index allows to spare the table access, only index access will be necessary. It will be improving select performance for VBRP access probably by factors. I agree that's not the best thing to do, but if the performance of the report is really very critical, I would still consider that.

[yuri_ziryukin]

Hi Yuri,
> 
> Originally i used the for all entries in instead of the range table. But the internal table lt_inv_data has about 15,000 records, and the performance for this SQL is very bad. 
> 
> So i replaced the for all entries in with range table. and limit the length for the range table is 8000 to avoided a dump.
> 
> Unfortunately, the performance even worse, because the abap time increased and the database time just a little improvement. 
> 
> I don't know why......
> 
> BTW, the sorted table is very useful, thanks again.
> 
> 
> 
> Regards,
> Ivy

Ivy, the range table will not provide you any significant improvement comparing with "for all entries".

15.000 documents is quite significant number. When selecting with a good index (I guess the right one is used in your case), you should expect around several milliseconds per row. So one select with 15.000 in "for all entries" table should probably take around 75 seconds (roughly estimated).

[brad_bohn]

I understand your point but I don't think 'index access only' implemented for a single program is a good reason for creating another secondary index, especially with the leading fields of the primary key. I could not read the blob of the original post and code before coffee this morning, but it doesn't sound like that was a big issue with the code. Your suggestions sounded good though .

[yuri_ziryukin]

I don't think 'index access only' implemented for a single program is a good reason for creating another secondary index, especially with the leading fields of the primary key.

Sure, that's why I have specifically said it's a last thing to do