topic Re: View Performance Issue in Application Development and Automation Discussions

View Performance Issue

Former Member — Mon, 06 Nov 2006 10:07:07 GMT

Hi All

We have created a zview which combines vbrk & vrpma tables, but the performance(response time) of the view is not good as it has millions of records. what can be done to the view to increase performance ? we have a situation where we cannot go without view !

Thanks in advance !

Re: View Performance Issue

Former Member — Mon, 06 Nov 2006 11:41:44 GMT

Pass mandatory fields as conditions

Re: View Performance Issue

Former Member — Mon, 06 Nov 2006 12:09:42 GMT

Hi Ashok,

Thanks for your reply, we have already specified join conditions while creating the view. How can we improve the table performance ?? does buffering the view will help the case. the view has approximately 13 crore records !

Thanks

Srikanth

Re: View Performance Issue

Former Member — Mon, 06 Nov 2006 13:57:31 GMT

I don't thin that buffering so big view is good solution. Moreover vbrk table is changed very often - so buffering the view will make no sense.

Check if your joins conditions are useing keys or indexes.

BR< Jacek

Message was edited by: Jacek Slowikowski

Re: View Performance Issue

Former Member — Mon, 06 Nov 2006 14:00:53 GMT

Hi Srkanth,

The tables you are using are big and contain transaction data, so buffering is not an option.

Part of your difficulty is that the document number field in VRPMA is not at the top of the key field list, so the join to create the view is inefficient. Could you create another index for VRPMA on document number?

Why is the view necessary at all? Can the business requirement be met via a BW or other custom solution.

John Armytage

Re: View Performance Issue

Former Member — Mon, 06 Nov 2006 15:43:53 GMT

Compare your view with standard SAP view VBRP_VRPMA. The tables aren't exactly the same, but should give you an idea of how to construct your view.

If the view is OK, what does you select look like?

Rob

Re: View Performance Issue

Former Member — Tue, 07 Nov 2006 05:22:56 GMT

Hi all,

Thank you very much for your thoughtful inputs ! actually report is taking indefinite time when retrieving records from the view.

Here is the processing block which is taking a long time

select distinct vbeln fkart vtweg knumv fkdat kunrg sfakn knuma

into table i_man_accr2

from zvbrkvrpma

for all entries in i_doc_flow

where ( fkart eq 'ZCB4' or fkart eq 'S2' or fkart eq 'S1' )

and vbeln eq i_doc_flow-vbeln

and vtweg eq p_vtweg.

does selecting fields from vbrk will help avoid taking view at all into consideration??

Thanks in advance !

Srikanth

Re: View Performance Issue

Former Member — Tue, 07 Nov 2006 07:33:25 GMT

Hi Srikanth,

Based on the sample code, you do not need a view at all since all the fields come from VBRK, so processing should be:

Select vbeln fkart vtweg knumv fkdat kunrg sfakn knuma

into table i_man_accr2

from vbrk

for all entries in i_doc_flow

where ( fkart eq 'ZCB4' or fkart eq 'S2' or fkart eq 'S1' )

and vbeln eq i_doc_flow-vbeln

and vtweg eq p_vtweg.

You do not need DISTINCT as the data comes from one table. This is a reason why the code was taking such a long time as it was retrieving the same VBRK data over and over again.