BusinessObjects Board

SQL Query running forever in BO while it runs in SQL developer Client and Power BI in 2 sec

building reports webintelligence
#1

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

DATABASE : DB2

image
image724Ă—391 43.1 KB

image
image746Ă—313 59.5 KB

#2

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.

#3

Both PROD and UAT has same connections, we are using JDBC.

I have tired on Oracle SQL developer client and PowerBI client, the query runs in 2 to 3secs

UAT BO server is being connected to UAT DB2 database, it cannot be connected to PROD Db, because of company restrictions

DBA on long weekend, will return on Tuesday, I have opened a ticket with the DBA team to check on the performance on query run time of BO

its actually 1 table, even if you bring 1 column it takes forever.

i dont think its a network issue, as all other databases oracle, sql server and even the table in db2 under the same schema (PARTNER) running smooth.

Query running in non bo tool takes like 2 to 3 secs , same query taking like 12min+ to run in BO.

Is there any performance tool you recommenced that I can install in our BO server and it can maybe explain what is causing this much delay ?

#4

check the execution plan, seems like is not the same.

#5

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.

#6

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
image

but the same query I ran in Webi takes like 13 mins
image

.

#7

have opened a ticket with the DBA, but im sure he will say how come that table running smooth in PowerBI and not in BO

#8

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.

1 Like
#9

Using JDBC connection for DB2 DB.

for testing I even ran a report using FHSQL and just run the report with no variables or anything.

#10

Are you using the same connection method for both Webi and SQL Dev?

Are you able to post the query that Webi generates?

#11

same JDBC connection…

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

#12

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.

#13

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 ?

#14

If the client tools aren’t running they don’t consume memory, so deinstalling them will not help you.

The might be one other point to check and that is the connection parameters. Fetch size is something your can change

#15

Nothing is checked in the Summary Tab of the Universe

image
image1086Ă—472 33 KB

These are the parameters, do you think anything needs to be changed ?

image
image1090Ă—630 80.7 KB

And these are the same settings in our PROD server as well

Also for the APS I have this AdaptiveProcessingServer_dump_@PID.log" -Xms512m -Xmx2g

#16

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.

#17

When you say Connection Parameters, where do I see them, I am using JDBC connection

#18

On the second screen of your JDBC connection, you can adjust the Array Fetch Size.
image

#19

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

#20

I remember a default Fetch Size of 1000 !
It is the number of rows returned with every DB-roundtrip.