Performance problem in SDPVGEN

[Former Member]

Hi ,

i am running report SDPVGEN (generate product proposal) for only one sales organization. This report is executed in the background. This report is taking too much time to execute. I raised a message with SAP too. They suggested that in the function module SD_DPP_HISTORY there is a select statement which is taking too much time.

SELECT vkorg vtweg spart kunnr vbeln vbtyp erdat erzet

FROM vbak

INTO CORRESPONDING FIELDS OF ipvinx

WHERE kunnr = i_kuagv-kunnr AND

vkorg = i_vkorg AND

vtweg = i_vtweg AND

spart = i_spart AND

vbtyp = vbtyp_auftr

ORDER BY erdat DESCENDING erzet ASCENDING.

PERFORM periode_ermitteln USING i_pvsmd-pvper

ipvinx-vbeln

ipvinx-erdat

CHANGING da_num.

ipvinx-num = da_num.

IF da_num > i_pvsmd-pvanz.

EXIT.

ENDIF.

APPEND ipvinx.

ENDSELECT.

Going forward I created a Z function module and changed the SELECT .....ENDSELECT with INTO TABLES. But still the performance is not improved. I my checked finding the expensive select statements, too much time is been spent on sequential read of VBAK.

Please suggest solution.

Thanks,

Chinmay

[Former Member]

You'll probably find that index VBAK~OI0 is not active in your system.

Rob

[HermannGahm]

Hi Chinmay,

please check if index VBAK~OI0 is active on the db.

If not, activate it.

(use transaction SE14 - enter vbak - display - indexes)

Kind regards,

Hermann

P.S.: Rob was faster

Edited by: Hermann Gahm on Aug 8, 2011 6:45 PM

[Former Member]

I don't think I would activate it just yet. Since an OSS message is open, see what SAP has to say.

Rob

[HermannGahm]

Hi Rob,

as far as i understood the statement - right now- is in customer code.

If it is in customer code, SAP standard support will reply with SAP note 48204 (or similar ones ).

After proving it is in Standard code the same... it depends... it might take a longer journey through support (where maybe somebody might recommend the activation of that index) or makes it to development support quickly.

There somebody might look at it and... it depends again... most likely somebody might recommend the activation of that index.

I doubt there will be an other outcome, since there is SAP note 378836 as welll

but i agree, it there shoudl be an official answer other than SAP note 48204 since it is in standard code as well.

Meanwhile I would create that index...

Kind regards,

Hermann

[Former Member]

Sorry Hermann, I should have explained more.

I try to avoid creating or activating indexes for one off reports due to the extra system overhead. The OP has probably(?) explained the situation to OSS and they would likely(?) be in a better position to judge.

I would just run the report(?) in the background

Rob

[HermannGahm]

Hi Rob,

> I try to avoid creating or activating indexes for one off reports due to the extra system overhead. The OP has probably(?) explained the situation to OSS and they would likely(?) be in a better position to judge.
> 
> I would just run the report(?) in the background 

oh i see, that's something different, of course.

Yes for sure, the index should only be created if the sql is executed frequently and/or if the program is part of an important business process and/or if the program runs at the same time with other important business processes. If the program is not executed very often, not important or not bothering the system... we can of course leaft it as it is

Let's see, maybe the mesaage makes it to our queue (technology - development support)

Kind regards,

Hermann

[Former Member]

Thanks for you replies,

I checked the index OI0 in table VBAK and is found to be inactive. But the index is made on the fields MANDT, KUNNR, VKORG, ERNAM, OIEXGNUM whereas I am passing KUNNR, VKORG, VTWEG, SPART, VBTYP in my select query. I tried checking SAP note 378836 as well. It do suggest index creation on fields MANDT KUNNR VKORG VTWEG SPART VBTYP VBELN ERDAT ERZET in addition to applying correction 246740. But my system is ECC 6.0 and the SAP note is valid till SAP 46C and the correction 246740 is already applied.

So, I have two options either to activate the index OI0 or a create a new with fields suggested in SAP note 378836. Not sure about which will be effective. Please suggest which one to go.

Thanks

Chinmay

[HermannGahm]

Hi,

> So, I have two options either to activate the index OI0 or a create a new with fields suggested in SAP note 378836. Not sure about which will be effective. Please suggest which one to go.
> 
> Thanks
> Chinmay

i would go for thei OIO index and test the run time again.

Kind regards,

Hermann

[Former Member]

Please check with your DBAs or Basis analysts to make sure they are OK with creating another index. It may be more effective to simply run the program in the background.

Rob

[HermannGahm]

Hi Rob,

i think that is what you mean:

The most significant question to ask when thinking about adding a new index:

u201CWill the index eliminate significantly more work than it introduces (at the moments when it really matters) ?u201D

from: http://jonathanlewis.wordpress.com/2010/05/11/philosophy-10/

and i agree with you.

As i said before the index should only be created if it helps to improve the overall performance, that's the bottom line.

Kind regards,

Hermann

[Former Member]

I seem to remember this discussion from somewhere

I guess we are both preaching to the choir.

Rob

[Former Member]

Hi Rob and Hermann,

Thanks for your valuable inputs. I have found that in table VBAK secondary index OI0 is inactive. I have proposed either of the two solutions to be applied to increase the performance of the SDPVGEN program.

1. In the table VBAK there is a already an secondary index OI0(currently inactive) on fields MANDT, KUNNR, VKORG, ERNAM,

OIEXGNUM. We need to activate this index and run the program SDPVGEN to see if the performance is increased.

2. The other option is to create an new secondary index on table VBAK with fields MANDT, KUNNR, VKORG, VTWEG, SPART,

VBTYP, VBELN, ERDAT, ERZET as suggested in SAP note 378836.

Recommended the former to be applied and tested first. I will let you know if solves my problem.

Thanks,

Chinmay