Zapytanie jest wykonywane bardzo wolno, czy jest jakiś sposób na jego dalsze ulepszenie?

9

Mam następujące zapytanie i ze względu na wiele SUM wywołań funkcji moje zapytanie działa zbyt wolno. Mam dużo danych w swojej bazie danych i chciałbym otrzymać raport z bieżącego roku i zeszłego roku (Ostatnie 30 dni, Ostatnie 90 dni i ostatnie 365 dni) dla każdego:

SELECT 
    b.id as [ID]
    ,d.[Title] as [Title]
    ,e.Class as [Class]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 30 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 30 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 90 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 90 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 365 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 365 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 30 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 30 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 90 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 90 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 365 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 365 Days Col2]


    FROM 
    tb1 a
INNER JOIN 
    tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN 
    tb3 c on b.fid = c.col5
INNER JOIN       
    tb4 d on c.id = d.col6
INNER JOIN 
    tb5 e on c.col7 = e.id
GROUP BY
    b.id, d.Title, e.Class

Czy ktoś ma pojęcie, jak mogę ulepszyć moje zapytanie, aby działało szybciej?

EDYCJA: Zachęcono mnie do przeniesienia DATEADDwywołania funkcji do whereinstrukcji i załadowania najpierw dwóch lat, a następnie filtrowania ich w kolumnach, ale nie jestem pewien, czy sugerowana odpowiedź została wykonana i działa, można ją znaleźć tutaj: https: // stackoverflow. com / a / 59944426/12536284

Jeśli zgadzasz się z powyższym rozwiązaniem, pokaż mi, jak mogę zastosować go w bieżącym zapytaniu?

Po prostu, używam tego SP w C #, Entity Framework (DB-First), coś takiego:

var result = MyDBEntities.CalculatorSP();
c#  sql  sql-server  stored-procedures 
— Jim
źródło
4
Pokaż nam swój plan egzekucji ...
— Dale K
1
NA cokolwiek - to może spowolnić zapytanie
— Fabio
4
— Dale K
2
Ponownie, proszę opublikować plan wykonania.
— SQL Police
2
Nadal nie widzimy Execution Plan. Proszę zamieścić
— Arun Palanisamy

Odpowiedzi:

10

Jak już wspomniano, plan wykonania będzie w tym przypadku naprawdę pomocny. Na podstawie tego, co pokazałeś, wydaje się, że wyodrębniłeś 12 kolumn z 15 wszystkich kolumn tb1 (a), więc możesz spróbować uruchomić zapytanie bez żadnego łączenia i tylko w tb1celu sprawdzenia, czy zapytanie działa zgodnie z oczekiwaniami. Ponieważ nie widzę nic złego w twoich wywołaniach funkcji SUM, wydaje mi się, że masz problem ze swoimi połączeniami, sugeruję wykonanie następujących czynności. Możesz zacząć od wykluczenia na przykład ostatniego złączenia INNER JOIN tb5 e on c.col7 = e.idi wszelkich powiązanych z nim zastosowań, takich jak e.Class as [Class]ie.Classw twojej grupie według oświadczenia. Nie zamierzamy go całkowicie wykluczyć, to tylko test, aby upewnić się, czy problem jest z tym, czy nie, jeśli twoje zapytanie działa lepiej i zgodnie z oczekiwaniami możesz spróbować użyć tabeli tymczasowej jako obejścia zamiast ostatniego łączenia , coś takiego:

SELECT *
INTO #Temp
FROM
  (
     select * from tb5
  ) As tempTable;

SELECT 
    b.id as [ID]
    ,d.[Title] as [Title]
    ,e.Class as [Class]

    -- SUM Functions

FROM 
    tb1 a
INNER JOIN 
    tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN 
    tb3 c on b.fid = c.col5
INNER JOIN       
    tb4 d on c.id = d.col6
INNER JOIN 
    #Temp e on c.col7 = e.id
GROUP BY
    b.id, d.Title, e.Class

W rzeczywistości tabele tymczasowe to tabele tymczasowo istniejące na serwerze SQL. Tabele tymczasowe są przydatne do przechowywania bezpośrednich zestawów wyników, do których dostęp jest uzyskiwany wielokrotnie. Możesz przeczytać więcej na ten temat tutaj https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/ A tutaj https://codingsight.com/introduction-to-temporary-tables-in -sql-server /

