Separate Universe?

[juliam921]

Would creating a separate universe for a complex report speed up the processing time? If not, any ideas on what would?

 

[jimbopalmer]

If the joins in the current universe are wrong, then a correct universe would help, but the joins are the item to watch.

Look for tables only included to make a join between two tables you do need, shortcut joins can help here.

Par.part = Stock.part
and stock.part = Transaction.part

If this is the only reference to the table stock, we can do a shortcut join

Look for outer joins where the child has qualifiers on it, they do not need to be outer joins.

As an Oracle example

Part.part = transaction.part(+)
and Transaction.type = 'Issue'

this does not need to be an outer join

an oracle specific tip is to use optimiser hints

select /*+ all_rows */ Part.part,
Part.desc,
Transaction.date,

you can embed optimiser hints in the object definitions, so they always happen
I tried to remain child-like, all I acheived was childish.

 

[blom0344]

Hello Juliam,

It all depends on what you mean with "complex report". If it is the SQL to the database that is slow in the retrieval of your data (which you can check with "view data&quot it could be (literally! COULD be) improved with a better fitting universe, or adding optimiser hints to the SQL (ORACLE). However if one or more conditions addresses a non-indexed field, performance can be quite slow or even non-existent. (great tool is TOAD for ORACLE which gives you explain plan facility to look at the path the SQL takes).

Sometimes, adding aggregate-tables (datawarehousing) can (greatly) improve report performance, cause aggregation has already been performed at database-level, giving you faster access to data.

However, there is a second level to report performance, starting immediately after query completes. At client level calculations and formatting have to be performed. Here is were client CPU really matters. We did some elaborate performance tests (actually to get new PC's!) years ago, which showed that for a very complex report upgrading from 133 to 350 Mhz gave a 5-fold faster performance. What certainly matters are calculations with 'nested' variables (variables which use other variables etc , etc) or applying lots of filters in reports consisting of multiple tables.

And even then some complex reports took ages to format , other complex ones were quite fast. This is something where documentation seems non-existent. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[AndyEdwards]

One check would be to refesh the report and check the refresh time of each of your dataproviders, see if it is retrieving the data that is slowing down your report or the calculations done on the report side. This will enable you to know whether the efficiency improvements need to be made in the universe or the report.

Andy