Docela těžká Power BI hádanka

Dovolím si nabídnout jeden úkol na rozluštění. Vypadá jednoduše, ale zase tak triviální není.

Vyjdeme ze situace, kdy máme MS SQL server s tabulkou, která obsahuje 10 sloupců a cca 80 000 000 řádků (je tedy docela dlouhá).

Power BI desktop (nebo obdobně Power Query v Excelu) se do této tabulky na MS SQL připojuje a provádí tři kroky – filtruje, řadí data, seskupuje data a odebírá poslední řádek. Vše je uloženo i načítáno jako texty.

Pokud jsou kroky seřazené takto (filtrování / seskupení / seřazení / odebrání posledního řádku):

trvá načítání cca 40 vteřin.

Pokud jsou seřazené takto (odebrání posledního řádku / filtrování / seskupení / seřazení):

trvá načítání desítky minut až hodiny (resp. neměl jsem trpělivost to měřit…:).

Otázka zní – proč tomu tak je a kde se bere tak dramatický rozdíl? Napíše někdo do diskuse správnou odpověď?

Poznámky:

• Oba dotazy jsou zcela totožné, rozdíl je pouze v pořadí kroků.
• Ano, vím že výsledky dotazů budou maličko odlišné – protože trochu záleží na tom, kdy se poslední řádek odebere. Pojďme to ale přejít a věnovat se rychlosti.
• Kódy M vypadají takto pro rychlé načtení:
  • let
    Source = Sql.Databases(“DESKTOP-8L3P34M”),
    pbi = Source{[Name=”pbi”]}[Data],
    dbo_combined = pbi{[Schema=”dbo”,Item=”combined”]}[Data],
    #”Filtered Rows” = Table.SelectRows(dbo_combined, each [Key] <> “Key”),
    #”Grouped Rows” = Table.Group(#”Filtered Rows”, {“Key”}, {{“Count”, each Table.RowCount(_), type number}}),
    #”Sorted Rows” = Table.Sort(#”Grouped Rows”,{{“Count”, Order.Ascending}, {“Key”, Order.Ascending}}),
    #”Removed Bottom Rows” = Table.RemoveLastN(#”Sorted Rows”,1)
    in
    #”Removed Bottom Rows”
• a takto pro pomalé:
  • let
    Source = Sql.Databases(“DESKTOP-8L3P34M”),
    pbi = Source{[Name=”pbi”]}[Data],
    dbo_combined = pbi{[Schema=”dbo”,Item=”combined”]}[Data],
    #”Removed Bottom Rows” = Table.RemoveLastN(dbo_combined,1),
    #”Filtered Rows” = Table.SelectRows(#”Removed Bottom Rows”, each [Key] <> “Key”),
    #”Grouped Rows” = Table.Group(#”Filtered Rows”, {“Key”}, {{“Count”, each Table.RowCount(_), type number}}),
    #”Sorted Rows” = Table.Sort(#”Grouped Rows”,{{“Count”, Order.Ascending}, {“Key”, Order.Ascending}})
    in
    #”Sorted Rows”
• Server je na stejném počítači jako Power BI Desktop, do kterého načítáme. Připojení klasicky importuje data – není to DirectQuery.

Tak kdo to dá? 🙂