Slow performance of Direct Query mode in a power BI desktop and Service

[PBILover]

I've created a Power BI report in which the user inputs their ID, and I retrieve the information related to this id from Synapse using a direct query method(dynamic parameter concept). I also used the direct query to build the id table as a parameter table where user can search the id. The performance of this report in a Power BI service is really poor. How can I make this query perform better? Is there a way to achieve this?

Thanks.

[Poojara_D12]

Hi @PBILover 

The poor performance of your Power BI report in Power BI Service is primarily due to the DirectQuery mode and the dynamic parameter filtering approach, which sends queries to Synapse every time a user searches or selects an ID. Since DirectQuery does not store data locally, it relies on real-time query execution against the database, leading to delays if the underlying database is not optimized or if frequent queries are being triggered. Additionally, if the ID table used for searching is large, every keystroke or selection can result in multiple queries hitting Synapse, further slowing down performance. To improve efficiency, you can optimize the database by indexing the ID column, using materialized views, or pre-aggregating frequently accessed data. Instead of dynamically retrieving the entire ID table, consider using Import mode or Hybrid Tables for storing the ID list locally while keeping the detailed data in DirectQuery. Within Power BI, enabling query reduction settings, such as disabling auto-apply filters, can help reduce unnecessary queries. Another approach is to move more filtering logic to Synapse by using optimized stored procedures or server-side parameterized queries to return only the necessary data. If performance remains an issue, you can explore Azure Analysis Services or Power BI Dataflows to preprocess and store commonly accessed data, reducing the reliance on DirectQuery while maintaining a near real-time experience.

[v-sgandrathi]

Hi @PBILover,

Thanks for connecting with the Microsoft Fabric Community Forum.

Here are some points that could work for you:
Open Performance Analyzer in Power BI Desktop, start recording, and interact with the report to find slow queries. If query execution is slow, the issue might be on the Synapse side.

Optimize Synapse queries by indexing the ID column, using partitioning, and leveraging Materialized Views for precomputed results. Avoid complex joins in DirectQuery and push transformations to Synapse SQL Views.

If the performance issue continues, consider using a Hybrid Model (Composite Mode) where frequent queries use Import Mode and only real-time queries use DirectQuery.

Below is the Microsoft Official Document on DirectQuery optimization which you can refer to:
DirectQuery optimization scenarios with the Optimize ribbon in Power BI Desktop - Power BI | Microso...

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

Best Regards,
Sahasra.

[PBILover]

Thanks @v-sgandrathi 
I will try to use the hybrid model, let me see if it helps.

[v-sgandrathi · Accepted solution]

Hi @PBILover,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Thank you for being a part of the Microsoft Fabric Community.

[GilbertQ]

Hi @PBILover 

What you could do is to test this out using Power BI desktop and use performance and laser to see where the longest query duration is. My assumption is going to be that it is due to your synapse that is taking a long time to find the single user ID and whatever other attributes they are in this table, but that should give you a good starting point to find out where the bottleneck is.

[PBILover]

Thanks @GilbertQ .

I am also assuming the same. It is a sql query which is bind with dynamic parameters. There is no complex calculation in the query but datasource is a large, which takes time to search that particular id in a database.