User exit vars & query performance

[robertbuttram]

I have a global calculated key figure that currently returns the difference between two dates, both of which are basic key figures in the info provider. I'm considering modifying this formula to use a user exit variable that returns the system date. (This is essentially a copy of the 0DAT SAP exit variable that can be used to restrict characteristic values according to the current calendar day.)

My question is, what is the likely performance impact of using a user exit variable in place of a basic key figure in this calculation?

I've collected some pre- and post-change statistics on query run times and found the query ran longer after making the change, but the increased run times were due almost entirely to longer DB read times. (I collected the post-change statistics in the middle of the day, when DB contention was probably higher than the night before when I collected the pre-change statistics.) There was almost no impact on OLAP times, which is where I would expect this change to be reflected.

Should I expect a significant performance hit for switching from a basic KF to a user exit variable in this formula? Could the use of this variable have produced an increase in DB read times?

Thanks,

Bob

[edwin_harpino]

hi Bob,

try to check if the sql statement used by the query is correct with rsrt -> 'execute+debug' option display 'sql ...', i experienced that sometimes after virtual char/kf added, the sql statement used is wrong, instead of select the needed only char and kf, the statement also select many unnecessary char and kf. what we did is with rsrt -> 'generate report'. and 'delete old abap' if necessary.

also take a look oss note

557870 'FAQ BW Query Performance'

and 567746 'Composite note BW 3.x performance Query and Web'.

and Prakash weblog on query performance

/people/prakash.darji/blog/2006/01/27/query-creation-checklist

/people/prakash.darji/blog/2006/01/26/query-optimization

hope this helps.

557870 'FAQ BW Query Performance'

....

11. What can I do if a query has a high OLAP runtime?

Answers:

o Check if a high number of Cells transferred to the OLAP (use

"All data" to get value "No. of Cells")

o <b>Use RSRT technical Information to check if any extra OLAP-processing

is necessary</b> (Stock Query, Exception Aggregation, Calc. before

Aggregation, <b>Virtual Char. Key Figures</b>, Attributes in Calculated

Key Figs, Time-dependent Currency Translation)

together with a high number of records transferred.

o Check if a user exit Usage is involved in the OLAP runtime?

....

[robertbuttram]

A.H.P,

Tons of useful information here. Thanks!

I checked the SQL and the query doesn't seem to be pulling back anything more than what it needs. Also, note 557870 makes pretty clear that any problems associated with the user exit variable would show up on OLAP times, which is basically what I wanted to confirm.

Thanks again.

Bob

[Former Member]

I have implemented virtual chars as well. In my query I have a few filters on material and plant, but what I notice is that the virtual char code is ignoring the filters I applied in the query on material and plant and goes through the entire list of data records in the cube, due to this the run time is signifacntly more...I also notice that it finally only displays the data for the filters I apply, but I dont understand why it should go throught the whole list of material and plant combinations in the code itself. Anyway I could correct this?