I am having a weird issue in our UAT environment. There is a specific table that when we run in ether BI Launchpad, Universe, Rich Client, it runs forever.The same query when we run in SQL developer Client or even in Power BI runs fine, The query also runs fine in PROD server and the table has like only 150k rows.
Till now I have tired creating new connections, new universe and even tried to run the query as a FHSQL, but it just runs forever. I have tired to restart the entire server as well, clear cache ( under catlina folder).
I have also talked to the DBA and he says the query just runs fine at their end as well.
Ofc when I add the filter the data comes back, but that too it takes like alot of time. Like without filter the results is coming back in Powerbi and SQL Developer tool in like 2 secs, and this UAT BO env it runs forever.
attaching the screenhots.
SAP BusinessObjects BI Platform 4.2 Support Pack 8 Patch 5
Are the database drivers/client software in your UAT enviro the same as Prod and the correct version for your dbase?
Have you tried running the query on a non-BO tool on the BO server itself to check performance?
Are you connecting to the same dbase and server from both UAT and Prod or are you connecting to a test database? If it’s different dbases, try connecting to prod from UAT and to UAT from prod.
Is the DBA able to see the query when it’s running? Can their tools provide any insight?
Is it just the one query that’s slow or are all queries slow?
All else equal, you may need to get your network team involved and grab packets to rule out a networking issue.
Install SQL Developer client tool on your Webi Server.
Run the SQL on there, so it’s connecting to the target database from the same server as Webi.
Don’t time how long it takes to start returning data - Webi has to return ALL the data before it can render the report. If you’re returning millions of rows, this can lead to false differences in performance because you are timing your SQL tool to just return the first rows from the query.
If there are definite differences, make sure that you are connecting using the same connector type.
If not, then maybe you need to update the connector.
SQL developer is install in the same machine where the clients tool is install, as client tool and BO server suite install in one server.
I have run the query in Power BI and it returns all records in few secs. There is another table as well on the same universe, but that runs flawlessly. Its just this table is causing this issue
Also I ran all records in SQL developer by doing Crtl+A after selecting the result set and it returned all 151k rows in like 12 sec
Are you using a JDBC connection in SQL Dev or the Oracle native connection?
In your WebI report, do you have a lot of complicated formulas or formatting or using auto-width functions that may be slowing things down? WebI has to return the data and do all the post processing before displaying the report.
While the report is running, monitor the session in Oracle to see what’s going on on the database side.
I have 16 GB ram, do you think that could be the issue. As the client and the server both install in the same VM. Someone told me min should be 32 GB. But we have only 42 simple webi reports and 4 universes, i don’t think 16 GB ram is less for that ? Also this query runs in like 12 secs in the SQL Developer client and also in Power BI,
SELECT
RTRIM(PARTN.PART_TB.PARTNER_ID),
PARTN.PART_TB.NAME,
PARTN.PART_TB.FIRST_NAME,
PARTN.PART_TB.LAST_NAME
FROM
PARTN.PART_TB
This can be a memory issue. It could be that the query already returned the data towards SAP BI server, but that the server can’t process the data (due to overhead of Java). Please have a look at the PAM file for the minimum requirements.
thanks, someone else also pointed out in the SAP Blog Platform that 16GB RAM might not be good for SAP 4.2 SP9 and also we have Client and Server installed in the same VM. It seems like maybe increasing the RAM might fix the issue ? or may be shall I go ahead and Delete the Client tool and check ?
That’s the universe parameters. Fetch size is in the connection parameters. Increasing this can speed up your query but will transfer some load to your database so should be tested as you go.
ok thanks for the tip. I changed the fetch size to 30, the query time was reduced to 4mins , and then i did 60 and query time was reduced to 2 mins.
but the question is that we have 10 set in prod, and it runs like in 12 secs, same like its running at SQL developer and PowerBI in UAT. what could be the issue then