Również gorąco polecam, jeśli używasz procedura przechowywana, ustaw NOCOUNTna ON, może też stanowić znaczący wzrost wydajności, ponieważ ruch w sieci jest znacznie zmniejszona:

SET NOCOUNT ON
SELECT *
INTO #Temp
-- The rest of code

W oparciu o to :

SET NOCOUNT ON to instrukcja set, która zapobiega komunikatowi, który pokazuje liczbę wierszy, na które wpływ mają instrukcje zapytania T-SQL. Jest to używane w procedurach przechowywanych i wyzwalaczach, aby uniknąć wyświetlania komunikatu dotyczącego wierszy. Użycie SET NOCOUNT ON w procedurze przechowywanej może znacznie poprawić wydajność procedury przechowywanej.

— Salah Akbari
źródło
1
Czy możesz wyjaśnić, dlaczego kopiowanie całości tb5do #Temptabeli i dołączanie do tabeli tymczasowej działa szybciej niż łączenie tb5bezpośrednie? niestety zawierają te same dane (i #Tempmoże brakować indeksu, jeśli on istniał tb5). Naprawdę nie rozumiem, dlaczego jest to bardziej wydajne (z tego co wiem, powinno być mniej wydajne kopiowanie wszystkich danych i dołączanie).
— zig
2
@zig Masz rację w tym przypadku, ale co jeśli tb5znajduje się na innym serwerze? W takim przypadku użycie tabeli tymczasowej jest zdecydowanie szybsze niż bezpośrednie dołączenie do innego serwera. To była tylko propozycja przetestowania i sprawdzenia, czy coś się zmieniło. Miałem podobną sytuację w przeszłości i wydaje się na szczęście, że tabela temp pomogła OP również w tym przypadku.
— Salah Akbari
2

Najlepszym rozwiązaniem jest wstawienie do tabeli zmiennej / tabeli skrótów (jeśli liczba wierszy jest mała, użyj zmiennej tabeli lub użyj tabeli skrótów, jeśli liczba wierszy jest dość duża). Następnie zaktualizuj agregację, a następnie w końcu wybierz ze zmiennej tabeli lub tabeli mieszającej. Konieczne jest sprawdzenie planu zapytań.

DECLARE @MYTABLE TABLE (ID INT, [Title] VARCHAR(500), [Class] VARCHAR(500),
[Current - Last 30 Days Col1] INT, [Current - Last 30 Days Col2] INT,
[Current - Last 90 Days Col1] INT,[Current - Last 90 Days Col2] INT,
[Current - Last 365 Days Col1] INT, [Current - Last 365 Days Col2] INT,
[Last year - Last 30 Days Col1] INT, [Last year - Last 30 Days Col2] INT,
[Last year - Last 90 Days Col1] INT, [Last year - Last 90 Days Col2] INT,
[Last year - Last 365 Days Col1] INT, [Last year - Last 365 Days Col2] INT)



INSERT INTO @MYTABLE(ID, [Title],[Class], 
[Current - Last 30 Days Col1], [Current - Last 30 Days Col2],
[Current - Last 90 Days Col1], [Current - Last 90 Days Col2],
[Current - Last 365 Days Col1], [Current - Last 365 Days Col2],
[Last year - Last 30 Days Col1], [Last year - Last 30 Days Col2],
[Last year - Last 90 Days Col1], [Last year - Last 90 Days Col2],
[Last year - Last 365 Days Col1], [Last year - Last 365 Days Col2]
  )
SELECT    b.id  ,d.[Title] ,e.Class ,0,0,0,0,0,0,0,0,0,0,0,0        
FROM     tb1 a
INNER JOIN   tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN   tb3 c on b.fid = c.col5
INNER JOIN   tb4 d on c.id = d.col6
INNER JOIN  tb5 e on c.col7 = e.id
GROUP BY b.id, d.Title, e.Class

UPDATE T 
SET [Current - Last 30 Days Col1]=K.[Current - Last 30 Days Col1] , 
[Current - Last 30 Days Col2]    =K.[Current - Last 30 Days Col2],
[Current - Last 90 Days Col1]    = K.[Current - Last 90 Days Col1], 
[Current - Last 90 Days Col2]    =K.[Current - Last 90 Days Col2] ,
[Current - Last 365 Days Col1]   =K.[Current - Last 365 Days Col1], 
[Current - Last 365 Days Col2]   =K.[Current - Last 365 Days Col2],
[Last year - Last 30 Days Col1]  =K.[Last year - Last 30 Days Col1],
 [Last year - Last 30 Days Col2] =K.[Last year - Last 30 Days Col2],
