BW Report taking long time to execute

[Former Member]

Experts,

I need your help asap.

I am running a BW report and it is taking long time to execute, almost 40 minutes.  Early it used to execute in 2-3 minutes but now it is taking 40 minutes.

I ran the query using RSRT tcode in "Debugg + Execute" mode and below are the logs i found.  From the below logs i could see that till 8:51 it ran good but suddenly it has taken nearly 40 minutes to go to next step and again from 9:33 it executed normal. 

Here I am not able to found why it has taken so much time.  Can you please help me to find out what is the reason it has taken so much time.

Thanks for your support !

Session UID	Step UID	Step Type	Step Counter	User name	Start Time	Handle ID	Handle Type	Info Provider	Object Name	Detail Levl Event ID	Event Text	Duration	Counter	Event Counter
4QNAB9IRTOKJY132WL7APL6A6	4QNABMJBYBCSI81GSJ63QWZYM	BEX3	3	JYTPY	7/26/2012 8:51	4	W3_I	QUERYDESIGNER	2	19911	3.x Web Rep. Item	0.000191	0	1
4QNAB9IRTOKJY132WL7APL6A6	4QNABMJBYBCSI81GSJ63QWZYM	BEX3	3	JYTPY	7/26/2012 8:51	5	W3_I	ZISO_MP1	GR1GR2NAVBLOCK	2	19911	3.x Web Rep. Item	0.000739	0	1
4QNAB9IRTOKJY132WL7APL6A6	4QNABMJBYBCSI81GSJ63QWZYM	BEX3	3	JYTPY	7/26/2012 8:51	1	W3_T			2	19919	Close Web 3.x	0.000081	0	1
4QNAB9IRTOKJY132WL7APL6A6	4QNDAELXTPQG0Q3UP6Y32IDQM	BEX3	4	JYTPY	7/26/2012 9:33		DFLT			2	1	Wait Time, User	271.660705	0	1
4QNAB9IRTOKJY132WL7APL6A6	4QNDAELXTPQG0Q3UP6Y32IDQM	BEX3	4	JYTPY	7/26/2012 9:33		DFLT			2		Not Assigned	0.000036	0	1
4QNAB9IRTOKJY132WL7APL6A6	4QNDAELXTPQG0Q3UP6Y32IDQM	BEX3	4	JYTPY	7/26/2012 9:33	1	BRFC		RRW3_WEBRFC	2	10000	RFC call	0.000145	0	1
4QNAB9IRTOKJY132WL7APL6A6	4QNDAELXTPQG0Q3UP6Y32IDQM	BEX3	4	JYTPY	7/26/2012 9:33	1	W3_I		MAIN	2	19911	3.x Web Rep. Item	0.000752	0	1
4QNAB9IRTOKJY132WL7APL6A6	4QNDAELXTPQG0Q3UP6Y32IDQM	BEX3	4	JYTPY	7/26/2012 9:33	2	W3_I	2	19911	3.x Web Rep. Item	0.000158	0	1

Regards,

Sajid.

[former_member210630 · Accepted solution]

Hi Sajid,

  Please try performing following pre-checks in case if you are facing slow performance issues suddenly,

1) Is the report performing the same way every time when you try executing the report or its some times the report behaves to run slower than normal.

2) Try running the report with smaller selection and see if the report still behaves the same way. Sometimes due to huge selection the report takes long time to output the desired results as lots of permutations & combinations are involved while fetching the data from the base target.

3) I would also suggest you to check if the DB indexes for the base target is intact in case if the report is based on a single Infocube & in case if it a Multiprovider, check if all the involved targets have DB indexes intact. If not then rebuild the DB indexes and try running the reports. you should see an improved performance.

4) In case if you feel that the report has slower performance since beginning then go for BWA indexes on the cubes this will surely improve the performance. Checking the query design would also be a good option, try optimizing the query.

Thanks & Regards

Pawan Kumar Chaturvedi

[Former Member · Accepted solution]

Hi,

I tried above tips but it is not working, still query performance is very slow.

Some of the old data in the cube has been archieved recently.

Is the Archiving data of cube effect the Query performance ?

Also I see in cube manage, if I go to Archeiving Tab, their it is showing cube status as lock.  I think after archeiving it show as lock only.  Please confirm.

Please let me know your suggestions.

Thanks alot.

Sajid

[Former Member]

Hello Sajid,

          -A large number of navigational attributes defined in the underlying InfoProvider may impact the overall performance.

          -A general recommendation on the need for calculations and formulas being leveraged as custom objects in the data set is to leverage the capabilities of the Restricted Key Figures and Calculated Key Figures as much as possible.

          -Please check with indexing in your infoproviders & also do not use many virtual providers because they take long time to get data over runtime.

Regards,

Naveen.

[Former Member]

You have 271 seconds duration on event id 1, Wait Time, User.  SAP defines this as:

"Time between two steps, such as entry of a variable, execution of an additional navigation step."

This doesn't make sense.  If you are running the query, you would know if it was waiting for a user action.

Unless the prompt is hidden behind other windows and you discover it 4 minutes later.

[Former Member]

Thanks.

Does Archiving of Cube affects the query performance.

Because earlier this cube data was not archived and after archiving is done, it is taking longer time.  So i am just suspecting if this has any issue with it.

Please suggest.

[Former Member]

We do archiving, and we haven't had that problem.  If anything, queries should run faster after archiving.

[Former Member]

Hi,

I tried above tips but it is not working, still query performance is very slow.

Some of the old data in the cube has been archieved recently.

Is the Archiving data of cube effect the Query performance ?

Also I see in cube manage, if I go to Archeiving Tab, their it is showing cube status as lock.  I think after archeiving it show as lock only.  Please confirm.

Please let me know your suggestions.

Thanks alot.

Sajid

[KamalMehta]

Hi Sajid ,

First of all are you running the report with the same selection or has the selection been changed .

Secondly  you need to check whether time taken is more at the Front end(OLAP) or at the data extraction(DM) from the relevant infoproviders using technical content .You can use the views RSDDSTAT_OLAP and RSDDSTAT_DM .You can also use ST03N to get this information .

Thanks

Kamal Mehta

[former_member210253]

Hi,

And also check Infoprovider( IC) statistics in the IC manage screen.

Check the cache enabled or not and also buffers.and check if they have any aggregates,Roll up status,and Infoprovider compressed or not

Regards,

Babu

[Former Member]

Hi Sajid,

After query execution, go to transaction ST03 and check for the query runtime statistics in BI Workload.

In this way, you could find in which step the query took long time to execute. (whether it's OLAP, data manager or front end etc.,)

Share the results of high runtime consumption. We'll suggest you how to optimize query based on the statistics results.

Br,

H