Query folding: SQL-Server-Abfragen mit Excel-Tabellen filtern
Power Query bietet praktische Möglichkeiten, SQL-Server-Abfragen mit ganzen Excel-Tabellen zu filtern, statt nur mit einzelnen Parametern (wie in anderen Excel-basierten Ansätzen). Man muss dabei beachten, dass der Filter zum Server durchgereicht wird, also die Daten gefiltert, bevor sie an Excel geliefert werden. Das hat einen enormen Einfluss auf die Abfrage-Performance und wird query folding genannt. Koen Verbeecks Blogbeitrag bietet einen sehr guten Überblick zu diesem Thema, Links zu anderen nützlichen Quellen sowie eine Auflistung von Elementen, die query folding verhindern.
Neulich bin ich allerdings über ein seltsames Phänomen gestolpert: Das folding schien trotz der gleichen Abfrage rein zufällig mal zu funktionieren und dann doch mal wieder nicht. Am Code konnte es also eigentlich nicht liegen. Das Microsoft-Team war hier aber sehr hilfreich: Sie wiesen auf einen Bug in der aktuellen Heuristik hin, die eigentlich das Ladeverhalten optimieren soll. Dieser Bug führt dazu, dass query folding immer genau dann verhindert wird, wenn die Excel-Tabelle länger als 200 Zeilen ist.
Wenn diese Heuristik also funktionieren würde wie geplant, würde Folgendes passieren:
- Bei weniger als 100 „nicht-SQL-Filterzeilen“ (also bei sehr kurzen Listen): Sofort die folding-Methode wählen
- ODER wenn die Filtertreffer der ersten 200 Zeilen unter 2% liegen (also auch recht wenige Treffer landen), dann auch den Server filtern lassen, ansonsten doch die ganze Tabelle an Excel hochladen und dort filtern lassen.
Das sieht aus meiner Sicht ganz OK aus (meine Anwendungsfälle wären alle auf dem Server gefiltert worden). Aber so funktioniert es aktuell leider nicht. Viel mehr wird bei jeder Filterliste, die länger als 200 Zeilen ist, grundsätzlich die gesamte Tabelle in Excel hochgeladen und erst dort gefiltert. Dies ist ein echter Performancekiller für viele Anwendungen.
Dieser Workaround umgeht das Problem, indem:
- die Filtertabelle automatisch in max. 200 Zeilen lang aufgesplittet wird
- über einen Funktion die Ausführung der Filteroperation über alle kleine Tabellen gesteuert wird und
- am Ende alles wieder zu einer Gesamttabelle zusammengefügt wird
So werden die Filter wieder auf dem Server ausgeführt, auch wenn die eigentlichen Filtertabellen viel länger sind:
let
// 2 Performing the filter operation in a function
function = (group)=>
let
Quelle = Sql.Databases("SQL_Server_Name"),
Database_Name = Quelle{[Name="Database_Name"]}[Data],
dbo_Data = Database_Name {[Schema="dbo",Item="Daten"]}[Data],
Result = Table.Buffer(Table.NestedJoin(dbo_Data,{"KeyId"},Table.SelectRows(FilterTable, each [Group] = group),{"KeyId"},"NewColumn",JoinKind.Inner))
in
Result,
// 1 Splitting filter table ("Tabelle1") into chuncks of 200 lines and add index
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
ChgType = Table.TransformColumnTypes(Quelle,{{"KeyId", type number}}),
AddIndex = Table.AddIndexColumn(ChgType, "Index", 1, 1),
FilterTable = Table.AddColumn(AddIndex, "Group", each Number.RoundUp([Index]/200)),
// 3 Main query: Create table with groups to call function from, call function and expand (thereby stitching the results back together)
Source = {1..Number.RoundUp(List.Max(FilterTable[Group]))},
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddFunctionCol = Table.AddColumn(ConvertToTable, "Custom", each function([Column1])),
ExpandResults = Table.ExpandTableColumn(AddFunctionCol, "Custom", {"KeyId", "Datum", "Wert"}, {"KeyId", "Datum", "Wert"})
in
ExpandResults
Die Zeichenfolgen in fett müssen auf Ihre jeweilige Serversituation angepasst werden.
Enjoy & stay queryious :-)