Performance issues

[tex628]

While trying to change a source in the query editor im getting extreme performance issues that i can not really explain:



I've always had the maximum amount of cashed ram in PBI to 4GB:



All that i am doing is changingthe source of this working SQL query:

let
  Source = Sql.Databases("[SQL Server]"),
  Step 1 = Source{[Name = [Database]]}[Data],
  #"Filtered Rows" = Table.SelectRows(Step 1, each Text.EndsWith([Name], "$Sales Invoice Line")),
    #Step 2 = #"Filtered Rows"{[Schema="dbo",Item="Company$Sales Invoice Line"]}[Data],
    #"Sorted Rows1" = Table.Sort(Company$Sales Line",{{"Shipment Date", Order.Descending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"Document No_", "Amount", "Shortcut Dimension 1 Code", "Posting Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Posting Date", type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Posting Date], 3) or Date.IsInCurrentMonth([Posting Date])),
    #"Appended Query" = Table.Combine({#"Filtered Rows1", #"Sales Cr_Memo Line"})
in
    #"Appended Query"

To dataflows:

let
    Source = PowerBI.Dataflows(null),
    #"ID" = Source{[workspaceId="ID"]}[Data],
    #"ID2" = #"ID"{[dataflowId="ID2"]}[Data],
    #"Sales Invoice Line1" = #"ID2"{[entity="Sales Invoice Line"]}[Data],
    #"Sorted Rows1" = Table.Sort(#"Sales Invoice Line1",{{"Shipment Date", Order.Descending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"Document No_", "Amount", "Shortcut Dimension 1 Code", "Posting Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Posting Date", type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Posting Date], 3) or Date.IsInCurrentMonth([Posting Date])),
    #"Appended Query" = Table.Combine({#"Filtered Rows1", #"Sales Cr_Memo Line"})
in
    #"Appended Query"

Does anyone have any idea as to what is causing this?

[tex628 · Accepted solution]

Hi @Mariusz!

I managed to figure out what caused all of this.
As you can see in the queries below there is a step where i sort decending on shipment date.

After taking a closer look i noticed that in the SQL query the datatype was DateTime while in dataflows it was string. 
Removing the sorting step loaded the query instantly. 

This is a little worrying. I don't quite understand how that can be the cause...

/ J
 

[Mariusz]

Hi @tex628 

My guess is that a lot of processing has been happening on SQL server end ( Query Folding ), where when you change to dataflows, all is done locally.

Best Regards,
Mariusz

Please feel free to connect with me.

[tex628 · Accepted solution]

Hi @Mariusz!

I managed to figure out what caused all of this.
As you can see in the queries below there is a step where i sort decending on shipment date.

After taking a closer look i noticed that in the SQL query the datatype was DateTime while in dataflows it was string. 
Removing the sorting step loaded the query instantly. 

This is a little worrying. I don't quite understand how that can be the cause...

/ J
 

[Mariusz · Accepted solution]

Hi @tex628 

Glad you worked it out, all the best.

Best Regards,
Mariusz

Please feel free to connect with me.