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

[usman1158]

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

Version: 14.2.8.3642

DATABASE : DB2

fhsql-based-query.jpg universe-based-query.jpg

[usman1158]

Increasing the Fetch size in the universe connection parameters from 10 to 30 reduced the run time to 4mins and then increassing the size to 60 brought it down 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