My DBA tells me that the Query Execution Time is affected by the order of the tables. Putting the table with the least number of rows in the last ,makes it the driving table and there by reduces the Query time.
Has anybody seen such things and if so how much performance do you gain in BO .
Also is there a way to control the order in which the tables appear in the query but for the order in which you drag the objects in the Query Panel.(because you cannot teach the users to drag the objects in a particular order since that is against the very fundamental objective of trying to hide the database details from the user:-))
Hi Guna,
The aggregate aware function in Designer is used to do just this. For your measures you specify which aggregate tables get which priority and this will drive the generation of the SQL statements. If it was not for the aggregate aware function, many people would not be using Business Objects! Good Luck,
Simon
Has anybody seen such things and if so how much performance do you gain in BO .
Also is there a way to control the order in which the tables appear in the query but for the order in which you drag the objects in the Query Panel.
In my experience (Oracle 7 & 8 databases), query performance can be enhanced by altering the order of tables in your query. I’ve forced this in the join order during universe creation. There may be other ways of accomplishing this as well.
Business Objects optimizes the query that is being generated for performance. One of the ways it does this is to put the tables in the correct order in the SQL statement. BO will place the table with the largest row count first in the FROM clause. I believe for BO to do this, you must get the number of rows for each table in universe (Right click on structure view in designer and Select Get number of Rows). You can change the order of tables in the SQL statement, if you need to, by manually setting the number of rows for a particular table. The order of tables in the from clause will not affected by the order of the objects in the Query Panel.
Make sure that the Paremeter REVERSE_TABLE_WEIGHT is Y in the appropriate .PRM file. Most verions after 4.1.3 had this after the default. However, if you upgrade, your .PRM settings may not have been upgraded.
Hi Guna,
The aggregate aware function in Designer is used to do just this. For your
measures you specify which aggregate tables get which priority and this will
drive the generation of the SQL statements. If it was not for the aggregate
aware function, many people would not be using Business Objects!
Apologies Simon, but I have to disagree. BO organises tables in the sql statement based upon row count, which can be determined from the database, or alternatively, manually (both achieved using the designer module). The aggregate aware function is used to determine which tables are included in the query and not the order they appear in the actual sql statement sent to the database.
No apology necessary from yourself Brian. It was I that did not read the email properly and hastily fired off a response. Sorry for any confusion.
Simon
I’m no DBA, only a designer that has been involved in performance tuning (Oracle Database), however I have picked up this much
Word of warning, Oracle can optimise SQL in a number of way Ordered is just one of many (and may not neccessarily be the best one for you’re set up) others include :
Parrellel
Start Scheam
Star Transformation
First rows
factors such as
Indexes (including how you create them - bit map, concatinated, etc) Partitions
Analysis (a job that gathers statistics about tables, could be as simple as Row counts for tables, in more complex conditions % histograms about distribution of vaules within key indexes)
Predicate weightings (the more where conditions you load onto a table the more weighting the optimiser puts on the table) Hints (Business objects can put a single hint on Webi Queries, not sure if the same is true for Full client)
All impact the way the Oracle Optimiser will transform a simple SQL statement
As long as the DBA is happy that “ordered” transformations give the best results in all cases (or even 80%) go for it.
Just be aware of what you may be losing.
Using a standard “Hint” might help things along (there’s an .sbo to modify in Webi)