Hi,
I have a report which is generating below query:

 
TABLE FILE ASSET
SUM TOTAL_VALUE_USD AS 'Assets USD'
BY CALCULATION_DATE_ID NOPRINT 
BY TOTAL COMPUTE DISDATE/A20 = EDIT ( CALCULATION_DATE_ID,`$$$$$$99` ) |`/`| EDIT ( CALCULATION_DATE_ID, `$$$$99` ) |`/`| EDIT ( CALCULATION_DATE_ID,`9999` ); AS 'Assets Date'
BY BUSINESS_PRESIDENT AS 'Business Group'
 BY INVESTMENT_ REGION AS 'Asset Region'
BY ASSET_TYPE AS 'Asset Type'
 BY  PROVIDER_PARTNER AS 'Partner'
 BY REPORTING_PRODUCT_ GROUP AS 'Reporting Product Group'
 WHERE REPORTING_PERIOD_ID GE 200601 AND REPORTING_PERIOD_ID LE 201101 
WHERE BUSINESS_CODE LIKE '%' 
WHERE BUSINESS_CODE IN('A2','AT','B2','BE','C1','C2','CH','CL','D2','D3','D6', 'D8','DE','DK','E1','E2','E4','E5','E7','E9','ES','F1','F2','F6','FN', 'FP','FR','G4','G6','I2','I3','I4','I5','I6','IC','IO','IT','J1','J3', 'J5','K2','KR','L2','LX','N2','N6','NA','NL','NO','OF','P2','PT','S2', 'S6','SE','SW','U3','U4','U5','UK','XX' ); WHERE BUSINESS_PRESIDENT_ ROLLUP IN( 'EMF','Institutional' );
 ON TABLE SET BYDISPLAY ON ON
 TABLE HOLD AS EAS_ASSET FORMAT FOCUS END
 

BY and WHERE fields are dynamic based on the selection of filter criterias on the front screen.So number of BY fields and WHERE clause values can be different based on user request.The performance of the report is quite slow.I want to improve the performance.I tried to index one of the BY field CALCULATION_DATE_ID which is static but it is further degrading the performance by bit.Master file ASSET is parent segment with around 15 child segment and SEGTYPE=U.Is there any way to improve performance in this case?Please suggest.

Thanks in advance.

This message has been edited. Last edited by: Kerry,

Well, first of all you should enable traces in WebFOCUS so you can take a look at what SQL statement is being translated by the iWay adapter and passed to your database.

If all joins, aggregations and filters are being passed to be done directly by the database, then you'll have to start looking at your database structures directly as WebFOCUS is "doing it's job".

Generally speaking, indexing a BY field may aid in the sorting portion of the request but won't necessarily speed things up when it comes to data selection.

You'd want to make sure that your WHERE statements are as selective as possible and it's there where I'd see a combination of indexing/table partitioning being of help. Obviously, if your WHERE are as dynamic as you say then it's very difficult to plan for this as you don't know what the final filters will be on.

Looking at your query (without knowin what you have in your other 15 joined segments) I would expect that your table is, at least, range-partitioned on REPORTING_PERIOD_ID.

What's the total number of records for those reporting periods? what's their ratio compared to the number of records matching the BUSINESS_CODE's in your list? If the ratio is "small" (and this is a very subjective measure) an index on BUSINESS_CODE *might* help but once again, this is applicable only in those cases where your users decide to filter on this field but not others.

In summary, my approach would be:

1. Enable traces, capture SQL statement and make sure your joins, where and aggregations are indeed done by Oracle (an "AGGREGATION DONE." message is usually a good indicator). If this is not the case, you'll have to review the while JOIN/TABLE FILE stuff to find out why a complete translation to SQL is not being done.

2. Take that statement and using your favourite Oracle DB explorer tool (SQL Developer, TOAD, SQL*Plus, etc.) analyze the execution plan the optimizer is giving you with different combinations of filters. This may give you an indication of what to improve at the database level ... indices, partitions, materialized views, etc.

You'll definitely have more chances to improve this at the database layer than in WebFOCUS directly unless the trace shows a problem in SQL translation which does not seem to be the case (again, I don't know your other 15 segments in the request).

Cluster Joins may also be an option...

ERINP

Shankar, did you try any of the stuff that was suggested?

Hi,
I tried your suggested techniques.In trace file AGGREGATION DONE was there and oracle is doing its job fine.The queries are running fine at DB side with in desired time.I am in talk with DBA to find some way to achieve better results.I am not very hopeful that something can be done at DB side as suggested by him. Is there any way to improve the performance at Webfocus side?

Thanks.

Usually the biggest bottleneck occurs when querying and fetching data from the DB. If your DBA does not think there's anything else that can be done at that layer to improve retrieval performance, then WebFOCUS will run as fast as the DB allows it to.

You may want to take a look at the iWay adapter for Oracle for some ideas such as changing FETCH size so more records can be read in one single trip to the DB, etc.

Now, if you say that the DB is running "fine", what else are you doing in WF that may be slowing down the overall process, either before or after data retrieval?

I will try these techniques and explore it further.Thanks for the reply.