Dzielenie zapytania SQL z wieloma złączami na mniejsze pomaga?

Każdej nocy musimy sporządzać raporty na naszym SQL Server 2008 R2. Obliczanie raportów zajmuje kilka godzin. W celu skrócenia czasu wstępnie obliczamy tabelę. Ta tabela jest tworzona na podstawie ŁĄCZENIA 12 dość dużych tabel (rząd dziesiątek milionów).

Obliczenie tej tabeli agregacji zajęło kilka dni temu około 4 godzin. Nasz DBA rozdzielił to duże złączenie na 3 mniejsze złączenia (każde łączące 4 tabele). Tymczasowy wynik jest zapisywany w tabeli tymczasowej za każdym razem, który jest wykorzystywany przy następnym łączeniu.

Rezultatem udoskonalenia DBA jest to, że tabela agregacji jest obliczana w 15 minut. Zastanawiałem się, jak to możliwe. DBA powiedział mi, że dzieje się tak, ponieważ liczba danych, które serwer musi przetworzyć, jest mniejsza. Innymi słowy, że w dużym oryginalnym złączeniu serwer musi pracować z większą ilością danych niż w sumowanych mniejszych złączeniach. Zakładam jednak, że optymalizator zadbałby o to, aby zrobić to skutecznie z oryginalnym dużym złączeniem, dzieląc sprzężenia samodzielnie i wysyłając tylko tyle kolumn, ile potrzeba do kolejnych złączeń.

Inną rzeczą, którą zrobił, jest to, że utworzył indeks na jednej z tabel tymczasowych. Jednak jeszcze raz pomyślałem, że optymalizator utworzy odpowiednie tabele skrótów, jeśli to konieczne, i ogólnie lepiej zoptymalizuje obliczenia.

Rozmawiałem o tym z naszym DBA, ale on sam nie był pewien, co spowodowało poprawę czasu przetwarzania. Właśnie wspomniał, że nie obwiniłby serwera, ponieważ obliczenie tak dużych danych może być przytłaczające i że możliwe jest, że optymalizator nie będzie w stanie przewidzieć najlepszego planu wykonania ... Rozumiem to, ale chciałbym uzyskać bardziej szczegółową odpowiedź na pytanie dlaczego.

Tak więc pytania są następujące:

1. Co może być przyczyną dużej poprawy?

2. Czy to standardowa procedura dzielenia dużych złączeń na mniejsze?

3. Czy ilość danych, które serwer musi przetworzyć, jest naprawdę mniejsza w przypadku wielu mniejszych połączeń?

Oto oryginalne zapytanie:

    Insert Into FinalResult_Base
SELECT       
    TC.TestCampaignContainerId,
    TC.CategoryId As TestCampaignCategoryId,
    TC.Grade,
    TC.TestCampaignId,    
    T.TestSetId
    ,TL.TestId
    ,TSK.CategoryId
    ,TT.[TestletId]
    ,TL.SectionNo
    ,TL.Difficulty
    ,TestletName = Char(65+TL.SectionNo) + CONVERT(varchar(4),6 - TL.Difficulty) 
    ,TQ.[QuestionId]
    ,TS.StudentId
    ,TS.ClassId
    ,RA.SubjectId
    ,TQ.[QuestionPoints] 
    ,GoodAnswer  = Case When TQ.[QuestionPoints] Is null Then 0
                      When TQ.[QuestionPoints] > 0 Then 1 
                      Else 0 End
    ,WrongAnswer = Case When TQ.[QuestionPoints] = 0 Then 1 
                      When TQ.[QuestionPoints] Is null Then 1
                     Else 0 End
    ,NoAnswer    = Case When TQ.[QuestionPoints] Is null Then 1 Else 0 End
    ,TS.Redizo
    ,TT.ViewCount
    ,TT.SpentTime
    ,TQ.[Position]  
    ,RA.SpecialNeeds        
    ,[Version] = 1 
    ,TestAdaptationId = TA.Id
    ,TaskId = TSK.TaskId
    ,TaskPosition = TT.Position
    ,QuestionRate = Q.Rate
    ,TestQuestionId = TQ.Guid
    ,AnswerType = TT.TestletAnswerTypeId
