Slow performance

[Anonymous]

Hi,

I have created a Power BI report and have noticed something odd with my reports, Let me explain a little about this report, I've created a report with four datasets and connected these datasets, when I create visuals they take roughly around 30-40 secs to display results on the visuals. Later I identified a single view which had the exact same columns needed for these reports and switched the query and merged three datasets into a single dataset and connected the datasets, now I had just two datasets, with this I hoped that the reports would be faster in displaying results but on the contrary, these visuals now take over 7-8 mins to run, it's insanely slow and I don't understand the reason what's causing this to happen. 

Also, I do not have any calculated columns or measures, the datasets are in Direct Query mode and hence no transformations on any of them, the datasets are connected as one-to-many with a single filter. There are many other reports that I have built over the months the exact same way and never have I ever seen any sought of performance issues on those reports. I use the exact same query on SSRS and the report works perfectly fine. 

I would really appreciate it if there are any suggestions/tips/solutions for this issue. Would also like to know if any have faced similar issues before.  

[camargos88]

@Anonymous ,

It would be nice to check the transformations that you have done on Power Query and the how query is executing on the source. 

[Anonymous]

Hello @camargos88,

All datasets are in Direct Query mode, so non of the datasets have any transformations on it. Still trying to figure what's causing the reports to run slow.  

[camargos88]

@Anonymous ,

Maybe the generated query is not performing well on the source.

If you work with a disconnected table, you get the same performance ? 

[Anonymous]

Hello @camargos88,

The disconnected dataset works perfectly fine, but when I create a relationship between the datasets that's when the issue starts, the relationship is one-to-many, I've been testing other relationships to see if there are any changes, but one-to-many is the only relationship permitted between these datasets.

And other alternative testings on canvas too like testing the data on a table or matrix, tables are performing a bit faster than the matrix, things like that.

[camargos88]

@Anonymous ,

So it's necessary to check the generated query pushed to the database and try to execute it directly on the source to see how fast it returns.

How many rows are we talking about ?

[Anonymous]

@camargos88,

The usual thing while creating my reports is that I always work on the queries on MS studio test them and then bring them into Power BI, output on MS studio is within 3-4 secs, with 3402 rows to be exact (this is on my testing lab). 

On Power Query the query loads fine and I make sure everything is good, then I do a close and apply.

[camargos88]

@Anonymous ,

It's possible that PQ is folding the query with the user interactions... so it's pushing a new query (with the applied filters) to the source.

Can you check from the database side the execution time for those visuals with problems ?