Sql-Abfragezeit springt von 1 Sekunde auf über 1 Minute, wenn Funktion in WHERE-Klausel verwendet wird

Question

Ich habe diese Abfrage in MS SQL, die sich sehr seltsam verhält (zumindest aus meiner Sicht).

Ich habe benutzerdefinierte Funktion namens: dbo.NajblizszaDataWyceny(3, '2010-02-05'), die einfache Prüfung für TOP 1 Eintrag in einer Tabelle mit einigen anderen verbunden ist. Die Abfrage selbst dauert wie Millisekunden, also ist es kein großes Problem, aber ich zeige die Funktion trotzdem.

CREATE FUNCTION [dbo].[NajblizszaDataWyceny] (@idPortfela INT, @dataWaluty DATETIME)
RETURNS DATETIME
AS BEGIN
RETURN (

SELECT TOP 1         [WycenaData]
FROM    [BazaZarzadzanie].[dbo].[Wycena] t1
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfeleKonta] t3
    ON t1.[KlienciPortfeleKontaID] = t3.[KlienciPortfeleKontaID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfele] t4
    ON t3.[PortfelID] = t4.[PortfelID]
WHERE   [WycenaData] <= @dataWaluty  AND [t3].[PortfelID] = @idPortfela
ORDER BY [WycenaData] DESC)
END

Wenn ich diese Funktion auf folgende Weise verwende:

DECLARE @dataWyceny DATETIME
SET @dataWyceny = dbo.NajblizszaDataWyceny(3, '2010-02-05') 

SELECT  t1.[KlienciPortfeleKontaID],
    t4.[PortfelIdentyfikator] AS 'UmowaNr',
    t5.[KlienciRachunkiNumer],
    [WycenaData],
    t2.[InISIN] AS 'InstrumentISIN',
    t2.[InNazwa] AS 'InstrumentNazwa',
    [WycenaWartosc]
FROM    [BazaZarzadzanie].[dbo].[Wycena] t1
    LEFT JOIN [BazaZarzadzanie].[dbo].[Instrumenty] t2
    ON t1.[InID] = t2.[InID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfeleKonta] t3
    ON t1.[KlienciPortfeleKontaID] = t3.[KlienciPortfeleKontaID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfele] t4
    ON t3.[PortfelID] = t4.[PortfelID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciRachunki] t5
    ON t3.[KlienciRachunkiID] = t5.[KlienciRachunkiID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[WycenaTyp] t6
    ON t1.[WycenaTyp] = t6.[WycenaTyp]
WHERE   WycenaData = @dataWyceny     AND t3.[PortfelID] = 3
ORDER BY t5.[KlienciRachunkiNumer],
    WycenaData

dauert es 1 Sekunde, um zu laufen. Aber wenn ich die Benutzerfunktion direkt in WHERE einfüge, sieht es so aus:

SELECT  t1.[KlienciPortfeleKontaID],
    t4.[PortfelIdentyfikator] AS 'UmowaNr',
    t5.[KlienciRachunkiNumer],
    [WycenaData],
    t2.[InISIN] AS 'InstrumentISIN',
    t2.[InNazwa] AS 'InstrumentNazwa',
    [WycenaWartosc]
FROM    [BazaZarzadzanie].[dbo].[Wycena] t1
    LEFT JOIN [BazaZarzadzanie].[dbo].[Instrumenty] t2
    ON t1.[InID] = t2.[InID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfeleKonta] t3
    ON t1.[KlienciPortfeleKontaID] = t3.[KlienciPortfeleKontaID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciPortfele] t4
    ON t3.[PortfelID] = t4.[PortfelID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[KlienciRachunki] t5
    ON t3.[KlienciRachunkiID] = t5.[KlienciRachunkiID]
    LEFT JOIN [BazaZarzadzanie].[dbo].[WycenaTyp] t6
    ON t1.[WycenaTyp] = t6.[WycenaTyp]
WHERE   WycenaData = dbo.NajblizszaDataWyceny(3, '2010-02-05')      AND t3.[PortfelID] = 3
ORDER BY t5.[KlienciRachunkiNumer],
    WycenaData

Es dauert 1,5 Minuten bis zum Ende. Kann mir jemand erklären, warum das passiert?

Answer 1

Funktionen werden in SQL Server nicht als rein angesehen, was bedeutet, dass der Abfrageoptimierer die Ergebnisse einer Funktion nicht zwischenspeichern und wiederverwenden wird; die Funktion wird jedes Mal aufgerufen, wenn auf sie verwiesen wird. Dies gilt sogar für einfache Funktionen, die nur Zahlen zurückgeben (wie wir bei einem Projekt, bei dem wir Funktionen zur Emulation von Konstanten verwendeten, feststellen mussten...).

In der ersten Version wird die Funktion also nur einmal aufgerufen, und das Ergebnis wird manuell zwischengespeichert und in der Abfrage wiederverwendet. In der zweiten Version wird die Funktion jedoch für jede Zeile aufgerufen, wenn die WHERE Klausel versucht, die Zeile zu finden. Wenn Sie viele Zeilen haben, summieren sich ein paar Millisekunden pro Zeile.

(Beachten Sie auch, dass Ihre Abfragen semantisch unterschiedlich sind. In der ersten Abfrage sagen Sie "wo die Dinge gleich sind wie das Ergebnis der Funktion, die ich zu Beginn ausgewertet habe" und in der zweiten sagen Sie "wo die Dinge gleich sind wie das Ergebnis der Funktion, die ich zu diesem bestimmten Zeitpunkt auswerte, während ich die Zeile betrachte". Da Ihre Funktion eine SELECT Anweisung, dann könnte sie - je nach Transaktionsisolationsebene - möglicherweise unterschiedliche Ergebnisse für verschiedene Zeilen liefern, wenn sich die zugrunde liegenden Daten ändern).

Answer 2

Im zweiten Codebeispiel wird die Funktion für jede Zeile in der resultierenden Join-Tabelle aufgerufen. Davon gäbe es viele.

Im ersten Fall wird sie immer nur einmal aufgerufen.

Answer 3

Der Datenbankserver ist offenbar nicht klug genug, um zu entscheiden, dass er die Funktion nur einmal auswerten und dann als Konstante in einem Index verwenden kann.

Handelt es sich um eine ältere Version von MS SQL?

Möglicherweise müssen Sie die Funktion auch irgendwie als deterministisch deklarieren (d.h. denselben Wert für dieselbe Eingabe zurückgeben), wenn MS-SQL eine solche Option hat.

Aktualisierung: Ich habe gerade gesehen, dass Ihre Funktion "eine einfache Prüfung auf TOP 1 Einträge in einer Tabelle ist, die mit mehreren anderen Tabellen verbunden ist." Das bedeutet, dass die Funktion nicht deterministisch und nicht unabhängig von den Datenbankdaten ist. Es gibt keine Möglichkeit für den Optimierer, dies zu beschleunigen.