Webi Rich Client refresh is very slow contrary to Webi and SQL

Question

I have a single Universe query that has 4-5 filters that takes almost 5 minutes to run using the Webi rich client. When I copy the SQL code and run it from SQL management studio (SSMS) it takes 10 seconds. I have created only the data query and don't have any reports or variables. When I run the query using the Webi HTML, it also runs in 10 seconds.

Both SSMS and Webi return 110,000 rows. If I stop the Webi query after about 20 seconds, it only returned 5000 rows, so it's not finishing and then getting hung up.

If I replace the Webi Universe query with a stored procedure (FHSQL) using the same SQL code, it takes 80 seconds. There are query filters in place. Without the Where clauses, SMSS takes 65 seconds to return 990,000 rows

                 Filtered    All_Records
   # of Rows:     110,000      990,000
   --------------------------------------------
   SQL (SSMS):    10 sec       65 sec
   Webi HTML:     10 sec
   Stored Proc:   80 sec
   Rich client:  270 sec

Just the rich client is slow, but much more than would be expected.

Are you absolutely certain that all of the rows are retrieved in SSMS? How many rows are you fetching? Also, have a look at universe parameters such as Array Fetch Size and Array Bind Size which can adversely affect performance when badly configured. Try monitoring the database and check if WRS is using those 5 minutes to retrieve data or if it is doing something else. Used E2E tracing to find out what's happening behind the scenes. — ƘɌỈSƬƠƑ

Vimal Jose Vimal Jose · Accepted Answer · 2016-12-01T11:28:03

This is mostly because of non-tuned Array Fetch Size and Array Bind Size. (You can find them in the universe parameters.) The easiest way to go about would be:

Identify 2-3 reports which retrieves a considerable no of rows.
Record their execution times (probably you can use scheduling).
Increment the parameters, majorly the Array Fetch Size, by steps of 50
Check the execution times again.
Based on the performance gain/loss fine-tune the parameters.

Webi Rich Client refresh is very slow contrary to Webi and SQL

2 Answers