I am querying a table which has 13 million record and my query returns almost all the records.I have indexes and all the required performance enhancers in the Oracle 8i database.
The concern is that I kicked the report off at 2pm yesterday and when I came in this morning the report still appeared to be refreshing - meaning that when I clicked on the BO application it didn’t respond.
I killed the BO application at 7am and when I reopened the report appeared to have refreshed 40 mins( this information I got it from the statistics in the DATAMAP ) after we kicked it off…
why did BO still look as though it was busy for 17 hours when it was done after 40mins?
second one
Is there anyway in BO to use Hints ( which is available in oracle 8i) to force it to use the indexes which has been created
I’m sure your not pulling all 13M rows into your report
But if by chance you were to pull 13M rows into a report it could potentially take this long for BO to format the report and screen refresh each tim you open it up.
Seriously, does this happen frequently or is this just a one off?
Let’s be realistic here. If you were really pulling 13 Million rows (or even 5 million rows), it likely would never complete. The query results would return to you machine. Unfortunately, BO loads the whole cube into memory. As a result you would end up in a swapping situation which would never be able to resolve.
Have you built a proper universe? With Sums on all your measures in the select statement.
If you really need all 13 M rows, you’ve chosen the wrong product. You need a datamart, not a reporting tool.
Just had an ‘interesting’ thought on this and I’ll see when I get home tonight,
If at the universe level you restrict a Key field object (say Customer ID) so it can’t be used as a result object, can you still use it as a result object in a subquery?
I’ve funny feeling that you can’t and its the only time I can think that you would want to use an object that would return such a large number of rows?
Forget BO. First, try and run the query directly on the database e.g. using TOAD. Does that query finish? If not then there is no point running it in BusinessObjects until you improve it. Any you may have got the general idea that people here think maybe less rows would be a good place to start
To answer all your questions ,the data in the table is 13 million,but the report displays only 1 million records.
And this query works well with business objects,toad and sqlplus.The issue is with the time taken by BO to display the report.
and for your information ,I didnt have any report variables,complex calculation, etc., except for a sum total at the end of all the records.So I removed this calculation after the report display was taking 6 hrs( earlier it was 7 hrs)But the BO statistics was still showing it to be 40 mins .Also my report objects where so simple .
Any inputs for why it should take 6-7 hrs to display ,when it had actually taken 40mins to execute in the database.
Or can it be related to network issues or m/c cappability .I ran this report in a 512MB RAM.
Also I would like to know whether this BO statistics is reliable.Or it messes up with the statistics details
(BOB member since 2002-06-20)
(BOB member since 2004-01-29)
(BOB member since 2002-06-25)
(BOB member since 2002-08-15)