SQL Query Folding Massive Performance Decrease after PBI Update | PBI Service

[thschr]

Hello fellow Power BI enthusiasts,

since a few days we saw that there are some scheduled refreshes that suddenly take 6-7 hours instead of 30 minutes.

After quite some investigation, we were able to recreate the issue in PBI Desktop.

We had to download the newest version to be able to recreate it.

We use a Microsoft SQL Server and focus a lot on query folding.

What we see is that any step that causes query folding to break, takes many times longer than it used to.

A simple query like this which runs over a table containing 30 million rows takes 30 minutes to query.

A typical load time would be in the few-second range (we have a 2gig/s connection between our workstation and the SQL server).

"let
Source = Sql.Database("server", dbname),
Offers = Source{[Schema="dbo",Item="Offers"]}[Data],
#"Removed Other Columns1" = Table.SelectColumns(Offers,{"DataDate", "price", "Marketplace", "SKU"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns1", {"DataDate", "SKU"}, {{"Count", each _, type table [DataDate=nullable date, price=nullable number, Marketplace=nullable text, SKU=nullable text]}})
in
#"Grouped Rows""

We recreated the behaviour across various tables and always come to the same result. The moment we break query folding, performance on the new PBI 64bit version is super slow (also reflected in scheduled refreshs).

When we download the previous version, everything loads fine again.

Did anyone experience the same issue with the new version?

Any suggestions on things we could check?

[MoorsTech]

@Icey thanks, we are working on this issue with the support but have not yet found a resolution to this issue.

[Icey]

Hi @thschr ,

Since this can be reproduced and it may involve log or other sensitive information. It is suggested to create a support ticket to get futher help from Product Team.

In addition, if you get any information helpful from Product Team, could you share us?

Best Regards,

Icey

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

[MoorsTech]

Just pitching in to clarify:
This issue is perfectly reproducable and was clearly introduced with version 2.92.1067.0 (April 2021 QFE 2)
of Power BI Desktop, version 2.92.943.0 (April 2021 QFE 1) does not exhibit the same behavior.