[Last year - Last 90 Days Col1]  =K.[Last year - Last 90 Days Col1], 
[Last year - Last 90 Days Col2]  =K.[Last year - Last 90 Days Col2],
[Last year - Last 365 Days Col1] =K.[Last year - Last 365 Days Col1],
 [Last year - Last 365 Days Col2]=K.[Last year - Last 365 Days Col2]
    FROM @MYTABLE T JOIN 
     (
SELECT 
    b.id as [ID]
    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col1 ELSE 0 END),0) as [Current - Last 30 Days Col1]
    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col2 ELSE 0 END),0) as [Current - Last 30 Days Col2]

    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col1 ELSE 0 END),0) as [Current - Last 90 Days Col1]
    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col2 ELSE 0 END),0) as [Current - Last 90 Days Col2]

    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END),0) as [Current - Last 365 Days Col1]
    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END),0) as [Current - Last 365 Days Col2]

    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col1 ELSE 0 END),0) as [Last year - Last 30 Days Col1]
    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col2 ELSE 0 END),0) as [Last year - Last 30 Days Col2]

    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col1 ELSE 0 END),0) as [Last year - Last 90 Days Col1]
    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col2 ELSE 0 END),0) as [Last year - Last 90 Days Col2]

    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END),0) as [Last year - Last 365 Days Col1]
    ,ISNULL(Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END),0) as [Last year - Last 365 Days Col2]
    FROM     tb1 a
INNER JOIN   tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN   tb3 c on b.fid = c.col5
INNER JOIN   tb4 d on c.id = d.col6
INNER JOIN  tb5 e on c.col7 = e.id
GROUP BY    b.id
) AS K ON T.ID=K.ID


SELECT *
FROM @MYTABLE
— Harshana
źródło
0

Zakładam, że tb1 jest dużą tabelą (względem tb2, tb3, tb4 i tb5).

Jeśli tak, sensowne jest tutaj ograniczenie wyboru tej tabeli (z klauzulą ​​WHERE).

Jeśli używana jest tylko niewielka część tb1, na przykład ponieważ połączenia za pomocą tb2, tb3, tb4 i tb5 zmniejszają potrzebne wiersze do zaledwie kilku procent, należy sprawdzić, czy tabele są indeksowane w kolumnach używanych w złączeniach .

Jeśli używana jest duża część tb1, warto zgrupować wyniki przed dołączeniem do tb2, tb3, tb4 i tb5. Poniżej znajduje się przykład tego.

SELECT 
    b.id as [ID]
    ,d.[Title] as [Title]
    ,e.Class as [Class]
    ,SUM(a.[Current - Last 30 Days Col1]) AS [Current - Last 30 Days Col1]
    ,SUM(a.[Current - Last 30 Days Col2]) AS [Current - Last 30 Days Col2]
    ,SUM(a.[Current - Last 90 Days Col1]) AS [Current - Last 90 Days Col1]
    -- etc.
    FROM (
      SELECT a.id, a.col3

      ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 30 Days Col1]
      ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 30 Days Col2]

      ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 90 Days Col1]
      ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 90 Days Col2]

      ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 365 Days Col1]
      ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 365 Days Col2]

      ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 30 Days Col1]
      ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 30 Days Col2]

      ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 90 Days Col1]
      ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 90 Days Col2]

      ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 365 Days Col1]
      ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 365 Days Col2]

      FROM  tb1 a
      WHERE a.DateCol >= DATEADD(YEAR,-2,GETDATE())
      GROUP BY a.id, a.col3
    ) AS a
INNER JOIN 
    tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN 
    tb3 c on b.fid = c.col5
INNER JOIN       
    tb4 d on c.id = d.col6
INNER JOIN 
    tb5 e on c.col7 = e.id
GROUP BY
    b.id, d.Title, e.Class
