Time Out error on Select from VBRP

[vinotha_m]

Hi Everyone,

We get a time out error on Select from VBRP even though we do not have much data.

Say we have data of about 10,000 records.

Could anyone suggest how this could be rectified.

This is our select

SELECT vbeln

posnr

aubel

aupos

netwr

fkimg

kzwi2

INTO TABLE it_vbrp

FROM vbrp

FOR ALL ENTRIES IN it_vbap

WHERE aubel EQ it_vbap-vbeln

AND aupos EQ it_vbap-posnr.

VBAP has about 50,000 records.

Thanks,

Vinotha M

[former_member589029]

Since AUBEL and AUPOS are non key fields and there also is no index for those two fields you are doing a full table scan for every entry in it_vbap. With the number of entries being 50,000 this adds up even though your VBRP table does not have a lot of entries.

If AUBEL and AUPOS are the only fields you have available to restrict your selection you should consider to create a secondary index for AUBEL and AUPOS on VBRP. That should increase the performance significantly.

Hope that helps,

Michael

[vinotha_m]

Hi Michael,

Thanks for your reply,

But isnt creating many secondary indices also bad for performance.

And is there anythng else we can do?

[former_member589029]

That is correct - any write or delete access to that table will update the index table as well. However the benefit of using an index outweighs the impact in your case. Also that table does not yet have any active secondary index (at least in our ECC60 system) so you shouldn't see any negative impact by adding the index. Only if you add a lot of secondary indexes to a table the performance will be noticeably affected.

There is no exact maximum number of indexes defined. SAP states you should create as little secondary index as possible but as many as needed......

The only other option would be to pass in at least the first primary key field and restrict on that because that would do an index range scan on the primary index, but if you don't have that information the secondary index is your best option.

Michael

[Former Member]

I think it's generally a bad idea to create a secondary index to speed up a SELECT in a single report program. There may be better programming options available.

Rob

[ThomasZloch]

One way would be to make use of sales document flow table VBFA.

Thomas

[Former Member]

Hi Vinotha,

As Thomas recommended, try a link over another table. Please take a look at the e.g. logical database VAV and you will find more table links which can improve your performance.

Your performance has to be improved anyway! But I would take a look at how many records were processed before the time-crash. This can be achieved by implementing the clause UP TO <n> ROWS.

Try it,

Heinz

[P561888]

Hi,

Try to Use package size option, Data is put in table in packets of size n in SELECT.

[valter_oliveira]

Hi there!

Why don't you use subqueries instead of for all entries? If you go to tcode SE30, Tips&Tricks you will see that subqueries are more faster than for all entries.

Let's analyse your example. Suposing you are getting VBAP data in a way like:

SELECT * FROM vbap INTO TABLE it_vbap WHERE pstyv EQ 'TAN'.

And then,

SELECT * FROM vbrp
  INTO TABLE it_vbrp
  FOR ALL ENTRIES IN it_vbap
 WHERE aubel = it_vbap-vbeln
   AND aupos = it_vbap-posnr.

Why don't you try something like (only one select, and only one itab):

SELECT * FROM vbrp AS F
  INTO TABLE it_vbrp
 WHERE EXISTS ( SELECT vbeln FROM vbap
                 WHERE vbeln = F~aubel
                   AND posnr = F~aupos
                   AND pstyv = 'TAN' ).

Use ST05 to find the difference. I tested with 1000 records (I know it's not too much) and took half of the time.

Hope it helps!

P.S - PACKAGE SIZE is for memory management if you will have problems with your internal tables (I think it's not the case with only 50,000 records).

Regards,

Valter Oliveira.

Edited by: Valter Oliveira on Sep 25, 2008 9:57 AM

[Former Member]

Hi ,

There are 2 ways to improve the performance

1. to create index on the combination of aubel and aupos ,

For this u need to contact ur basis consultant to find out the table size and available space in DB.

2. Use table VBFA which has complete document flow , for this contact ur SD consultant to find out the respective referance field.

Hope This Helps.

Regards,

Ramesh.

[Former Member]

HI,

Use VBFA table, thats the best way to go forward to improve the performance of your code.

in VBFA table the VBELV is the preceding document ( give the order number here ) and the VBELN holds the subsequent document number, check for which document categories you should choose for deliveries and use those in where clause against field VBTYP_N.

By doing this you will use the Primary keys all the way from the Order to Document flow to Billing document tables.

And yes creating secondary index is not a good idea. It create an overhead of updating the index every now and then.

regards,

Advait

[Former Member]

Hello Vinotha M ,

As far SAP is concern the aubel and aupos are the non primary key value of their table. And it is not good idea to create the secondary index so, i suggest you to change your select statement to use some other tables and try to fetch data.

[Former Member]

Hi,

I will not suggest you to for Secondary Index creation just for this Issue. Best one is to use the Sales Flow Table VBFA and find the Direct Relation among the SO & Invoice and get the PK of VBRP into SELECT.

I believe you can also look for a FM......to get Invoice Line Item Details....

Use this...RV_INVOICE_DOCUMENT_READ

Or look for the FM in Invoice Print program RVADIN01.

[Former Member]

When ever you use for all entries, make sure that the itab (it_vbap) is not initial.

so enclose ur select query between.

if not it_vbap is initial.

.......

endif.

Sort your itab it_vbap by aubel aupos and delete its adjacent duplicate records comparing aubel aupos before the select query is executed.

If still the query is slow create a secondary index on mandt aubel aupos for table VBRP.

hope this will solve ur issue.

Regards

Sam