How to valuate efficiency of aggregates (in time of query run)

[Former Member]

Hi, all!

Is there simple way to learn query execution time? I'd like to use it for valuating aggregates efficiency.

I know that it can be done by looking at BW Stat tables, but it is not comfortable. Maybe there is an option in rsrt to show total run time, without using cache?

[edwin_harpino · Accepted solution]

hi,

try transaction st03 ? choose expert mode,

<< BW system load << choose today << come out analysis view << here can see 'reporting views' >> runtime

drill down to query.

if Percentage of DB time STILL > 30% probably the existing aggregate not help.

transaction RSRT, Aggr -> mark aggregate found.

try mark 'do not use aggregates' and remove mark.

[Former Member · Accepted solution]

Thank you very much, powerful tool.

But is there any easier tool?

[Former Member · Accepted solution]

You can use RSRT to compare query results with and without the aggregate. Use the Execute & Debug option when you go to run your query. Then near the of the Debug options under Others, is an option to Display BW Statistics. Tihs option will display the components of the run time time and how many rows were read and how many were transferred to the OLAP processor.

Near the top of the the Debug options is the option Do Not Use Aggregates, so you can run the same query using the aggregates and then again without the aggregates and compare the results.

Couple of important items -

This is only testing one particular execution with the inputs you provide. If the query you are testing has many input prompts and many free characteristics, your users could be running the query in very different ways than your test.

Your primary interest in this test is the DB time which should pretty strongly correlate with the number of rows selected (read). The total RSRT time shouldn't be worried about for evaluating impact of an aggregate because the front end time should generally be the same regardless of use of the aggregate, since it is a function of the time it takes to get the data to the client and formatted, which should be the same since you are moving the same data to the client with the aggregate and without the aggregate.

RSRT also has the Debug option Display Run Schedule, which will providew the database optimziers Explain (Execution) Plan which you could also use to evalue the query's performance with and without the aggregate.

Before starting, you should make sure the DB statistics for the base cube and the aggregate are current in order to provide a valid comparison.

Also, when using RSRT, keep in mind, that if the query is against an Infocube that has data in both the E and F fact tables, two SQL queries really run, and RSRT will show two Explain plans if you have the Disaply Run Schedule option checked. If the query is against the multiprovider, you will have SQL queries against each underlying Infoprovider and Explain Plans for each.

[Former Member · Accepted solution]

One VERY important item when comparing query results as far as comparing the DB read time -

You need to run your query twice, and use the timings from the second run. The database will be buffering much of the data so comparisons between two runs of the same query will almost always show the DB time of the second run as being faster since much of the data now resides in th DB buffers after the first run.

And finally - when comparing results in RSRT, you should make sure you check the Debug option, Do Not Use Cache. You to force the query to run agasint the DB rather than retrieve the results from the OLAP cache. If query does use the OLAP cache, it will be evident becuase you will see no DB time and no DB rows selected.

[Former Member · Accepted solution]

Is there any explanations on that field names from Statistics Page(RSRT):

QDBTRANS

QNUMRANGES

RECCHAVLRE

QTIMEOLAPI

QTIMEOLAP

QTIMEDB

QTIMEVARDP

QTIMEUSER

QTIMECLIEN

TIMECHAVLR

TIMEAUTHCH

TIMEREST

DMTDBBASIC

DMTDBREMOT

DMTDBODS

DMTNCUMPRO

DMTREMOTES

DMTCUBEACC