I am developing a WebI report and as I add objects to the report, the report took lot of time to fetch the data from the database.
Then to make sure the problem on the back end I ran the original query from the BO and I had the same problem.
So finally I removed the date condition from the query and the data was coming in the backend. But I need the date condition in the report. What would be solution on the report Side?
On the report side? Nothing. You need to optimize the query on the database side. Check with your DBA why the query is slow when there is a condition with the date. He/she should be able to optimize the performance by creating a new index or collecting the latest statistics for tables/indexes or by partitioning tables…
Thanks Marek for your suggestions. I had that in my mind.
In addition if I add sum(measure) in the query also the same problem.
So for me inorder to get the data on the backend I had to remove the date condition and also I had to remove the Sum and in addition to that I had to remove the group by as well.
Would breaking the queries in the report side would help? Any other suggestions Marek?
You are going in a very wrong direction. This way you are not going to retrieve less and aggregated data but raw detailed data - which can potentially be millions of rows - into the WebI document. Then the usage of such document will be very slow if even not possible at all.
As I said, you need to optimize the query on the DB level and then bring as few rows as possible (aggregated data) into the WebI document.
Is there some reason you don’t want to talk to the dba? There is nothing in the report or the universe that you can do to make a query that is slow on the backend run faster. You can perhaps break it up, but if there isn’t an index on the date field you want to use it is going to do a full table scan and be slow anyway. If your joins are correct and complete in the universe, you will be at the whim of the database engine.
Before talking to DBA I am trying to find what is actually causing the issue.
Further investigation on my side, I found that it is not the date issue (I believe).
A same query runs fine with all condition and objects. At the same query I replaced another measure object and then the query becomes sluggish.
I have access to database. So can you guide me on what steps I can take further. Help on this will be greatly appreciated.
I meant sluggish to be slow.
You know what, if I run the query in Deski I can get the data in 5 mins , but if I run the query in Webi it takes forever.
Check the installed version of the database middleware on your BOE servers, including the patch level. Against which DBMS (IBM UDB, Oracle, etc.) are you reporting?
Run the SQL query directly from your BOE server using the DB middleware (not via Webi!).
How many rows is that problematic query returning?
Can you post the SQL code generated bei Webi (hoping it is not 100 lines of code…)?
What is the version and SP of your BOE server installation?
Note: Every single measure should use a SQL aggregate function as a rule of thumb, or it is not a measure. See here for more details about measures, SQL aggregate functions, and projections of measures.
I am reporting off from Sybase.
When I run the query against the database directly from my machine it takes 20 mins and returns 11663 rows.
When I run the same query in Deski it fetches and runs just 2 mins and returns 2223 rows - I don’t know why the query ran against Database returns 11663 rows and in Deski 2223 rows.
Do you have any thoughts on this? I am testing more on this by running in Webi and I think in Webi it will take 20 mins or more.
What would be the issue if it runs good on Deski and not on Webi
One more thing is I am changing the report query in Webi (just modifying one code) and saving that and it takes forever to get validate and come back.
can you sense where the problem woule be? If you need more information, please let me know.
(BOB member since 2007-06-25)
(BOB member since 2003-11-27)
(BOB member since 2002-06-20)