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

[usman1158]

Attached is the screenshot showing all current running servers and APS services

By concern is why would a query that takes 12 sec to run on SQL Developer client and Power Bi would need extra RAM. I mean I understand if the query was taking like 5 to 10 mins to runs at the backend and then we might think of increasing RAM. But just for 1 query, especially even we add 2 columns runs for like 10 to 12mins and then those columns takes like 10 to 12 secs to run at backend,

aps-services.jpg

capture.png

[DellSC]

16 GB RAM is bare bones minimum for the system, it works better with 32 GB or more depending on the workload.

Is the DSL Bridge Service running in the AdaptiveProcessingServer (APS) as well or did you split that into multiple servers, which is the best practice.

Also, if you're running only Webi reports, here are the servers you need to have running:

CentralManagementServer
InputFileRepositoryServer
OutputFileRepositoryServer
WebiProcessingServer
AdaptiveJobServer
ConnectionServer
ConnectionServer32 (only if you have a database that requires a 32-bit connection
APS_Webi (split from the AdaptiveProcessingServer) contains the following
Custom Data Access Service
Document Recovery Service
Excel Data Access Service
Rebean Service
VisualizationService
Web Intelligence Monitoring Service
TraceLogService (will be added automatically.)
APS_Core (split from the AdaptiveProcessingServer)
Monitoring Service
Platform Search Service
Promotion Management Service
Publishing Service (If you're using Publications)
Pubishing Post Processing Service (If you're using Publications)
Version Management Service
Visual Difference Service
Client Auditing Proxy Service
TraceLogService (will be added automatically.)
Event Server (If you're scheduling based on events)

You don't need any of the Crystal Reports services unless you're running Crystal Reports.

You don't need the Data Federation Services unless you're using multi-source universes.

You don't need any Analysis Services unless you're using cubes as data sources.

By shutting down the servers/services you're not using, you'll free up RAM.

I believe the issues you're running into are due to a lack of RAM and the memory/processing overhead from the services that are needed to run queries on the server.

-Dell

[DellSC]

If you're running this in Webi, you need to look at the formulas/variables that are included in the report. The time it takes for the report to appear when refreshing it is not just a matter of how long it takes the query to run. It also includes how long it takes for the report to render. This includes things like how many rows of data are in the report, complex formulas and whether you're calculating the total number of pages - in which case it has to render ALL of the pages before it can give a page count instead of just rendering the first page and showing it.

Also note that if there are a lot of records, SQL Developer will just return the first 1,000 or so and then re-query the database if you want more than that. A better way to determine how long the query runs is to run it and then use CTRL-End to go to the last "page" of data so that you know you're loading all of the records.

-Dell

[usman1158]

thanks for your reply Dell,

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 there are no variables or anything heavy in the report. I created a Blank report with just this table

Please see attached screenshot of both run time of SQL Developer and Webi timing

capture.jpg

[DellSC]

I should mention that with BOBJ 4.2 and newer, SAP no longer supports having the full suite and the Client Tools installed on the same server for many reasons. In particular, there is memory (RAM) overhead for both the server processes and the client tools. So, running a query in the IDT, UDT, or Rich Client on the same machine where the server suite is installed can be slower as the resources compete for memory. Remember that IDT, UDT, Rich Client, and Webi throug BI Launchpad are not hitting the database directly. They go through the Connection Server and DSL_Bridge Server to query the database. So, it's not the same pro

Which version of BOBJ are you running and how much RAM is on the server where your system is installed? Did you split the Adaptive Processing Server up into multiple servers? If so, how much heap do you have allocated to the DSL Bridge Service? (-Xmx command line parameter).

-Dell

[usman1158]

Do have 2 APS. 16 GB RAM

-Xms512m -Xmx2g

We have total of like 42 reports and 5 universes in the whole server. Only Admin has access to this server, no other users access this BO server.

We had some issues with the following error and we applied the solution as well by adding DSL_Bridge

2975976 - Got error " [error.repositoryServiceCriterionSerializationFailed] 0] (Error: INF) " when refreshing webi report with prompt based on unv file Version 2 Type SAP Knowledge Base Article Language English Master Language English Release Status Released to Customer Category Problem Component BI-RA-WBI (Web Intelligence) Released On 02.10.2020

Please find the original document at https://launchpad.support.sap.com/#/notes/2975976 Symptom • Could not refresh webi report with prompt based on unv file. • Error message : " [error.repositoryServiceCriterionSerializationFailed] 0] (Error: INF) ". Environment BusinessObjects BI Platform 4.2 SP8 Reproducing the Issue Refresh migrated webi report from 4.1 server which has prompt and created by unv file from BI Launchpad. 1. Or try to refresh new created webi report with prompt based on unv file from BI Launchpad. >>Got above error message. 2. Cause

The issue is caused by DSL Bridge service missing on Adaptive Processing Server. Due to design change, processing webi report with prompt created by unv file needs to involve DSL Bridge Service. So the service needs to be added on Adaptive Processing Server.

Resolution 1. Go to CMC > Servers. Stop existing Adaptive Processing Server. 2. Right click Adaptive Processing Server > Select service and add DSL Bridge Service. Start server. ***In case you are using cluster environment, make sure each node has available DSL Bridge Service***