Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RKM
Helper IV
Helper IV

Directquery Performance And Each visual executing query in SQL Server - Any ways to improve?

I want to understand this scenario a little better from you experts...

I have a simple dashboard with 3 tables. 2 out of those 3 are hardcoded value tables [Enter Data in Power Bi] and having 4 and 6 rows respectively, with 2 columns in each. Finally last table is from a SQL query that is used as "DirectQuery" to SQL Server 2016.

This query is on a SQL Server View and then having a where clause [Where ExecutionDate = Cast(Getdate() as Date)].

In SSMS this runs in 0 sec (ok.. less than 1 sec.).

 

MeterialDelivery [DirectQuery From SQL View and a Where clause Date Filter] - Every time returns 120 Rows for that day

BoxedBy [Enter Data - Only 4 Names]

ReceivedBy [Enter Data - Only 4 Names]

 

But my power bi performance is horribly slow. When checking performance analyzer as well as queries in SQL Profiler, something that I noticed is -  PBI is pretty much sending 10 queries for my 10 visuals.

~ i.e. For example... 1 visual shows last_Meterial_Arrival_Time (i.e Lastest Arrival_Time). another visual shows number of meterials received already on current day. (count meterial receipt id). etc.

 

Now for each and every such visual - PBI is sending pretty much same query to SQL, where as my expectation was,  that in one round - PBI will send  query 1 time and then it will do derivation for above visuals with in power bi.

 

While I indeed want PBI to fetch real time/latest data from SQL via directquery, I do not want that at same time for each visual it will send similar query in SQL and eventually run 15 - 20 queries in SQL. Is there any way to improve performance?

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

Hi @RKM ,

 

In DirectQuery, whenever you click something in the report, a query is sent to the underlying data source. If you change a slicer, there will be sent a new query for every visual on the current page. You click on a bar on the bar chart, all the visuals send a query to the data source. You deselect the bar chart, all visuals send again a query to the data source.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
RKM
Helper IV
Helper IV

Hi @amitchandak 

Thanks... but no bi-directional join. and Index where? Its a view.

@RKM , If you have a few view in the SQL server, You need to check it is on how many tables. Those tables have indexes in the join column.

Also, need to check how complex the view is

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak 

It is just one view, using 4 tables. View complexity is moderate.

But above all as I said, when running the source query in SQL - it is within fraction of second rerturnin result.

In Power Bi its slow because power bi not executing that one query one time, rather for each visual it is executing that query. That's where I am having a problem.

If I convert everything to import mode, then my PBI is lightening fast and refresh time is also very quick. But I can not really have it in Import mode, because we want to have near real time visibility and that's why I had to go for DirectQuery Approach

amitchandak
Super User
Super User

@RKM , Do you have bi-directional joins.

If yes, Try to avoid that.

Second, make sure you have index in the join columns

 

Please share sample data model

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.