SQL Server - Obsługa lokalizacji ciągów w zagnieżdżonych niedeterministycznych stosach widoków

Podczas profilowania bazy danych natknąłem się na widok odwołujący się do niektórych niedeterministycznych funkcji, do których dostęp uzyskuje się 1000–2500 razy na minutę dla każdego połączenia w puli tej aplikacji. Prosty SELECTz widoku daje następujący plan wykonania:

To wydaje się być złożonym planem dla widoku, który ma mniej niż tysiąc wierszy, w których rząd lub dwa zmieniają się co kilka miesięcy. Ale pogarsza się z następującymi innymi obserwacjami:

1. Widoki zagnieżdżone są niedeterministyczne, więc nie możemy ich indeksować
2. Każdy widok odwołuje się do wielu UDFs, aby zbudować ciągi
3. Każdy UDF zawiera zagnieżdżone UDFs, aby uzyskać kody ISO dla zlokalizowanych języków
4. Widoki na stosie wykorzystują dodatkowe konstruktory ciągów zwrócone zs UDFjako JOINpredykaty
5. Każdy stos widoków jest traktowany jak tabela, co oznacza, że na każdym są INSERT/ UPDATE/ DELETEwyzwalacze do zapisu do tabel bazowych
6. Te wyzwalacze na widokach używać CURSORS, że EXECprocedury stosowane przez odniesienie więcej z nich ciąg budynek przechowywane UDFs.

Wydaje mi się to dość zepsute, ale mam tylko kilka lat doświadczenia z TSQL. To też staje się lepsze!

Wygląda na to, że programista, który zdecydował, że to świetny pomysł, zrobił to wszystko, aby kilkaset zapisanych ciągów mogło mieć tłumaczenie na podstawie ciągu zwróconego z określonego UDFschematu.

Oto jeden z widoków na stosie, ale wszystkie są równie złe:

CREATE VIEW [UserWKStringI18N]
AS
SELECT b.WKType, b.WKIndex
    , CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.I18NString
       ELSE il.I18nString
       END AS WKString
    ,CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.IETFLangCode
       ELSE il.IETFLangCode
       END AS IETFLangCode
    ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
    ,dbo.UserI18N_Session_Locale_Key()  AS IETFSessionLangCode
    ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
FROM   UserWKStringBASE b
LEFT OUTER JOIN User3StringI18N il
ON    (
il.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')
AND il.IETFLangCode = dbo.UserI18N_Session_Locale_Key()
)
LEFT OUTER JOIN User3StringI18N id
ON    (
id.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex,N'WKS')
AND id.IETFLangCode = dbo.UserI18N_Database_Locale_Key()
)
GO

Oto dlaczego UDFsą używane jako JOINpredykaty. I18NIDKolumna jest utworzona przez złączenie:STRING + [ + ID + | + ID + ]

Podczas testowania, prosty SELECTz widoku zwraca ~ 309 wierszy i zajmuje 900-1400 ms do wykonania. Jeśli zrzucę ciągi znaków do innej tabeli i uderzę w nią indeksem, ten sam wybór zostanie zwrócony za 20-75 ms.

Krótko mówiąc (i mam nadzieję, że doceniliście trochę tej głupoty) Chcę być dobrym Samarytaninem i przeprojektować i napisać to dla 99% klientów korzystających z tego produktu, którzy w ogóle nie używają żadnej lokalizacji - - użytkownicy końcowi powinni korzystać z [en-US]ustawień regionalnych, nawet jeśli angielski jest językiem 2/3.

Ponieważ jest to nieoficjalny hack, myślę o następujących rzeczach:

1. Utwórz nową tabelę ciągów wypełnioną czysto połączonym zestawem danych z oryginalnych tabel podstawowych
2. Indeksuj tabelę.
3. Utwórz zestaw zastępczy widoków najwyższego poziomu na stosie, który zawiera NVARCHARi INTkolumny dla kolumn WKTypei WKIndex.
4. Modyfikować garść UDFs, które odwołują się do tych poglądów typu uniknąć konwersji w niektórych dołączyć predykaty (nasz największy stół audytu jest 500-2,000M wierszy i przechowuje INTw NVARCHAR(4000)kolumnie, który jest używany do przyłączenia przeciwko WKIndexkolumnie ( INT)).
5. Schemabind the views
6. Dodaj kilka indeksów do widoków
7. Odbuduj wyzwalacze w widokach za pomocą ustawionej logiki zamiast kursorów

Teraz moje aktualne pytania:

1. Czy istnieje metoda najlepszych praktyk do obsługi zlokalizowanych ciągów za pomocą widoku?
2. Jakie są alternatywy dla zastosowania kodu pośredniczącego UDF? (Mogę napisać konkretny VIEWdla każdego właściciela schematu i na stałe napisać język zamiast polegać na różnych kodach pośredniczących UDF).
3. Czy widoki te można po prostu uczynić deterministycznymi, w pełni kwalifikując zagnieżdżone UDFs, a następnie schematyzując stosy widoków?

Patrząc na dany kod, możemy powiedzieć:

• Po pierwsze, nie powinien to być widok, ale powinna to być procedura składowana, ponieważ nie tylko odczytuje z tabeli, ale wykorzystuje UDF.

• Po drugie, UDF nie powinien być często wywoływany dla tej samej kolumny. Tutaj nazywa się to raz w zaznaczeniu

  ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID 

  i drugi raz za dołączenie

  .IETFLangCode = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')

Można wygenerować wartości w tabeli tymczasowej lub użyć CTE (Common Table Expression), aby uzyskać te wartości w pierwszej kolejności przed połączeniem.

Wygenerowałem przykładowy USP, który zapewni pewne ulepszenia:

CREATE PROCEDURE usp_UserWKStringI18N
AS
BEGIN
    -- Do operation using UDF 
    SELECT b.WKType
        ,b.WKIndex
        ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
        ,dbo.UserI18N_Session_Locale_Key() AS IETFSessionLangCode
        ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
    INTO #tempTable
    FROM UserWKStringBASE b;

    -- Now final Select
    SELECT b.WKType
        ,b.WKIndex
        ,CASE 
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.I18NString
            ELSE il.I18nString
            END AS WKString
        ,CASE 
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.IETFLangCode
            ELSE il.IETFLangCode
            END AS IETFLangCode
        ,b.I18NID
        ,b.IETFSessionLangCode
        ,b.IETFDatabaseLangCode
    FROM #tempTable b
    LEFT OUTER JOIN User3StringI18N il
        ON il.I18NID = b.I18NID
            AND il.IETFLangCode = b.IETFSessionLangCode
    LEFT OUTER JOIN User3StringI18N id
        ON id.I18NID = b.I18NID
            AND id.IETFLangCode = b.IETFDatabaseLangCode
END

Spróbuj tego