FROM 
    [TestQuestion] TQ WITH (NOLOCK)
    Join [TestTask] TT WITH (NOLOCK)            On TT.Guid = TQ.TestTaskId
    Join [Question] Q WITH (NOLOCK)         On TQ.QuestionId =  Q.QuestionId
    Join [Testlet] TL WITH (NOLOCK)         On TT.TestletId  = TL.Guid 
    Join [Test]     T WITH (NOLOCK)         On TL.TestId     =  T.Guid
    Join [TestSet] TS WITH (NOLOCK)         On T.TestSetId   = TS.Guid 
    Join [RoleAssignment] RA WITH (NOLOCK)  On TS.StudentId  = RA.PersonId And RA.RoleId = 1
    Join [Task] TSK WITH (NOLOCK)       On TSK.TaskId = TT.TaskId
    Join [Category] C WITH (NOLOCK)     On C.CategoryId = TSK.CategoryId
    Join [TimeWindow] TW WITH (NOLOCK)      On TW.Id = TS.TimeWindowId 
    Join [TestAdaptation] TA WITH (NOLOCK)  On TA.Id = TW.TestAdaptationId
    Join [TestCampaign] TC WITH (NOLOCK)        On TC.TestCampaignId = TA.TestCampaignId 
WHERE
    T.TestTypeId = 1    -- eliminuji ankety 
    And t.ProcessedOn is not null -- ne vsechny, jen dokoncene
    And TL.ShownOn is not null
    And TS.Redizo not in (999999999, 111111119)
END;

Nowe podzielone złączenia po świetnej pracy DBA:

    SELECT       
    TC.TestCampaignContainerId,
    TC.CategoryId As TestCampaignCategoryId,
    TC.Grade,
    TC.TestCampaignId,    
    T.TestSetId
    ,TL.TestId
    ,TL.SectionNo
    ,TL.Difficulty
    ,TestletName = Char(65+TL.SectionNo) + CONVERT(varchar(4),6 - TL.Difficulty) -- prevod na A5, B4, B5 ...
    ,TS.StudentId
    ,TS.ClassId
    ,TS.Redizo
    ,[Version] = 1 -- ? 
    ,TestAdaptationId = TA.Id
    ,TL.Guid AS TLGuid
    ,TS.TimeWindowId
