Obliczanie ilości zapasów na podstawie dziennika zmian

Wyobraź sobie, że masz następującą strukturę tabeli:

LogId | ProductId | FromPositionId | ToPositionId | Date                 | Quantity
-----------------------------------------------------------------------------------
1     | 123       | 0              | 10002        | 2018-01-01 08:10:22  | 5
2     | 123       | 0              | 10003        | 2018-01-03 15:15:10  | 9
3     | 123       | 10002          | 10004        | 2018-01-07 21:08:56  | 3
4     | 123       | 10004          | 0            | 2018-02-09 10:03:23  | 1

FromPositionIdi ToPositionIdsą pozycjami giełdowymi. Niektóre identyfikatory pozycji mają na przykład specjalne znaczenie 0. Zdarzenie z lub do 0oznacza, że ​​zapas został utworzony lub usunięty. Od 0może być zapas z dostawy i 0może być wysyłanym zamówieniem.

Ta tabela zawiera obecnie około 5,5 miliona wierszy. Obliczamy wartość zapasów dla każdego produktu i pozycji w tabeli pamięci podręcznej według harmonogramu, używając zapytania wyglądającego mniej więcej tak:

WITH t AS
(
    SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId 
    FROM ProductPositionLog
    GROUP BY ToPositionId, ProductId
    UNION
    SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId 
    FROM ProductPositionLog
    GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0

Chociaż kończy się to w rozsądnym czasie (około 20 sekund), wydaje mi się, że jest to dość nieefektywny sposób obliczania wartości zapasów. Rzadko robimy nic opróczINSERT : s w tej tabeli, ale czasami wchodzimy i dostosowujemy ilość lub ręcznie usuwamy wiersz z powodu błędów osób generujących te wiersze.

Wpadłem na pomysł utworzenia „punktów kontrolnych” w osobnej tabeli, obliczenia wartości do określonego momentu w czasie i wykorzystania jej jako wartości początkowej podczas tworzenia naszej tabeli pamięci podręcznej ilości zapasów:

ProductId | PositionId | Date                | Quantity
-------------------------------------------------------
123       | 10002      | 2018-01-07 21:08:56 | 2

Fakt, że czasami zmieniamy wiersze, stanowi problem, w takim przypadku musimy również pamiętać o usunięciu dowolnego punktu kontrolnego utworzonego po zmianie wiersza dziennika. Można to rozwiązać, nie obliczając punktów kontrolnych do tej pory, ale pozostawiając miesiąc między teraz a ostatnim punktem kontrolnym (bardzo rzadko dokonujemy zmian tak daleko wstecz).

Trudno uniknąć faktu, że czasami musimy zmieniać wiersze i chciałbym móc to nadal robić, nie jest to pokazane w tej strukturze, ale zdarzenia dziennika są czasami powiązane z innymi rekordami w innych tabelach i dodają kolejny wiersz dziennika uzyskanie odpowiedniej ilości jest czasami niemożliwe.

Tabela dzienników rośnie, jak można sobie wyobrazić, dość szybko, a czas obliczeń wydłuża się z czasem.

Więc na moje pytanie, jak byś to rozwiązał? Czy istnieje bardziej skuteczny sposób obliczania bieżącej wartości zapasów? Czy mój pomysł na punkty kontrolne jest dobry?

Prowadzimy SQL Server 2014 Web (12.0.5511)

Plan wykonania: https://www.brentozar.com/pastetheplan/?id=Bk8gyc68Q

Właściwie podałem niepoprawny czas wykonania powyżej, 20s był czasem, który zajęła pełna aktualizacja pamięci podręcznej. Uruchomienie tego zapytania zajmuje około 6-10 sekund (8 sekund, kiedy utworzyłem ten plan zapytań). W tym zapytaniu jest również złączenie, którego nie było w pierwotnym pytaniu.

Czasami możesz poprawić wydajność zapytania, wykonując trochę dostrajania zamiast zmiany całego zapytania. Zauważyłem w twoim aktualnym planie zapytań, że twoje zapytanie przelewa się do tempdb w trzech miejscach. Oto jeden przykład:

Rozwiązanie tych wycieków tempdb może poprawić wydajność. Jeśli Quantityjest zawsze nieujemna następnie można wymienić UNIONz UNION ALLktórego prawdopodobnie ulegnie zmianie operatora unia hash do czegoś innego, który nie wymaga dotacji pamięci. Twoje inne wycieki tempdb są spowodowane problemami z oszacowaniem liczności. Korzystasz z SQL Server 2014 i używasz nowego CE, więc poprawienie szacunków liczności może być trudne, ponieważ optymalizator kwerendy nie używa statystyk wielokolumnowych. Jako szybką poprawkę rozważ skorzystanie ze MIN_MEMORY_GRANTwskazówki dotyczącej zapytania udostępnionej w SQL Server 2014 SP2. Przydział pamięci dla zapytania wynosi tylko 49104 KB, a maksymalny dostępny przydział to 5054840 KB, więc mam nadzieję, że zwiększenie go nie wpłynie zbytnio na współbieżność. 10% to rozsądne początkowe przypuszczenie, ale może być konieczne dostosowanie go i wykonanie w zależności od sprzętu i danych. Podsumowując, tak może wyglądać Twoje zapytanie:

WITH t AS
(
    SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId 
    FROM ProductPositionLog
    GROUP BY ToPositionId, ProductId
    UNION ALL
    SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId 
    FROM ProductPositionLog
    GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0
OPTION (MIN_GRANT_PERCENT = 10);

Jeśli chcesz dalej poprawić wydajność, zalecamy wypróbowanie indeksowanych widoków zamiast budowania i utrzymywania własnej tabeli punktów kontrolnych. Widoki indeksowane są znacznie łatwiejsze do uzyskania niż niestandardowe rozwiązanie obejmujące własny zmaterializowany stół lub wyzwalacze. Dodadzą niewielką część kosztów ogólnych do wszystkich operacji DML, ale może to umożliwić usunięcie niektórych indeksów nieklastrowych, które aktualnie masz. Widoki indeksowane wydają się być obsługiwane w internetowej wersji produktu.

Istnieją pewne ograniczenia dotyczące widoków indeksowanych, dlatego musisz utworzyć ich parę. Poniżej znajduje się przykładowa implementacja wraz z fałszywymi danymi, których użyłem do testowania:

CREATE TABLE dbo.ProductPositionLog (
    LogId BIGINT NOT NULL,
    ProductId BIGINT NOT NULL,
    FromPositionId BIGINT NOT NULL,
    ToPositionId BIGINT NOT NULL,
    Quantity INT NOT NULL,
    FILLER VARCHAR(20),
    PRIMARY KEY (LogId)
);

INSERT INTO dbo.ProductPositionLog WITH (TABLOCK)
SELECT RN, RN % 100, RN % 3999, 3998 - (RN % 3999), RN % 10, REPLICATE('Z', 20)
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

CREATE INDEX NCI1 ON dbo.ProductPositionLog (ToPositionId, ProductId) INCLUDE (Quantity);
CREATE INDEX NCI2 ON dbo.ProductPositionLog (FromPositionId, ProductId) INCLUDE (Quantity);

GO    

CREATE VIEW ProductPositionLog_1
WITH SCHEMABINDING  
AS  
   SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
    FROM dbo.ProductPositionLog
    WHERE ToPositionId <> 0
    GROUP BY ToPositionId, ProductId
GO  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   
    ON ProductPositionLog_1 (PositionId, ProductId);  
GO  

CREATE VIEW ProductPositionLog_2
WITH SCHEMABINDING  
AS  
   SELECT FromPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
    FROM dbo.ProductPositionLog
    WHERE FromPositionId <> 0
    GROUP BY FromPositionId, ProductId
GO  

CREATE UNIQUE CLUSTERED INDEX IDX_V2   
    ON ProductPositionLog_2 (PositionId, ProductId);  
GO  

Bez widoków indeksowanych zapytanie kończy się na moim komputerze około 2,7 sekundy. Mam podobny plan do twojego, z wyjątkiem moich szeregowych:

Uważam, że musisz zapytać indeksowane widoki z NOEXPANDpodpowiedź, ponieważ nie jesteś w wersji Enterprise. Oto jeden ze sposobów, aby to zrobić:

WITH t AS
(
    SELECT PositionId, Quantity, ProductId 
    FROM ProductPositionLog_1 WITH (NOEXPAND)
    UNION ALL
    SELECT PositionId, Quantity, ProductId 
    FROM ProductPositionLog_2 WITH (NOEXPAND)
)
SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0;

To zapytanie ma prostszy plan i kończy się na mniej niż 400 ms na moim komputerze:

Najlepsze jest to, że nie będziesz musiał zmieniać żadnego kodu aplikacji, który ładuje dane do ProductPositionLogtabeli. Musisz po prostu sprawdzić, czy narzut DML dla pary indeksowanych widoków jest akceptowalny.

Nie sądzę, żeby twoje obecne podejście było tak nieefektywne. Wydaje się to dość prostym sposobem na zrobienie tego. Innym podejściem może być użycie UNPIVOTklauzuli, ale nie jestem pewien, czy byłaby to poprawa wydajności. Zaimplementowałem oba podejścia z poniższym kodem (nieco ponad 5 milionów wierszy), a każde z nich wróciło za około 2 sekundy na moim laptopie, więc nie jestem pewien, co tak różni się w moim zestawie danych w porównaniu do prawdziwego. Nie dodałem nawet żadnych indeksów (oprócz klucza podstawowego LogId).

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductPositionLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductPositionLog] (
[LogId] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[ProductId] int NULL,
[FromPositionId] int NULL,
[ToPositionId] int NULL,
[Date] datetime NULL,
[Quantity] int NULL
)
END;
GO

SET IDENTITY_INSERT [ProductPositionLog] ON

INSERT INTO [ProductPositionLog] ([LogId], [ProductId], [FromPositionId], [ToPositionId], [Date], [Quantity])
VALUES (1, 123, 0, 1, '2018-01-01 08:10:22', 5)
INSERT INTO [ProductPositionLog] ([LogId], [ProductId], [FromPositionId], [ToPositionId], [Date], [Quantity])
VALUES (2, 123, 0, 2, '2018-01-03 15:15:10', 9)
INSERT INTO [ProductPositionLog] ([LogId], [ProductId], [FromPositionId], [ToPositionId], [Date], [Quantity])
VALUES (3, 123, 1, 3, '2018-01-07 21:08:56', 3)
INSERT INTO [ProductPositionLog] ([LogId], [ProductId], [FromPositionId], [ToPositionId], [Date], [Quantity])
VALUES (4, 123, 3, 0, '2018-02-09 10:03:23', 2)
INSERT INTO [ProductPositionLog] ([LogId], [ProductId], [FromPositionId], [ToPositionId], [Date], [Quantity])
VALUES (5, 123, 2, 3, '2018-02-09 10:03:23', 4)
SET IDENTITY_INSERT [ProductPositionLog] OFF

GO

INSERT INTO ProductPositionLog
SELECT ProductId + 1,
  FromPositionId + CASE WHEN FromPositionId = 0 THEN 0 ELSE 1 END,
  ToPositionId + CASE WHEN ToPositionId = 0 THEN 0 ELSE 1 END,
  [Date], Quantity
FROM ProductPositionLog
GO 20

-- Henrik's original solution.
WITH t AS
(
    SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId 
    FROM ProductPositionLog
    GROUP BY ToPositionId, ProductId
    UNION
    SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId 
    FROM ProductPositionLog
    GROUP BY FromPositionId, ProductId
)
SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0
GO

-- Same results via unpivot
SELECT ProductId, PositionId,
  SUM(CAST(TransferType AS INT) * Quantity) AS Quantity
FROM   
   (SELECT ProductId, Quantity, FromPositionId AS [-1], ToPositionId AS [1]
   FROM ProductPositionLog) p  
  UNPIVOT  
     (PositionId FOR TransferType IN 
        ([-1], [1])
  ) AS unpvt
WHERE PositionId <> 0
GROUP BY ProductId, PositionId

Jeśli chodzi o punkty kontrolne, wydaje mi się to rozsądnym pomysłem. Ponieważ mówisz, że aktualizacje i usunięcia są naprawdę rzadkie, po prostu dodam wyzwalacz uruchamiający się ProductPositionLogpodczas aktualizacji i usuwania, który odpowiednio dostosowuje tabelę punktów kontrolnych. Dla pewności od czasu do czasu ponownie obliczałem tabele punktów kontrolnych i pamięci podręcznej.