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

[RKM]

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?

[v-jayw-msft]

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

[RKM]

Hi @amitchandak 

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

[amitchandak]

@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

[RKM]

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]

@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