Dataflows very very slow Validating Queries

[michaelsh]

Simple SQL query that runs 7 seconds in SQL takes 19 minutes(!) to validate in Dataflows

19 minutes!!!???
Not just one query.

I've tried SQL Server and PostgreSQL - the time it takes to Validate is just crazy.

What is going on?

Please help

[v-chuncz-msft]

@michaelsh 

That seems to work fine for me. You may take a look at Dataflows best practices.

[michaelsh]

@v-chuncz-msft 

Please see this:

https://youtu.be/5fHsRwEM7SY?t=621

But please, please, see it through, read the comments and please, listen to the users, not just me:

The Validation step TAKES FOREVER! It just SUCKS!

The users are wasting TONS of time for this.

This is an AWEFUL experience!

Please fix

Thank you!

[Anonymous]

If I could thumbs up this I would a million times over. It makes no sense why it takes so long to 'validate.' 

I've had multiple DF's take 20-30 minutes to validate and yet the refresh takes 4 minutes. Makes no sense to me at all. It would be nice if there was some intelligence behind it. I've even had it take that 30 minutes to validate the data flow when the only thing I did was delete a table. Since there is logic in place that prevents me from deleting a query in use, the delete should be clean, why does it require a full rebuild of everything again?

[michaelsh]

And here are 60 more votes:

https://community.powerbi.com/t5/Issues/PowerQuery-Validating-queries-when-saving-is-taking-excessiv...

[Anonymous]

Same issue.

I found out that Validating and Editing work super fast outside of working hours.

Submitted a ticket to Microsoft.

[Gulan70]

It's pure performance issue on MS side. Very frustrating: sometimes more than 2 hours for a few changes!
It is my opinion also that after hours or in the weekends you have sometimes more luck.
But this unprofessional slow.

[Anonymous]

It still an issue. Please vote here as well

https://community.powerbi.com/t5/Issues/PowerQuery-Validating-queries-when-saving-is-taking-excessiv...

@v-chuncz-msftcan we ask you to inform the Power BI team about the issue?

[pdbenbow]

@v-chuncz-msft's response to the post is exactly the same one he/she gave to this post 3 months ago about the exact same issue: PBI dataflows validating for a VERY LONG TIME!!! - Microsoft Power BI Community

It would be nice if we could get the support team to take this matter seriously instead of refer us to documentation that does not address the true nature of the issue. @Gulan70's comment is spot-on: this is a MSFT performance issue, and they need to acknowledge it as such and deploy a fix. I'm staring at a query right now that has been validating for 30 minutes, against 9 tables in SQL Server with only 1-2 million rows combined. This kind of performance is absolutely pitiful.

[michaelsh]

What is going on!
It cannot continue this way.

Hours of work lost...

Please fix!
@v-chuncz-msft 

[pdbenbow]

Very annoying that this issue persists. However, it appears that Microsoft plans to release a new feature later this month which will enable background validation for dataflows. It probably will not make them any faster, but at least it will allow us to save our dataflow definitions without having to wait for lengthy validation timelines.

Link: Background validation for dataflows - Power Platform Release Plan | Microsoft Docs

In the meantime, I was finally able to get my queries to validate, but only by making my changes at 7:00 AM local time, when there are fewer users on the Power BI Service.