We have a set of reports on a database that is a nightmare to report from where simply reports can take up to 2 hours to run.
Please could anyone give any thoughts or ideas on how to optimise these reports? i.e. can I re-order the order the tables are queried in (I had wondered if you add a ordered hint object first then brought in the objects running from smallest table to largest table this would work?)? Is there a way to alter the SQL in BO to make performance better? Or does this type of thing involve more work or knowledge from the DBAs?
Also, (not sure if I should have added another topic for this one?) can you add order by, having statements into BO SQL and then not re-generating the SQL?
Any suggestions or advice would be really appreciated.
What type of application are you trying to report against? An OLTP app? A data warehouse? Which database are you reporting against? Are there reports that came with the app that run fine? Are you trying to pull bazillions of rows of data?
Thanks for getting back to me. I don’t have much to do with the databases themselves or the connections as another department does all that. I think it is an Oracle database but basically we have been told that the database is not structured in a way that is easy to report from - every table goes through a standard table (so if you query 2 tables you are actually going through 3, etc), every table joins via 7 joins, and the list goes on…
The reports that have maybe 5 columns and 2 conditions could take up to an hour and are simple reports. The supplier reports are the same, in fact, they have had to hard code some of their reports also when creating BO stuff for us in the past apparently (I am still fairly new to the organisation). So it seems a long-standing issue.
I have heard you can look at the best ways of executing queries, In-Line views, etc but just wondered if you could create oracle hint objects and bring them into queries or if there were other things you can do in Busines Objects/Universe instead of having to go through DBA’s, etc. The reason I ask is that we are fairly strapped for staff and the chaps we do have are just swamped with work, so I would like to find ways to fix things that don’t always have to involve them to save them some time if possible.
A long response but hope it makes sense; thanks for getting back to me again.
Yes, you can. You create them as a dimension object and use them as the first object in the query.
I think you can also assign a generic hint under universe parameters.
You can create a having clause by defining a measure. I would not recommend hacking the BO SQL, its a maintenance overhead.
Ordering of tables in the query should make a difference in a modern database witha cost based rather than rule based optimiser.
To be honest, the best way of speeding up queries is to analyse the queries and apply hardware / software partitioning and or indexing, back to the overworked DBA’s…:).
My experience is that if you want the reporting solutions that you provide to be successful, you need to do some work on the database side. You should get the user name for the connection and its password in a development instance. Using TOAD or SQL*PLUS or something similar, connect to the development instance and look at the indexes of each of your tables. Decide what should be the optimal way for Oracle to execute your simplest query. Then generate an explain plan for your simplest query and compare. Check to see if you are leaving a column out of a join.
That doesn’t really matter. What matters are the indexes and the universe structure.
Hints are the last resort. A hint that makes a query run great today can slow down the same query a year from now as your data changes.
Agreed. I said that you can do this, but I wouldn’t recommend it either.
Most people, far more skilled in databases than me, have always looked at hints as a bit of a fire fighting solution, i.e. not really recommended…:).
(BOB member since 2008-05-12)
(BOB member since 2003-09-17)
(BOB member since 2005-01-06)