INTO
    [#FinalResult_Base_1]
FROM 
    [TestSet] [TS] WITH (NOLOCK)
    JOIN [Test] [T] WITH (NOLOCK) 
        ON [T].[TestSetId] = [TS].[Guid] AND [TS].[Redizo] NOT IN (999999999, 111111119) AND [T].[TestTypeId] = 1 AND [T].[ProcessedOn] IS NOT NULL
    JOIN [Testlet] [TL] WITH (NOLOCK)
        ON [TL].[TestId] = [T].[Guid] AND [TL].[ShownOn] IS NOT NULL
    JOIN [TimeWindow] [TW] WITH (NOLOCK)
        ON [TW].[Id] = [TS].[TimeWindowId] AND [TW].[IsActive] = 1
    JOIN [TestAdaptation] [TA] WITH (NOLOCK)
        ON [TA].[Id] = [TW].[TestAdaptationId] AND [TA].[IsActive] = 1
    JOIN [TestCampaign] [TC] WITH (NOLOCK)
        ON [TC].[TestCampaignId] = [TA].[TestCampaignId] AND [TC].[IsActive] = 1
    JOIN [TestCampaignContainer] [TCC] WITH (NOLOCK)
        ON [TCC].[TestCampaignContainerId] = [TC].[TestCampaignContainerId] AND [TCC].[IsActive] = 1
    ;

 SELECT       
    FR1.TestCampaignContainerId,
    FR1.TestCampaignCategoryId,
    FR1.Grade,
    FR1.TestCampaignId,    
    FR1.TestSetId
    ,FR1.TestId
    ,TSK.CategoryId AS [TaskCategoryId]
    ,TT.[TestletId]
    ,FR1.SectionNo
    ,FR1.Difficulty
    ,TestletName = Char(65+FR1.SectionNo) + CONVERT(varchar(4),6 - FR1.Difficulty) -- prevod na A5, B4, B5 ...
    ,FR1.StudentId
    ,FR1.ClassId
    ,FR1.Redizo
    ,TT.ViewCount
    ,TT.SpentTime
    ,[Version] = 1 -- ? 
    ,FR1.TestAdaptationId
    ,TaskId = TSK.TaskId
    ,TaskPosition = TT.Position
    ,AnswerType = TT.TestletAnswerTypeId
    ,TT.Guid AS TTGuid

INTO
    [#FinalResult_Base_2]
FROM 
    #FinalResult_Base_1 FR1
    JOIN [TestTask] [TT] WITH (NOLOCK)
        ON [TT].[TestletId] = [FR1].[TLGuid] 
    JOIN [Task] [TSK] WITH (NOLOCK)
        ON [TSK].[TaskId] = [TT].[TaskId] AND [TSK].[IsActive] = 1
    JOIN [Category] [C] WITH (NOLOCK)
        ON [C].[CategoryId] = [TSK].[CategoryId]AND [C].[IsActive] = 1
    ;    

DROP TABLE [#FinalResult_Base_1]

CREATE NONCLUSTERED INDEX [#IX_FR_Student_Class]
ON [dbo].[#FinalResult_Base_2] ([StudentId],[ClassId])
INCLUDE ([TTGuid])

SELECT       
    FR2.TestCampaignContainerId,
    FR2.TestCampaignCategoryId,
    FR2.Grade,
    FR2.TestCampaignId,    
    FR2.TestSetId
    ,FR2.TestId
    ,FR2.[TaskCategoryId]
    ,FR2.[TestletId]
    ,FR2.SectionNo
    ,FR2.Difficulty
    ,FR2.TestletName
    ,TQ.[QuestionId]
    ,FR2.StudentId
    ,FR2.ClassId
    ,RA.SubjectId
    ,TQ.[QuestionPoints] -- 1+ good, 0 wrong, null no answer
    ,GoodAnswer  = Case When TQ.[QuestionPoints] Is null Then 0
                      When TQ.[QuestionPoints] > 0 Then 1 -- cookie
                      Else 0 End
    ,WrongAnswer = Case When TQ.[QuestionPoints] = 0 Then 1 
                      When TQ.[QuestionPoints] Is null Then 1
                     Else 0 End
    ,NoAnswer    = Case When TQ.[QuestionPoints] Is null Then 1 Else 0 End
    ,FR2.Redizo
    ,FR2.ViewCount
    ,FR2.SpentTime
    ,TQ.[Position] AS [QuestionPosition]  
    ,RA.SpecialNeeds -- identifikace SVP        
    ,[Version] = 1 -- ? 
    ,FR2.TestAdaptationId
    ,FR2.TaskId
    ,FR2.TaskPosition
    ,QuestionRate = Q.Rate
    ,TestQuestionId = TQ.Guid
    ,FR2.AnswerType
INTO
    [#FinalResult_Base]
FROM 
    [#FinalResult_Base_2] FR2
    JOIN [TestQuestion] [TQ] WITH (NOLOCK)
        ON [TQ].[TestTaskId] = [FR2].[TTGuid]
    JOIN [Question] [Q] WITH (NOLOCK)
        ON [Q].[QuestionId] = [TQ].[QuestionId] AND [Q].[IsActive] = 1

    JOIN [RoleAssignment] [RA] WITH (NOLOCK)
        ON [RA].[PersonId] = [FR2].[StudentId]
        AND [RA].[ClassId] = [FR2].[ClassId] AND [RA].[IsActive] = 1 AND [RA].[RoleId] = 1

    drop table #FinalResult_Base_2;

    truncate table [dbo].[FinalResult_Base];
    insert into [dbo].[FinalResult_Base] select * from #FinalResult_Base;

    drop table #FinalResult_Base;

1 Zmniejszenie „przestrzeni wyszukiwania” w połączeniu z lepszymi statystykami dla połączeń pośrednich / późnych.

Musiałem poradzić sobie z połączeniami przy 90 stołach (projekt myszki miki), w których procesor zapytań odmówił nawet stworzenia planu. Podział takiego łączenia na 10 subjoin z 9 tabel, dramatycznie obniżył złożoność każdego łączenia, które rośnie wykładniczo z każdym dodatkowym stołem. Dodatkowo Optymalizator Kwerend traktuje je teraz jako 10 planów, spędzając (potencjalnie) więcej czasu w ogóle (Paul White może nawet mieć dane!).

Tabele wyników pośrednich będą teraz miały własne statystyki, dzięki czemu będą się łączyć znacznie lepiej niż statystyki głębokiego drzewa, które jest wypaczone wcześnie, a wkrótce potem zostanie science fiction.

Dodatkowo możesz najpierw wymusić najbardziej selektywne łączenia, zmniejszając ilość danych przesuwających się w górę drzewa. Jeśli potrafisz oszacować selektywność swoich predykatów znacznie lepiej niż Optymalizator, to dlaczego nie wymusić kolejności łączenia. Być może warto poszukać „krzaczastych planów”.

2 To powinno być rozpatrywane w moim przekonaniu, jeżeli sprawność i wydajność są ważne

3 Niekoniecznie, ale może być tak, że najbardziej selektywne sprzężenia są wykonywane wcześnie

1. Optymalizator SQLServer zwykle wykonuje dobrą robotę. Jednak jego celem nie jest wygenerowanie najlepszego możliwego planu, ale znalezienie planu, który jest wystarczająco dobry. W przypadku konkretnego zapytania z wieloma złączeniami może powodować bardzo niską wydajność. Dobrym wskazaniem takiego przypadku jest duża różnica między szacowaną a rzeczywistą liczbą wierszy w faktycznym planie wykonania. Jestem też całkiem pewien, że plan wykonania dla początkowego zapytania pokaże wiele „zagnieżdżonych pętli łączyć”, co jest wolniejsze niż „łączyć scalenie”. Ten ostatni wymaga sortowania obu danych wejściowych za pomocą tego samego klucza, co jest kosztowne i zwykle optymalizator odrzuca taką opcję. Przechowywanie wyników w tabeli tymczasowej i dodawanie odpowiednich indeksów tak, jak to robiłeś - zgaduję - w wyborze lepszego algorytmu dla dalszych połączeń (uwaga - przestrzegasz najlepszych praktyk, wypełniając najpierw tabelę temp, i dodawanie indeksów po). Ponadto SQLServer generuje i przechowuje statystyki dla tabel tymczasowych, co również pomaga wybrać odpowiedni indeks.
2. Nie mogę powiedzieć, że istnieje standard dotyczący używania tabel tymczasowych, gdy liczba złączeń jest większa niż pewna stała liczba, ale zdecydowanie jest to opcja, która może poprawić wydajność. Nie zdarza się to często, ale kilka razy miałem podobne problemy (i podobne rozwiązanie). Możesz też spróbować samemu znaleźć najlepszy plan wykonania, zapisać go i zmusić do ponownego użycia, ale zajmie to dużo czasu (nie ma 100% gwarancji, że odniesiesz sukces). Kolejna uwaga - w przypadku, gdy zestaw wyników przechowywany w tabeli tymczasowej jest stosunkowo mały (powiedzmy, że około 10 000 rekordów) zmienna tabeli działa lepiej niż tabela tymczasowa.
3. Nienawidzę mówić „to zależy”, ale to prawdopodobnie moja odpowiedź na twoje trzecie pytanie. Optymalizator musi szybko dawać wyniki; nie chcesz, aby spędzał godziny próbując znaleźć najlepszy plan; każde sprzężenie powoduje dodatkową pracę, a czasami optymalizator „gubi się”.

Cóż, zacznę od stwierdzenia, że ​​pracujesz na małych danych - 10 milionów milionów nie jest dużych. Ostatni projekt DWH, który miałem 400 milionów wierszy dodanych do tabeli faktów. NA DZIEŃ. Przechowywanie przez 5 lat.

Problem polega częściowo na sprzęcie. Ponieważ duże sprzężenia mogą zajmować DUŻO tymczasowej przestrzeni, a pamięci RAM jest tylko tyle, w momencie, gdy przepełniasz dysk, sprawy stają się znacznie wolniejsze. W związku z tym sensowne może być podzielenie pracy na mniejsze części po prostu dlatego, że podczas gdy SQL żyje w świecie zestawów i nie dba o rozmiar, serwer, na którym pracujesz, nie jest nieskończony. Jestem dość przyzwyczajony do usuwania błędów miejsca w tempdb 64 GB podczas niektórych operacji.

W przeciwnym razie, tak długo, jak długo są poprawne, optymalizator zapytań nie jest przytłoczony. Tak naprawdę to nie obchodzi, jak duży jest stół - działa na podstawie statystyk, które tak naprawdę nie rosną. TO POWIEDZIAŁO: Jeśli naprawdę masz DUŻĄ tabelę (dwucyfrowa liczba wierszy), mogą być nieco grubsze.

Istnieje również kwestia blokowania - chyba że dobrze zaprogramujesz, że duże połączenie może zablokować stół na wiele godzin. W tej chwili wykonuję operacje kopiowania 200 GB i dzielę je na smllerparty za pomocą klucza biznesowego (efektywnie zapętlonego), dzięki czemu zamki są znacznie krótsze.

Na koniec pracujemy z ograniczonym sprzętem.