Hi all,
I have performance issue in executing a query and returning the result set. While executing the query in toad, i get first 500 rows within 10 seconds. But to return the entire result set it takes minutes together.
Now, Is there an option to make BO to fetch these first 500 rows and display them in the report, than waiting for the entire result set instead?
I have tried changing the array fetch size to 500 and array bind size (to 1000) in universe (not sure if this is the right approach) but no luck.
I join 7 tables and all have 10 millions of records. And at the end of joins and filtering, i get 1.5 million of records.
I have tried creating indexes, forced db to use indexes, tried changing the join fashions. At end of all these i was able to just see the first set of rows.
select /*+ FIRST_ROWS(1) */
col1,col2…
from
table1,table2.
This Hint will fetch first 500 rows very fast and will take time to fetch the rest ,I think for your req try this one.(Use custon SQL in Webi with hint and if it works encorporate in universe)
Note : Hint has to be after select and before column and please use the exact syntax else it will ignore the hint.
Yes, but you should avoid hints wherever possible…
You can also create a universe object containing this, you should use it as the first column in the Select, in the query panel.
As more data is loaded in and the database is maintained hints can actually cause the queries to run slower. What runs quick today, won’t necessarily run quick over time.
OK, Thanks Mark.
One of my Webi report runs faster with Hints. We are just returning yearly data. With out this hint the query runs forever.
I think as per your suggestion its better to resolve it in the database level rather than using the Hints. A temporary solution with Hints only for today for my users, thats all.
Thanks…
Just an update. My universe designer has told me that he has added the Set Table count some where in the Edit connection in the universe itself.
That means every query will run faster.
Is it advisable?
Thanks…
You are correct. If more queries run, then the database chokes up and it affects all queries which are running against the database.
Now we had to remove the hints from database, universe and also from the reports. The set table count utilizes almost every resources of the database.
Thanks.
Toad returns the first 500 rows by default, it can do this because it doesn’t know what you want to do with the data so it doesn’t need to fetch the entire result set before it displays data.
BO on the other hand needs the entire resultset before it can begin to create the report. It will begin to build the cube for the report as it fetches data but it cannot actually create the report until it has all the data.
You cannot compare performance in Toad and performance in BO, too many differences in what is happening.
(BOB member since 2005-01-06)
.
(BOB member since 2007-06-25)