none
Funktion vielfach langsamer als direkte Abfrage RRS feed

  • 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

    Donnerstag, 20. Oktober 2011 15:07

Antworten

  • Hallo Volker,
    schau Dir bitte mal diesen Artikel an
    http://jahaines.blogspot.com/2010/08/sql-server-parameter-sniffing.html
    SQL Server Parameter Sniffing

    und 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
    Dienstag, 25. Oktober 2011 09:45
    Beantworter
  • 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
    Dienstag, 25. Oktober 2011 17:08

Alle Antworten

  • Hallo!
    Hast Du mal die Ausführungspläne verglichen?
    Ohne Wissen um das Tabellendesign und die Mengengerüste kann man dies nicht beantworten.

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    Freitag, 21. Oktober 2011 09:25
    Beantworter
  • Hallo drh1,

     

    kannst du vielleicht mehrere Info geben, wie Christoph empfohlen hat?

     

    Viele Grüße,

    Alex

    Montag, 24. Oktober 2011 10:13
  • 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

    Montag, 24. Oktober 2011 11:00
  • 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

    Montag, 24. Oktober 2011 15:00

  • 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
    Montag, 24. Oktober 2011 16:13
  • Hallo Volker,
    schau Dir bitte mal diesen Artikel an
    http://jahaines.blogspot.com/2010/08/sql-server-parameter-sniffing.html
    SQL Server Parameter Sniffing

    und 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
    Dienstag, 25. Oktober 2011 09:45
    Beantworter
  • 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
    Dienstag, 25. Oktober 2011 17:07
  • 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
    Dienstag, 25. Oktober 2011 17:08