— Gert-Jan
źródło
O wiele lepiej byłoby najpierw wyświetlić plan wykonania, a następnie podjąć decyzję o tworzeniu indeksów i odtwarzaniu statystyk.
— SQL Police
Naprawdę nienawidzę tego, że mój post otrzymał wynik negatywny bez wyjaśnienia dlaczego. Oczywiście zgadzam się, że aby dotrzeć do sedna problemu z wydajnością, należy sprawdzić plan wykonania. Powiedziawszy to, podtrzymuję moje zalecenie dotyczące sprawdzania indeksów dla wszelkich kluczy obcych, które są istotne w zapytaniu.
— Gert-
1
„Zakładasz” coś bez wiedzy. Więc piszesz odpowiedź na podstawie nieznanego. Dlatego głosuj negatywnie. Lepiej poinstruować PO, aby poprawił swoje pytanie, publikując plan wykonania.
— SQL Police
To nie wszystko, co napisałem. Osobiście głosowałbym tylko wtedy, gdy odpowiedź jest zła lub zła, a nie, gdy po prostu się nie zgadzam. Ale dzięki za odpowiedź.
— Gert-
W pewnym sensie jest to złe, ponieważ jak możesz udowodnić, że jest poprawne?
— SQL Police
0

Aby zoptymalizować takie obliczenia, należy rozważyć wstępne obliczenie niektórych wartości. Ideą wstępnych obliczeń jest zmniejszenie liczby wierszy, które należy odczytać lub kontynuować.

Jednym ze sposobów osiągnięcia tego jest użycie widoku indeksowanego i pozostawienie silnika, aby sam wykonał obliczenia. Ponieważ tego typu widoki mają pewne ograniczenia, możesz stworzyć prostą tabelę i zamiast tego wykonać obliczenia. Zasadniczo zależy to od potrzeb biznesowych.

Tak więc, w przykładzie poniżej tworzę tabelę z RowIDi RowDatetimekolumn oraz wstawienie 1 milion wierszy. Korzystam z widoku indeksowanego do zliczania jednostek na dzień, więc zamiast przesyłać zapytania do 1 miliona wierszy rocznie, wykonuję zapytania do 365 wierszy rocznie, aby policzyć te metryki.

DROP TABLE IF EXISTS [dbo].[DataSource];
GO

CREATE TABLE [dbo].[DataSource]
(
    [RowID] BIGINT IDENTITY(1,1) PRIMARY KEY
   ,[RowDateTime] DATETIME2
);

GO

DROP VIEW IF EXISTS [dbo].[vw_DataSource];
GO

CREATE VIEW [dbo].[vw_DataSource] WITH SCHEMABINDING
AS
SELECT YEAR([RowDateTime]) AS [Year]
      ,MONTH([RowDateTime]) AS [Month]
      ,DAY([RowDateTime]) AS [Day]
      ,COUNT_BIG(*) AS [Count]
FROM [dbo].[DataSource]
GROUP BY YEAR([RowDateTime])
        ,MONTH([RowDateTime])
        ,DAY([RowDateTime]);
GO

CREATE UNIQUE CLUSTERED INDEX [IX_vw_DataSource] ON [dbo].[vw_DataSource]
(
    [Year] ASC,
    [Month] ASC,
    [Day] ASC
);

GO

DECLARE @min bigint, @max bigint
SELECT @Min=1 ,@Max=1000000

INSERT INTO [dbo].[DataSource] ([RowDateTime])
SELECT TOP (@Max-@Min+1) DATEFROMPARTS(2019,  1.0 + floor(12 * RAND(convert(varbinary, newid()))), 1.0 + floor(28 * RAND(convert(varbinary, newid())))          )       
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2

GO


SELECT *
FROM [dbo].[vw_DataSource]


SELECT SUM(CASE WHEN DATEFROMPARTS([Year], [Month], [Day]) >= DATEADD(MONTH,-1,GETDATE()) THEN [Count] ELSE 0 END) as [Current - Last 30 Days Col1]
      ,SUM(CASE WHEN DATEFROMPARTS([Year], [Month], [Day]) >= DATEADD(QUARTER,-1,GETDATE()) THEN [Count] ELSE 0 END) as [Current - Last 90 Days Col1]
      ,SUM(CASE WHEN DATEFROMPARTS([Year], [Month], [Day]) >= DATEADD(YEAR,-1,GETDATE()) THEN [Count] ELSE 0 END) as [Current - Last 365 Days Col1]
FROM [dbo].[vw_DataSource];

Sukces takiego rozwiązania zależy w dużej mierze od sposobu dystrybucji danych i liczby posiadanych wierszy. Na przykład, jeśli masz jeden wpis dziennie dla każdego dnia roku, widok i tabela będą miały takie same dopasowanie wierszy, więc operacje we / wy nie zostaną zmniejszone.

