cancel
Showing results for 
Search instead for 
Did you mean: 

Performance of the query is poor

Former Member
0 Kudos

Hi All,

This is Prasad. I have a problem with the query it is taking more time to retrieve the data from the Cube. In the query they are using a Variable of type Customer Exit. The Cube is not at compressed. I think the issue with the F fact table is due to the high number of table partitions (requests) that it has to select from. If I compress the cube, the performance of the query is increased r not? Is there any alternative for improving the performance of the query. Somebody suggested Result set query, iam not aware of this technique if u know let me know.

Thanks in advance

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi prasad,

there are many ways to improve query performance.

When u compress the cube then ur query performance will certainly boostup.

other ways are:

1. Create aggregates.

2. Use OLAP cache.

3. Pre calculated webtemplates.

4. BI Accelarator.

Assign points if helpful.

Answers (3)

Answers (3)

Former Member
0 Kudos

Before trying to asnwer HOW? lets try answering WHERE the issues are and for that lets analyze the pain points form the query standpoint via sanity check like try using RSRT.Go to RSRT and put your query name.Select Execute and debug option and you will a whole bunch of options to select and watch how the query gets executed.

OR

View BI statistics using tcode- ST03N ,prerequisite is to have the BI statistics installed and activated.

Also can follow my thread below-

https://forums.sdn.sap.com/click.jspa?searchID=14102670&messageID=4712660

Hope it Helps

Chetan

@CP..

Former Member
0 Kudos

Hi Prasad,

Query performance will depend on many factors like

1. Aggregates

2. Compression of requests

3. Query read mode setting

4. Cache memory setting

5. By Creating BI Accelerator Indexes on Infocubes

6. Indexes

Proposing aggregates to improve query performance:

First try to execute the query in RSRT on which u required to build aggregates. Check how much time it is taking to execute.....and whether it is required to build aggregate on this querry?? To get this information, Goto SE11> Give tabl name RSDDSTAT_DM in BI7.0 or RSDDSTAT in BW3.x.> Disply -> Contnts-> Give from date and to date values as today, user name as Ur user name, and give the query name

--> execute.

Now u'll get a list with fields like Object anme(Report anme), Time read, Infoprovider name(Multiprovider), Partprovider name (Cube), Aggregate name... etc. If the time read is less than 100,000,000 (100 sec) is acceptable. If the time read is more than 100 sec then it is recommended to create Aggregates for that query to increase performance. Keep in mind this time read.

Again goto RSRT> Give query name> Execute+Debug-->

A popup will come in that select the check box display aggregates found--> continue. If any aggregates or exist for that

query it will display first if u press on continue button, it will display from which cube which fields are coming it will display...try to copy this list of objects on which aggregate can be created into one text file...

then select that particular cube in RSA1>context>Maintain Aggregates-> Create by own> click on create aggregate button on top left side> Give discription of the aggregate>continue> take first object from list and fclick on find button in aggregates creation screen> give the object name and search... drag and drop that object into aggregate name right side (Drag and drop all the fields like this into aggregate).---->

Activate the aggregate--> it will take some time once the activation finishes --> make sure that aggregate is in switch on mode.

Try to xecute the query from RSRT again and find out the time read and compare this with first time read. If it is less tahn first time read then u can propose this aggregate to incraese the performance of the query.

I hope this will help u... go through the below links to know about aggregates more clear.

http://help.sap.com/saphelp_nw04s/helpdata/en/10/244538780fc80de10000009b38f842/frameset.htm

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/3f66ba90-0201-0010-ac8d-b61d8fd9...

Follow this thread for creation of BIA Indexes:

Hopr this helps...

Regards,

Ramki.

Former Member
0 Kudos

Hi,

Your user exit variable might be the one causing the problem. You can run the query in debug mode through RSRT to find out what the cause of the poor performance is.

Also compressing the cube would help with the query performance.

Look at the presentation below for more tips on performance tuning.

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/cccad390-0201-0010-5093-fd9ec815...

Cheers,

Kedar