Question Re: Query Optimization using Aggregates in Technology Q&A

Query Optimization using Aggregates

Former Member — Wed, 07 Jan 2015 14:14:56 GMT

Hi Folks

We have a performance tunning activity in which we need to optimize the query running time using aaggregates. The aggregates are already present in the system. I need to find to find out what more aggregates can i create or delete the existing non-usable aggregate.

I have taken the list of the long running query and there multiprovider from the t-code st03.

Can you please guide me how to procede forward in this regard.

Regards

Nitin

Re: Query Optimization using Aggregates

KodandaPani_KV — Wed, 07 Jan 2015 15:24:07 GMT

Hi,

you need to find the trace in ST05.

first activate the trace -> then execute the query at RSRT -> after query exection compeleted deactive the trace -> now read the select statement.

based on the select statement you will decide the which object required for the aggegates ot not.

plase read the document for more detail.

Thanks,

Phani.

Re: Query Optimization using Aggregates

Former Member — Wed, 07 Jan 2015 16:16:48 GMT

Hi pani,

thanks for reply, apart from running the SQL trace, is there any other way to identify the object to be used for aggregate.

Nitin....

Re: Query Optimization using Aggregates

chanda — Thu, 08 Jan 2015 02:51:21 GMT

Hi Nitin,

Firstly is check if your query is hitting the aggregates or not based on your input .

Even though aggregates exist, it is not necessary that they being hit.

If you do not know how to check it below is the procedure.

RSRT--> provide query name --> click on Execute & debug--> Select Display Aggregate Found ( Under AGGS). then execute.

You will see which aggregate on which cube have been hit.

If for any cube it does not hit, you can take screen shot and create an aggregate based on the characteristics shown .

Hope it helps,

Regards,

Sudhir

Re: Query Optimization using Aggregates

Former Member — Thu, 08 Jan 2015 05:39:26 GMT

Hi nitin,

First make sure that the Aggregates are being used during Query Execution...Aggegrates may be present but they may not be used...there wud nt be any hit to it.

This can be checked using RSRT + Execute +Debug with Display Aggregates.

rgds

SVU

Re: Query Optimization using Aggregates

KodandaPani_KV — Thu, 08 Jan 2015 07:07:23 GMT

Hi,

you need to check the aggregates being used or not in RSRT + Execute +Debug

and check the aggregates panle - selec the cube -> right clcik -> select the maintain aggregates.

now you will find the column as USED - then it says last used aggregates for the cube.

Thanks,

Phani.

Re: Query Optimization using Aggregates

Former Member — Thu, 15 Jan 2015 11:46:05 GMT

Hi pani,

I ran the SQL trace, but output came with SQL program and /BIC/E* table. Is it possible that the available aggregates are not being hit at all.

Regards

Nitin

Re: Query Optimization using Aggregates

Former Member — Thu, 15 Jan 2015 12:23:57 GMT

Hi Chandra,

Thanks for ur reply, as i went to execute+debug and selected the display aggregate and a list appear ..

I can see a list of objects that are present under maintain aggregate of infocube and some other object is also displayed.

do i need to build aggregate on these "other objects".

Also Can you please elaborate on your statment:

If for any cube it does not hit, you can take screen shot and create an aggregate based on the characteristics shown ."