Ponadto powyższe jest tylko przykładem zmaterializowania danych i ich odczytania. W twoim przypadku może być konieczne dodanie większej liczby kolumn definicji widoku.

— gotqn
źródło
0

Użyłbym tabeli przeglądowej „Daty”, aby połączyć moje dane z indeksem na DatesId. Używam dat jako filtra, gdy chcę przeglądać dane historyczne. Sprzężenie jest szybkie, a więc filtrowanie, ponieważ DatesId jest klastrowanym indeksem podstawowym (kluczem podstawowym). Dodaj także kolumnę z datą (jako kolumnę dołączoną) do tabeli danych.

Tabela dat zawiera następujące kolumny:

DatyId, Data, Rok, Kwartał, Rok Kwartał, MiesiącNum, MonthName Krótki, Rok Tydzień, WeekNum, DayOfYear, DayOfMonth, DayNumOfWeek, DayName

Przykładowe dane: 20310409 2031-04-09 2031 2 2031-Q2 4 kwietnia 2031_15 15 99 9 3 środa

Możesz wysłać mi wiadomość e-mail, jeśli chcesz mieć plik CSV, aby móc go zaimportować do bazy danych, ale jestem pewien, że możesz łatwo znaleźć coś takiego online i stworzyć własny.

Dodam również kolumnę tożsamości, aby można było uzyskać liczbę całkowitą dla każdej daty. To sprawia, że ​​nieco łatwiej jest pracować, ale nie jest to wymagane.

SELECT * FROM dbo.dates where dateIndex BETWEEN (getDateIndexDate(getDate())-30 AND getDateIndexDate(getDate())+0) --30 days ago

To pozwala mi łatwo wrócić do pewnego okresu. Tworzenie własnych poglądów na ten temat jest dość łatwe. Możesz oczywiście użyć funkcji ROW_NUMBER (), aby zrobić to również przez lata, tygodnie itp.

Kiedy już będę mieć potrzebne dane, dołączam do danych. Działa bardzo szybko!

— starbyone
źródło
0

Ponieważ zawsze grupujesz wartości w oparciu o całą liczbę miesięcy, najpierw pogrupowałbym według miesięcy w podzapytanie w klauzuli from. Jest to podobne do korzystania z tabeli tymczasowej. Nie jestem pewien, czy rzeczywiście przyspieszyłoby to zapytanie.

SELECT f.id, f.[Title], f.Class,
    SUM(CASE WHEN f.MonthDiff = 1 THEN col1 ELSE 0 END) as [Current - Last 30 Days Col1],
    -- etc
FROM (
    SELECT 
        b.id,
        d.[Title],
        e.Class,
        DateDiff(Month, a.DateCol, GETDATE()) as MonthDiff,
        Sum(a.col1) as col1,
        Sum(a.col2) as col2
    FROM  tb1 a
    INNER JOIN tb2 b on a.id = b.fid and a.col3 = b.col4
    INNER JOIN tb3 c on b.fid = c.col5
    INNER JOIN tb4 d on c.id = d.col6
    INNER JOIN tb5 e on c.col7 = e.id
    WHERE a.DateCol between DATEADD(YEAR,-2,GETDATE() and GETDATE()
    GROUP BY b.id, d.Title, e.Class, DateDiff(Month,  a.DateCol, GETDATE())
) f
group by f.id, f.[Title], f.Class
— Jeremy Lakeman
źródło
-2

Aby poprawić szybkość zapytania SQL, musisz dodać indeksy. Do każdej połączonej tabeli musisz dodać jeden indeks.

Podobnie jak w tym przykładzie kodu dla Oracle:

CREATE INDEX supplier_idx
ON supplier (supplier_name);
— użytkownik12467638
źródło
to nie jest zła sugestia. widzisz z PO, że tworzona jest tabela tymczasowa bez indeksu - INNER JOIN #Temp e na c.col7 = e.id. choć w odpowiedzi jest miejsce na poprawę, nie sądzę, że powinna być oceniana masowo. szczególnie dla nowego użytkownika.
— smoore4
@ smoore4 Zgadzam się, ta opcja głosowania bez wyraźnego argumentu powinna zostać usunięta. Istnieje duże niewłaściwe wykorzystanie tej funkcjonalności
— Greggz
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.