Die wenigsten werden drüber nachdenken, aber hier kann sich ein echter Performance-Killer verbergen. Die Berichtsparameter vom Typ Text sind nämlich immer (n)varchar!

Die Ausgangslage

Gegeben war eine Tabelle mit ca. 45 Mio. Zeilen. Einige Spalten in der Tabelle waren vom Typ varchar und sollten für die Filterung verwendet werden. Hier gab es z. B. nur 10 verschiedene Ausprägungen, womit die Gesamtanzahl von Zeilen schon deutlich reduziert werden konnte. Der SQL Server hat sich aber trotz vorhandener Indizes immer für einen Scan der Tabelle, bzw. der Indizes entschieden.

Verwendet wurde der Parameter in der Regel in dieser Form in der Where-Klausel:

WHERE <Varchar-Feld> IN (@MeinParameter)

Die Ursache

Wenn man z. B. über den Aktivitätsmonitor das generierte Statement mitgeschnitten hat, konnte man sehen, dass die Prüfungen von den Reportingservices wie folgt umgebaut worden sind:

WHERE <Varchar-Feld> IN (N'erster Wert', N'zweiter Wert', N'dritter Wert')

An dieser Stelle kommt jetzt die Rangfolge der Datentypen ins Spiel. Wie man der Online-Doku entnehmen kann, werden die impliziten Konvertierungen immer so vorgenommen, dass die Datentypen welche eine niedrigere Rangfolge haben in den Typ der höheren Rangfolge konvertiert werden. In diesem Falle waren es also varchar (in der Tabelle) und nvarchar (als Parameter). Die Liste ist nun leider etwas ungünstig aufgebaut, denn die kleinste Nummer bedeutet den höchsten Datentyp. Man kann also erkennen, dass immer die varchar-Felder nach nvarchar konvertiert werden. Das sind also die Daten in der Tabelle, bzw. im Index.

Günstiger wäre es ja gewesen die paar Konstanten nach varchar zu konvertieren, aber solche Überlegungen stellt die automatisierte Skripterstellung der Reportingservices nicht an.

Die Lösung

Wie bereits vor einiger Zeit im SQL Server Premier Field Engineer Blog beschrieben, gibt es verschiedene Möglichkeiten dieser Konvertierung aus dem Weg zu gehen. Hier will ich den von mir beschrittenen Weg beschreiben.

Ein dynamisches Dataset

Wir bauen uns also zur Laufzeit ein Dataset zusammen, was implizit bedeutet, dass wir die Definition der Felder auch einmal vollständig von Hand machen müssen.

Das Dataset fängt also an mit

="SELECT <Feldliste> FROM <Tabelle> 
WHERE <Varchar-Feld> IN ("& "'" & Join(Parameters!MeinParameter.Value,"','") & "') AND ...

Wir bauen uns damit eine kommaseparierte Liste auf, wo kein großes N vor jedem String steht und die Werte also nicht nvarchar sind.
Das Ergebnis sieht dann so aus:

WHERE <Varchar-Feld> IN ('erster Wert', 'zweiter Wert', 'dritter Wert')

Wenn wir jetzt auch noch andere Parameter haben, die z. B. numerisch sind müssen wir auch die Mühe auf uns nehmen, diese entsprechend zu kodieren. Aber das ist jetzt nicht mehr schwierig:

<numerisches Feld> IN ("& Join(Parameters!NumerischerParameter.Value,",") & ") AND ...

Mit diesem dynamischen Dataset können wir jetzt typkonform die Abfragen starten und erhalten anstelle des Index-Scan einen Index-Seek.

Die Konvertierung des Datentyps nach nvarchar in der Tabelle stand nicht zur Debatte, da der verwendete Speicherplatz dadurch natürlich steigt, weniger Datensätze auf eine Page passen, die Komprimierung auf Data-Page Ebene weniger ausrichten kann.
Eine gute Alternative wäre noch, die Zeichenketten durch numerische Werte zu ersetzen und die Abfragen nur über numerische Parameter laufen zu lassen. Aber dafür hätte man ein kleines Datawarehouse mit entsprechenden Dimensionstabellen schaffen müssen und den Transfer der Daten, die natürlich wieder ganz woanders herkommen, entsprechend umbauen müssen.