Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

SE11 Table "VBOX": Can performance of a dictionary table be improved?

Former Member
0 Kudos
1,221

Hi All,

There is one standard table - VBOX: SD Document: Billing Document: Rebate Index.

In the system it has tons of entries around 4,50,000,00 in our system. There is a requirement to improve its performance.

To my knowledge we generally do this by creating secondary indexes. Here there is already one created by SAP itself through a note. This table doesn't have many fields - only 5 - and all of them are key fields. I checked SAP notes but aren't any related to this table. Thus I would like to know if anyone has has faced this type of issue earlier. I checked this forum and I found entries pertaining to table improvement but most of them suggested to create secondary indexes and use key fields in program etc.

Thus can anyone suggest how can this performance be improved and are there any other features available in SAP to improve a SE11 table performance? Also is it possible to archive the old data - sort of data archiving?

All your views/suggestions are welcome.

Thanks/Regards

Saurabh

1 ACCEPTED SOLUTION

Former Member
0 Kudos
209

usually VBOX would be archived together with the billing documents (transaction SARA, object: SD_VBRK), this surely is the best method to reduce the data volume.

however, there is report RV15B001 which allows you to delete VBOX-entries independently from the archiving object. there are performance issues with that report, so you might want to read note 12150 and all it's sisters/brothers.

in addition, please read note 75778 which describes an alternate model to implement such rebates, impacting your problem from yet another path.

Edited by: Mylène Dorias on Jan 11, 2010 11:56 AM

13 REPLIES 13

Former Member
0 Kudos
210

usually VBOX would be archived together with the billing documents (transaction SARA, object: SD_VBRK), this surely is the best method to reduce the data volume.

however, there is report RV15B001 which allows you to delete VBOX-entries independently from the archiving object. there are performance issues with that report, so you might want to read note 12150 and all it's sisters/brothers.

in addition, please read note 75778 which describes an alternate model to implement such rebates, impacting your problem from yet another path.

Edited by: Mylène Dorias on Jan 11, 2010 11:56 AM

0 Kudos
209

Thanks both for the responses

-


Hi Mylene

I checked T-code SARA and program RV15B001, I see it is for transaction OVB3. But I need your help in better understanding of this. I haven't done archiving before - so few questions. I have 2 things:-

Say suppose i use this t-code SARA with the specified object key SD_VBRK (which would also run for VBOX I guess) it would archive the documents but they would remain accessible, right? Becoz we dont want to delete the data. And where would the data be archived and how it can be accessed later.

For the second, the program RV15B001 would this delete the documents totally? So just wanted have some idea as how would this program work? I tried to check the note 12150, but its not accessible saying its for internal purpose only or in reworking.

Can you please share the info?

Thanks/Regards

Saurabh.

0 Kudos
209

>

> Say suppose i use this t-code SARA with the specified object key SD_VBRK (which would also run for VBOX I guess) it would archive the documents but they would remain accessible, right? Becoz we dont want to delete the data. And where would the data be archived and how it can be accessed later.

With transaction SARA, Object SD_VBRK you would archive all the billing documents that fullfill the specifications you have given for them in transaction VORR. The data would still be available, either directly as flatfiles (although not accessible using VF03 as far as I know), or indirectly by setting up archiving info structures.

