Benutzer mit den meisten Antworten
Funktion vielfach langsamer als direkte Abfrage

Frage
-
Gegeben: Eine tabellenwertige Inline-Funktion mit drei Parametern und ein Beispiel-Parametersatz.
Umgebung: SQL Server 2008 Enterprise 64 bit (10.0.1600), Windows Server 2008 Enterprise
Diese recht komplexe Funktion braucht mit dem Parametersatz 8 s. Werden dagegen drei lokale Variablen definiert, entsprechend gesetzt und der Inhalt der Funktion direkt ausgeführt, braucht die Abfrage nur 0,5 s. Es spielt keine Rolle, ob die Funktion zum ersten oder wiederholtem Male aufgerufen wird.
Weshalb braucht die Funktion vielfach länger als die direkte Abfrage?
Um die Funktion zu beschleunigen wurden mehrere sinnvolle ungruppierte Indizes eingerichtet. Einer davon wurde vom tatsächlichen Ausführungplan vorgeschlagen. Weiterhin braucht die Funktion noch 8 s, obwohl im Ausführungplan die Anwendung der Indizes ersichtlich ist.
Wieso bringen die Indizes keine Beschleunigung?
Funktionsdefinition:CREATE FUNCTION dbo.fDatensatzrechte
(
@ID_Art int,
@ID_Benutzer int,
@ID_Formular int
)
RETURNS table
AS
RETURN
WITH RechteRelevant as (
SELECT
D.*,
R.istAusnahme, R.ID_KstGruppenart,
case when D.ID_Gruppe = 0 then 0 else 2 end
+ case when D.ID_Formular = 0 then 0 else 1 end as Rang
FROM tblDatensatzrechte as D
JOIN tblDatensatzrechtRadien as R on R.ID_DatensatzrechtRadius = D.ID_DatensatzrechtRadius
WHERE D.ID_Art = @ID_Art
and (D.ID_Gruppe = 0 or D.ID_Gruppe in (
select ID_Gruppe from tblBenutzerGruppen where ID_Benutzer = @ID_Benutzer ))
and (D.ID_Formular = @ID_Formular or (D.ID_Formular = 0 and exists (
select * from tblGruppenFormularberechtigungen as E
where (E.ID_Gruppe = D.ID_Gruppe or (D.ID_Gruppe = 0 and E.ID_Gruppe in (
select ID_Gruppe from tblBenutzerGruppen where ID_Benutzer = @ID_Benutzer )))
and E.ID_Formular = @ID_Formular and E.fRead = 1 )))
),
RechteEinzeln as (
SELECT
case when T.istAusnahme = 1 then T.ID_Datensatz
else case @ID_Art
when 1 then RI.ID_Person
when 2 then RP.ID_Person
when 3 then RK.ID_Kostenstelle
when 4 then RK.ID_KstGruppe
end
end as ID_Datensatz,
T.istAusnahme,
T.fRead, T.fUpdate, T.fInsert, T.fDelete
FROM RechteRelevant as T
JOIN (
SELECT
istAusnahme,
max(Rang) as Rang
FROM RechteRelevant
GROUP BY istAusnahme
) as G on G.istAusnahme = T.istAusnahme and G.Rang = T.Rang
LEFT JOIN tblKstGruppenarten as KA on KA.ID_KstGruppenart = T.ID_KstGruppenart
LEFT JOIN fKstGruppen() as RK on T.istAusnahme = 0
and ( (@ID_Art = 1 and RK.ArtName = 'Mandant' and T.ID_KstGruppenart is not Null)
or (@ID_Art = 2 and RK.Ebene = 1 and T.ID_KstGruppenart is not Null)
or (@ID_Art = 3 and RK.Ebene = 1)
or (@ID_Art = 4)
)
and (T.ID_DatensatzrechtRadius = 1
or exists (
select * from (
SELECT isNull( KBK.ID_Kostenstelle, KBP.ID_Kostenstelle ) as ID_Kostenstelle
FROM tblBenutzer as KB
JOIN fBeschaeftigungsverhaeltnisse() as KBV on KBV.ID_Mitarbeiter = KB.ID_Person
and KBV.Eintrittsdatum <= getdate()
and (KBV.Austrittsdatum is Null or dateadd(day,1,KBV.Austrittsdatum) > getdate())
JOIN tblBeschaeftigungsparameter as KBP
on KBP.ID_Beschaeftigungsverhaeltnis = KBV.ID_Beschaeftigungsverhaeltnis
and KBP.von <= getdate()
and (KBP.bis is Null or dateadd(day,1,KBP.bis) > getdate())
LEFT JOIN tblBeschaeftigungKostenstellen as KBK
on KBK.ID_Beschaeftigungsparameter = KBP.ID_Beschaeftigungsparameter
WHERE KB.ID_Benutzer = @ID_Benutzer
) as KP
join fKstGruppen() as KK on KK.Ebene = 1 and KK.ID_Kostenstelle = KP.ID_Kostenstelle
join fKstGruppen() as KG on KK.Pfad like KG.Pfad + '%' and KG.Ebene = KA.Ebene
and RK.Pfad like KG.Pfad + '%'
))
and ( isNull(T.ID_Betreuungskategorie, 0) = 0
or ( T.ID_Betreuungskategorie = 1 and RK.fA = 1 )
or ( T.ID_Betreuungskategorie = 2 and RK.fB = 1 )
or ( T.ID_Betreuungskategorie = 3 and RK.fC = 1 )
)
LEFT JOIN fMandanten() as RI on @ID_Art = 1 and T.istAusnahme = 0
and (T.ID_DatensatzrechtRadius = 1
or RI.ID_KstGruppe = RK.ID_KstGruppe
)
LEFT JOIN fPersonen() as RP on @ID_Art = 2 and T.istAusnahme = 0
and (isNull(T.ID_Personengruppe, 1) = 1 or RP.ID_Person in (
select ID_Person from tblPersonengruppenMitglieder as M
where M.explizit = 0 and M.ID_Personengruppe = T.ID_Personengruppe
))
and (T.ID_DatensatzrechtRadius = 1
or (T.ID_DatensatzrechtRadius = 2 and RP.ID_Person in (
select ID_Person from tblBenutzer as B
where B.ID_Benutzer = @ID_Benutzer ))
or (RK.ID_Kostenstelle is not Null and (
exists (select * from fBeschaeftigungsverhaeltnisse() as BV
left join tblBeschaeftigungsparameter as BP
on BP.ID_Beschaeftigungsverhaeltnis = BV.ID_Beschaeftigungsverhaeltnis
left join tblBeschaeftigungKostenstellen as BK
on BK.ID_Beschaeftigungsparameter = BP.ID_Beschaeftigungsparameter
where BV.ID_Mitarbeiter = RP.ID_Person
and RK.ID_Kostenstelle = isNull(BK.ID_Kostenstelle, BP.ID_Kostenstelle)
)
or exists (select * from tblAUWohnen as B
where B.ID_Bewohner = RP.ID_Person and B.ID_Kostenstelle = RK.ID_Kostenstelle
)
))
)
)
SELECT
ID_Datensatz,
cast(max(cast(fRead as int)) as bit) as fRead,
cast(max(cast(fUpdate as int)) as bit) as fUpdate,
cast(max(cast(fInsert as int)) as bit) as fInsert,
cast(max(cast(fDelete as int)) as bit) as fDelete
FROM (
SELECT *,
cast(
max(cast(istAusnahme as int)) over(partition by ID_Datensatz)
as bit) as hatAusnahme
FROM RechteEinzeln
) as G
WHERE istAusnahme = 1 or hatAusnahme = 0
GROUP BY ID_Datensatz
Antworten
-
Hallo Volker,
schau Dir bitte mal diesen Artikel an
http://jahaines.blogspot.com/2010/08/sql-server-parameter-sniffing.html
SQL Server Parameter Sniffingund danach diesen:
http://msdn.microsoft.com/de-de/library/ms186755.aspx
CREATE FUNCTION (Transact-SQL)Hier ist dann das Beispiel C "Erstellen einer Tabellenwertfunktion mit mehreren Anweisungen" interessant. Das dürfte auch ungefähr das sein, worauf Elmar Dich hinweisen wollte. Versuch doch auch mal die Parameter zuerst in lokale Variablen zu übergeben und dann erst in den Statements zu verwenden. Vielleicht klappt es dann schon besser, oder Du versuchst noch vier einzelne Abfragen draus zu machen. Alternativ kannst Du noch die Ergebnisse der CTEs in temporäre Tabellen packen und dann erst weiter verwenden. Es bleibt halt kein Einzeiler.
Der Unterschied zwischen den tatsächlichen und den geschätzten Werte weist darauf hin, dass die Schätzung eben nicht sehr gut war. Bei der Version, die total daneben liegt, könnte die Verarbeitungsreihenfolge der Tabellen eine Rolle spielen. Einschränkungen greifen erst später und dadurch steigt die Verarbeitungsdauer. Der Optimizer stellt ja auch schon mal ein Statement komplett um, falls er denkt, dies könnte besser sein. Hier klappt es dann wohl eher nicht.
Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu- Als Antwort markiert drh1 Dienstag, 25. Oktober 2011 17:11
-
Ich scheine eine Lösung gefunden zu haben:
Ich habe etwa in der Mitte des CTEs "RechteEinzeln", direkt unterhalb des Blocks, indem sich die Ausführungsplane untschieden, folgende Zeile geändert:
inner hash join fKstGruppen() as KK on KK.Ebene = 1 and KK.ID_Kostenstelle = KP.ID_Kostenstelle
Durch den JOIN-Hinweis "hash" werden unnötig große Zwischenergebnisse vermieden und die Funktion läuft mit allen Parametersätzen in etwa 0,3 s, also noch schneller also zuvor die direkte Abfrage. Ein Merge-JOIN ist ebenso schnell, nur ein Loop-JOIN ist langsam. Und das wurde automatisch angewendet.
Allerdings erklärt das nicht den Unterschied zwischen Funktion und direkter Abfrage. Denn bei beiden wurde ein Loop-JOIN angewendet. Der entscheidende Unterschied ist anscheinend, dass durch den JOIN-Hinweis die WHERE-Einschränkung vor dem JOIN angewendet wird und die tatsächlichen Anzahl Zeilen im Ausführungsplan wie erwartet 1 ist.
- Als Antwort markiert drh1 Dienstag, 25. Oktober 2011 17:10
Alle Antworten
-
-
Hallo,
Bei einer Funktion müssen die Parameter allgemein abgehandelt werden,
im Gegensatz zu einer direkten Abfrage, wo sich der SQL Server die Parameter auswerten kann.Zudem ist die Funktion ist reichlich komplex, insbesondere durch die Fallunterscheidungen,
verbunden über äußere Verknüpfungen.
Da einen guten allgemeinen Abfrageplan zu bekommen, ist (fast) schon Glücksache.Du solltest überlegen, ob nicht eine gespeicherte Prozedur sinnvoller ist,
in der die Einzelfälle separat abgehandelt werden, d. h. in einzelne Abfragen zerlegt
und ggf. Zwischenergebnisse in temporäre Tabellen übernommen werden.Gruß Elmar
-
Hallo Christoph,
der entscheidende Unterschied der tatsächlichen Ausführungsplane liegt wohl im folgenden Block:
(
SELECT isNull( KBK.ID_Kostenstelle, KBP.ID_Kostenstelle ) as ID_Kostenstelle
FROM tblBenutzer as KB
JOIN fBeschaeftigungsverhaeltnisse() as KBV on KBV.ID_Mitarbeiter = KB.ID_Person
and KBV.Eintrittsdatum <= getdate()
and (KBV.Austrittsdatum is Null or dateadd(day,1,KBV.Austrittsdatum) > getdate())
JOIN tblBeschaeftigungsparameter as KBP
on KBP.ID_Beschaeftigungsverhaeltnis = KBV.ID_Beschaeftigungsverhaeltnis
and KBP.von <= getdate()
and (KBP.bis is Null or dateadd(day,1,KBP.bis) > getdate())
LEFT JOIN tblBeschaeftigungKostenstellen as KBK
on KBK.ID_Beschaeftigungsparameter = KBP.ID_Beschaeftigungsparameter
WHERE KB.ID_Benutzer = @ID_Benutzer
) as KP
Als eigenständige Abfrage mit dem Beispiel-Parametersatz liefert dieser Block genau 1 Zeile.
Der tatsächliche Ausführungsplan der Funktion ergibt überall innerhalb dieses Blocks:
Anzahl Zeilen tatsächlich: 3609, geschätzt: zwischen 1 und 1,68.
Der tatsächliche Ausführungsplan der Abfrage ergibt überall innerhalb dieses Blocks:
Anzahl Zeilen tatsächlich: 11, geschätzt: zwischen 1 und 1,68.
Die Funktion fBeschaeftigungsverhaeltnisse JOINed 3 Tabellen. Die beiden Ausführungsplane untscheiden sich in der Reihenfolge der 4 Tabellen aus
tblBenutzer as KB
JOIN fBeschaeftigungsverhaeltnisse()
Detaillierte Mengenangaben: Der Ausdruck
FROM tblBenutzer as KB
WHERE KB.ID_Benutzer = @ID_Benutzer
liefert 1 Zeile. Der JOIN auf fBeschaeftigungsverhaeltnisse() und die beiden anderen JOINs belassen es bei 1 Zeile.
Die Schätzung der Anzahl Zeilen passt gut. Aber wieso können die tatsächlichen Anzahl Zeilen 11 und 3609 statt 1 sein?
Der Unterschied 11 zu 3609 könnte ein Symptom der deutlich abweichenden Ausführungsdauern sein, die ich im letzten Post angesprochen habe. Bleibt die Frage, warum reproduzierbar ein anderer Ausführungsplan generiert wird, je nachdem, ob es als Funktion oder direkt ausgeführt wird.
Gruß
Volker -
Hallo Elmar,
für die Parameter der Funktion gibt es etwa 4, 500 bzw. 300 Möglichkeiten, also etwa 600.000 Parametersätze, die im Mittel 10.000 Zeilen liefern. Würden alle Ergebnisse vorberechnet und zwischengespeichert hätte die Tabelle 6 Milliarden Einträge.
Ich verwende den Parameter @ID_Art = 1..4, um Codeduplizierung zu vermeiden. Denn ich hätte sonst 4 ähnliche Funktionen. Ich habe gerade die Funktion für den Spezialfall @ID_Art = 1 vereinfacht und getestet. Ausführungsdauer und Ausführungsplan verbessern sich dadurch nicht.
Gruß
Volker -
Hallo Volker,
schau Dir bitte mal diesen Artikel an
http://jahaines.blogspot.com/2010/08/sql-server-parameter-sniffing.html
SQL Server Parameter Sniffingund danach diesen:
http://msdn.microsoft.com/de-de/library/ms186755.aspx
CREATE FUNCTION (Transact-SQL)Hier ist dann das Beispiel C "Erstellen einer Tabellenwertfunktion mit mehreren Anweisungen" interessant. Das dürfte auch ungefähr das sein, worauf Elmar Dich hinweisen wollte. Versuch doch auch mal die Parameter zuerst in lokale Variablen zu übergeben und dann erst in den Statements zu verwenden. Vielleicht klappt es dann schon besser, oder Du versuchst noch vier einzelne Abfragen draus zu machen. Alternativ kannst Du noch die Ergebnisse der CTEs in temporäre Tabellen packen und dann erst weiter verwenden. Es bleibt halt kein Einzeiler.
Der Unterschied zwischen den tatsächlichen und den geschätzten Werte weist darauf hin, dass die Schätzung eben nicht sehr gut war. Bei der Version, die total daneben liegt, könnte die Verarbeitungsreihenfolge der Tabellen eine Rolle spielen. Einschränkungen greifen erst später und dadurch steigt die Verarbeitungsdauer. Der Optimizer stellt ja auch schon mal ein Statement komplett um, falls er denkt, dies könnte besser sein. Hier klappt es dann wohl eher nicht.
Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu- Als Antwort markiert drh1 Dienstag, 25. Oktober 2011 17:11
-
Hallo Christoph,
Danke für die ausführliche Antwort.
Kann es an Parameter Sniffing liegen, wenn die Funktion zum ersten Mal aufgerufen wird, nachdem sie erzeugt wurde? Wie im ersten Post beschrieben lief sie bereits beim ersten Mal langsam.
Tatsächliche Werte weichen natürlich von Schätzungen ab. Wie im letzten Post geschrieben sind die tatsächlichen Anzahl Zeilen deutlich größer als die Rückgabe. Das müsste bedeuten, dass die Einschränkung
WHERE KB.ID_Benutzer = @ID_Benutzer,
die die Rückgabe auf eine Zeile einschränkt, erst außerhalb der Unterabfrage angewendet wird. Das wäre eine fatale "Optimierung".
Gruß
Volker- Als Antwort vorgeschlagen hubi777 Freitag, 13. Januar 2012 07:45
-
Ich scheine eine Lösung gefunden zu haben:
Ich habe etwa in der Mitte des CTEs "RechteEinzeln", direkt unterhalb des Blocks, indem sich die Ausführungsplane untschieden, folgende Zeile geändert:
inner hash join fKstGruppen() as KK on KK.Ebene = 1 and KK.ID_Kostenstelle = KP.ID_Kostenstelle
Durch den JOIN-Hinweis "hash" werden unnötig große Zwischenergebnisse vermieden und die Funktion läuft mit allen Parametersätzen in etwa 0,3 s, also noch schneller also zuvor die direkte Abfrage. Ein Merge-JOIN ist ebenso schnell, nur ein Loop-JOIN ist langsam. Und das wurde automatisch angewendet.
Allerdings erklärt das nicht den Unterschied zwischen Funktion und direkter Abfrage. Denn bei beiden wurde ein Loop-JOIN angewendet. Der entscheidende Unterschied ist anscheinend, dass durch den JOIN-Hinweis die WHERE-Einschränkung vor dem JOIN angewendet wird und die tatsächlichen Anzahl Zeilen im Ausführungsplan wie erwartet 1 ist.
- Als Antwort markiert drh1 Dienstag, 25. Oktober 2011 17:10