Extremely slow paginated reports due to high connection opening cost via On-premises Data Gateway

[samsonfr]

I was wondering why a report that was rendering in 1-2 seconds in Power BI Report Builder was taking 37 seconds when deployed in Power BI Service under a Premium Per User capacity. It was just a test report to reproduce an issue reported by a customer.

Paginated reports seem to suffer from the same performance issue than SSRS when a report generates a lot of small queries due to the cost of opening /closing connections throught the On-Premises Data Gateway ...

I knew that SSRS did not reuse connections accross query execution which increase significantly the execution time when using MDX/DAX over Analysis Services on a different server or when using Azure Analysis Services. But with SQL it was never a problem ... before Paginated Reports that have to go through the On-Premises Data Gateway ...  It's a NO-GO for anything but the simplest report ...

I created 3 small stored procedures returning harcoded data : GetAList, GetADefault and GetData. They take like 1 ms to execute.

I created a paginated report in a way that I knew would generate a lot of small SQL queries : 12 parameters (GetAList, GetADefault) and one table using GetData.

When it executes, even if all parameters use the same datasets for Available Values and Default Value, it is reexecuted again and again using different connections ... The cost of opening those connections is prohibitive and it explains why even if queries are instantaneous, total report time is very high :

Our only solution is to migrate to Azure SQL Database to avoid having to use On-Premises Data Gateway. Our gateway is in the same data region than our Power BI Tenant.

Anyone has a better way? Or a way to force the paginated report to always reuse the same connection for all it's queries?

Thanks,

Frederick

[v-robertq-msft]

Hi, @samsonfr 

According to your description and sample picture, you have finished some detailed tests to figure out the data query mechanism when the pagination report uses the gateway.

As far as I’m concerned, the Power BI report server is similar to SSRS in some ways, and PBIRS can the next version of, or a replacement for SSRS. So it’s they do experience similar performance issues.

Your test results in the SQL profiler can also explain why the query takes so much time when rendered in the Power BI service. Therefore, maybe migrating to Azure SQL Database can be the best solution.

What’s more, you should also guarantee the stability of the network and sufficient computer memory on the gateway computer, which can also be a reason for this problem.

More info about the On-premises data gateway architecture

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samsonfr · Accepted solution]

Thanks for your answer.

I realize that my SQL Server Profiler screen shot is not clearly visible.

I ran it again, and it takes 20 seconds for query execution, even though the duration column is 0. It seems it takes about 500 ms to establish a connection for each query. 

First query StartTime : 07:56:38.227

Last query StartTime : 07:56:58.540

With Azure SQL Database, the same report is much faster (using Azure Data Studio for profiling), 5.36 sec with an average of 130-200 ms in between executions.

First query StartTime : 13:09:09.502

Last query Starttime : 13:09:14.860

Knowing that, I can never recommand to use paginated reports with an on-premises data source using the gateway. Also, an increase in the number of parameters (filters), even if the supporting queries are instaneous will lead to poor performance even with Azure Database because of the 130-200 ms connection opening tax (260-400 ms : available values + default value). I will let you know if a future update mitigates this ...

I there was an option in paginated reports designer to indicate that all queries should be executed serially using the same connection (no open/close), this report would be much faster ...

Best regards,

Frederick