--> this is a project (and should be considered when you/your company is thinking about archiving in general (= legal implications etc.). if you need quick help with table VBOX, this is not an option ... but still, I had to write it here because it is the best (= most consistent) solution, complete ... etc.

>

> For the second, the program RV15B001 would this delete the documents totally? So just wanted have some idea as how would this program work? I tried to check the note 12150, but its not accessible saying its for internal purpose only or in reworking.

This would delete 'only' the VBOX entries and leave the rest of the billing documents intact. it's not an optimal solution, but if you need help quickly, this is the way. Of course you should test this report in a sandbox first and make very sure that it works as you expect.

0 Kudos
209

Mylene, thanks a lot for that.

I get your point. Which means if we run through the program RV15B001 it will delete the VBOX entries for those billing documents. Yes this can be a time thing to do, but deletion is not an option. May be will have to work in detail on the archving aspect. Just one last thing, are these the only way to improve performance of very large tables like these?

Regards

Saurabh

0 Kudos
209

>

> Mylene, thanks a lot for that.

>

> I get your point. Which means if we run through the program RV15B001 it will delete the VBOX entries for those billing documents. Yes this can be a time thing to do, but deletion is not an option. May be will have to work in detail on the archving aspect. Just one last thing, are these the only way to improve performance of very large tables like these?

>

> Regards

> Saurabh

No, there are other ways. Keep in mind though, that data reduction is the most solid solution to mass-inflicted performance problems ... but if it is not an option, I can understand this.

So ... next step: run a ST05 trace on all the transactions that are having performance problems with VBOX (or other tables) and extract the REOPEN/SELECT statements that are using this table. Analyze which fields are used for reading the table and create secondary indexes for these select/where statements. I cannot give you further details, since the exact structure of that index would be DB-dependent (like with/without field client), so maybe - if this is the way you want to go, come back here and state your OS/DB/SAP release (with patch and/or build-levels).

0 Kudos
209

Oh .. yeah, I did this check the select statements for VBOX. But I didn't found any scope for creating indexes in this table as it has very few fields namely

MANDT

KAPPL

KOTABNR

VAKEY

FBUDA

VBELN

POSNR

and all of them are marked as key fields also there is one standard index created by SAP itself through a note which is already present from ECC. However, yes this can be checked with other tables that are using this table VBOX as join.

The system is AIX/Oracle 10.2.0.2.0/ECC 6.0(SAP_ABA 701 level 00030).

Saurabh.

0 Kudos
209

you will have to investigate the trace results again, then.

the pre-delivered index from SAP is for 3 fields

MANDT

KAPPL

VBELN

what does your trace say? is this index accessed by the application? what's the select/where statement like?

0 Kudos
209

These fields have been accessed, but in all the Z reports where its been called has a join of queries which are not recommended by SAP - they have join of more than 5 tables, like one below:

But this sort of thing is there in plenty of programs.

SELECT VBRKKUNRG VBRKBUKRS VBRKLANDTX VBRKVKORG VBRK~STCEG

LIKPKUNNR KONHKSCHL VBOX~VAKEY

VBRPMATNR VBOXFBUDA

VBOXVBELN VBOXPOSNR VBOX~KOTABNR KONPKNUMH KONPKOPOS

VBRKERDAT KONHKNUMA_BO VBRP~BONBA

VBRPAUBEL VBRPAUPOS VBRPFKLMG VBRPFKIMG

VBRPKURSK VBRKKNUMV

VBRKKUNAG VBRPNETWR VBRK~RFBSK

VBRKWAERK VBRPMEINS VBRPVRKME VBRPMWSKZ

VBRKVTWEG VBRPSPART

VBRKLAND1 VBRPWERKS VBRP~PRSDT

VBRKFKART VBRKXBLNR VBRKSPART VBRPPRODH

INTO TABLE Z_VBOX_KP

FROM KONH

INNER JOIN KONP

ON KONPKNUMH = KONHKNUMH

INNER JOIN VBOX

ON VBOXKAPPL = KONHKAPPL

AND VBOXKOTABNR = KONHKOTABNR

AND VBOXVAKEY = KONHVAKEY

INNER JOIN VBRK

ON VBRKVBELN = VBOXVBELN

INNER JOIN VBRP

ON VBRPVBELN = VBRKVBELN

AND VBRPPOSNR = VBOXPOSNR

INNER JOIN LIKP

ON LIKPVBELN = VBRPVGBEL

INNER JOIN LIPS

ON LIPSVBELN = LIKPVBELN

FOR ALL ENTRIES IN I_ZV_KON_AH

WHERE KONH~KNUMH = I_ZV_KON_AH-KNUMH

AND KONH~KAPPL = C_V

AND VBOX~KOTABNR = I_ZV_KON_AH-KOTABNR

AND VBOX~VAKEY = I_ZV_KON_AH-VAKEY(50)

AND VBOX~FBUDA >= I_ZV_KON_AH-DATAB

AND VBOX~FBUDA <= I_ZV_KON_AH-DATBI.

And this would be a mammoth change and can be done in only Z programs.

Can a index on other fields be created as well? But then all are key fields.

Please advise.

Regards

Saurabh

0 Kudos
209

the problem is:

VAKEY is not unique,

FBUDA in your example is a 'quasi-range',

so everything that would really be helpful like the combination of VBELN and POSNR is out of the question.

do you have a sandbox? try an index for VBOX there with fields:

MANDT

KOTABNR

VAKEY

FBUDA

I'm not 100% sure about the fields, since I am on MaxDB (where things are totally different), so maybe somebody else has a suggestion?

0 Kudos
209

The unique thing about all the queries for VBOX is that they have a inner join on the same fields in all of them

INNER JOIN VBOX

ON VBOXKAPPL = KONHKAPPL

AND VBOXKOTABNR = KONHKOTABNR

AND VBOXVAKEY = KONHVAKEY.

Field FUBDA is there in the where clause in all of them.

I will try out the new index in the sandbox, this should improve somewhat, given the sheer size of the table.

I would post a reply to the same post.

Many thanks Mylene for your valuable inputs. Hats off!

Saurabh.

0 Kudos
209

Hi Maynil,

Just a question, I have the same problem as yours. We manage to have 1,800,000,000 table entries. I will be implementing data archiving in our system and we are currently using DB400 as our DB.

So my question is, did you ever thought of table partitioning as an option? I see that this was not discussed in your question. But of course I totally agree with Mylene!

Hoping for your kind feedback.

Mark

0 Kudos
209

Hi Saurabh,

Could you please share what you ended up doing? we have same situation where we have performance issue with VBOX table. Thanks.

Former Member
0 Kudos
209

Hi, data is stored using already defined method:

1. Create Archive file

2. Storage of the archive file. Archived files can be stored hierarchically, optically or manually even though SAP does not recommend the manual storage of archive file.

3. Deletion of the data. This is done after the program reads the archive file.