Strategies to reduce paginated report initial load time?

[MarkPalmberg]

I have a paginated report in our premium tenant that takes ~75 seconds to load the parameters header. Once the report finally loads, it executes fairly quickly. I've set default values for all my parameters and have employed other methods to tweak performance of the RDL and its underlying proc, but the report load in the service remains an issue.

Is there some setting in our tenant I can look at to try to reduce this load time? 

Any feedback is very much appreciated. TIA.

EDIT: The proc is running against our Azure SQL server, if that additional information is useful.

[MarkPalmberg]

Thanks, @Poojara_D12 , @lbendlin and @GilbertQ . I just got done re-writing my proc from the ground up. It executes in my test environment in ~8 seconds, which is pretty fast for our test server.

I'm wondering now if what I'm seeing is an issue with our Azure VNet. We're using a data gateway in the service to connect to our Azure SQL servers because we've implemented a VNet, and I guess it's possible that there's some sort of authentication handshake happening there on RDL load that's responsible for the performance hit we're seeing...?

[GilbertQ]

Hi @MarkPalmberg 

I certainly think going via the vnet gateway definitely adds more complexity and time to get to where it needs to get to. So that could certainly be why it is taking a lot longer to render.

[v-menakakota]

Hi @MarkPalmberg ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Here are few suggestions to reduce the load time of your paginated report:

Simplify the report layout and minimize the number of parameters to improve initial load time.
Optimize queries by using indexes and maintaining up-to-date statistics to enhance data retrieval efficiency from your Azure SQL Server.

Here is the documentation link that may help to resolve your query:
Paginated reports in the Power BI service - Power BI | Microsoft Learn

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Regards,
Menaka

[v-menakakota]

Hi @MarkPalmberg ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Regards,
Menaka.

[v-menakakota]

Hi @MarkPalmberg ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Poojara_D12 · Accepted solution]

Hi @MarkPalmberg 

The slow load time of your paginated report in Power BI Service (75 seconds to load parameters) may be due to slow-performing SQL queries in the stored procedure or dynamic parameter queries fetching large datasets. To improve performance, optimize the stored procedure by reviewing indexing and query execution on Azure SQL Server. Limit dynamic parameters or use static pre-populated values to reduce load time. Ensure your Azure SQL Server is using a high-performance tier, and enable data caching in Power BI Premium for quicker report generation. Reducing the number of parameters and checking Power BI's premium capacity settings for potential bottlenecks will also help. Ensure that both Azure SQL Server and Power BI Service are in the same region to minimize network latency. Regularly monitor resource usage with Azure Monitor to detect any issues.

 

[lbendlin]

Did you examine the queries that run against your database?  Are these queries covered by indexes and up to date statistics?

[MarkPalmberg]

We continue to poke at this issue. An extremely common reporting scenario around here (and all over, I presume) is to have a stored proc that returns a list of financial transactions by transaction date and a hierarchy of designation "levels," like College, Department, Area. So we need datasets in the RDL for the designation level values to souce those parameter values, and they're dependent on each other (Department looks up to College; Area looks up to Department). I guess right now we're at the stage where we're experimenting to find the fastest way to run these parameter datasets. 

[GilbertQ]

Hi @MarkPalmberg 

What would happen if you had to remove the parameters from your header just to see if that is where the actual issue is? Because if it is, then additional way to start working to see how to reduce that. It could potentially be that you've got to use nested parameters so that one will be through to the other parameter instead of one large or long query running?