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
