Расчет количества запасов на основе журнала изменений

Представьте, что у вас есть следующая структура таблицы:

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

FromPositionId и ToPositionId - акции позиции. Некоторые идентификаторы позиции имеют особое значение, например 0. Событие от или до 0 означает, что запас был создан или удален. Из 0 может быть запас с доставкой и до 0 может быть отправленным заказом.

В настоящее время эта таблица содержит около 5,5 миллионов строк. Мы вычисляем значение запаса для каждого продукта и позиции в таблицу кеша по расписанию, используя запрос, который выглядит примерно так:

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

Несмотря на то, что это завершается в разумные промежутки времени (около 20 секунд), я чувствую, что это довольно неэффективный способ расчета стоимости акций. Мы редко делаем что-либо, кроме INSERT: s в этой таблице, но иногда мы входим и корректируем количество или удаляем строку вручную из-за ошибок людьми, генерирующими эти строки.

У меня возникла идея создать «контрольные точки» в отдельной таблице, вычисляя значение до определенного момента времени и используя это как начальное значение при создании таблицы кеша количества запасов:

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

Тот факт, что мы иногда меняем строки, создает проблему для этого, в этом случае мы также должны помнить об удалении любой контрольной точки, созданной после строки журнала, которую мы изменили. Это можно решить, не вычисляя контрольные точки до сих пор, но оставляя месяц между последним контрольным пунктом (мы очень редко делаем изменения, которые находятся далеко назад).

То, что нам иногда нужно менять строки, трудно избежать, и я хотел бы иметь возможность все еще это делать, он не показан в этой структуре, но события журнала иногда привязаны к другим записям в других таблицах, и добавление другая строка журнала, чтобы получить нужное количество, иногда не представляется возможным.

Таблица журналов, как вы можете себе представить, быстро растет, и время для вычисления будет только возрастать со временем.

Итак, на мой вопрос, как бы вы решили это? Существует ли более эффективный способ расчета текущей стоимости акций? Является ли моя идея контрольных точек хорошей?

У нас работает SQL Server 2014 Web (12.0.5511)

План выполнения: https://www.brentozar.com/pastetheplan/?id= Bk8gyc68Q

Я на самом деле дал неправильное время выполнения выше, 20 секунд было временем полного обновления кэша. Этот запрос занимает около 6-10 секунд для запуска (8 секунд, когда я создал этот план запроса). В этом запросе также есть соединение, которое не было в исходном вопросе.

10 голосов | спросил Henrik 24 PM00000010000002131 2018, 13:18:21

2 ответа


6

Иногда вы можете улучшить производительность запросов, просто сделав небольшую настройку вместо изменения всего вашего запроса. Я заметил в вашем фактическом плане запроса, что ваш запрос разливается в tempdb в трех местах. Вот один пример:

 tempdb spills

Разрешение этих утечек tempdb может повысить производительность. Если Quantity всегда неотрицателен, вы можете заменить UNION с помощью UNION ALL, который, скорее всего, изменит оператор объединения хешей на что-то другое, для которого не требуется выделение памяти. Другие утечки tempdb вызваны проблемами с оценкой мощности. Вы работаете на SQL Server 2014 и используете новый CE, поэтому может быть сложно улучшить оценки мощности, поскольку оптимизатор запросов не будет использовать статистику с несколькими столбцами. В качестве быстрого исправления рассмотрите возможность использования подсказки запроса MIN_MEMORY_GRANT, доступной в SQL Server 2014 SP2 . Предоставление памяти для вашего запроса - всего 49104 КБ, а максимальный доступный грант - 5054840 КБ, поэтому, надеюсь, он набросится на него слишком сильно. 10% - разумное исходное предположение, но вам может потребоваться его корректировка и выполнение в зависимости от вашего оборудования и данных. Объединив все это, это будет выглядеть так:

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);

Если вы хотите улучшить производительность, я рекомендую попробовать индексированные представления вместо создания и поддержания собственной таблицы контрольных точек. Индексированные представления значительно легче получить, чем пользовательское решение, включающее вашу собственную материализованную таблицу или триггеры. Они добавят небольшое количество накладных расходов ко всем операциям DML, но это может позволить вам удалить некоторые из некластеризованных индексов, которые у вас есть в настоящее время. Индексированные представления выглядят как поддерживается в веб-версии продукта.

Есть некоторые ограничения на индексированные представления, поэтому вам нужно создать пару из них. Ниже приведен пример реализации, а также поддельные данные, которые я использовал для тестирования:

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  

Без индексированных просмотров запрос занимает около 2,7 секунды для завершения работы на моей машине. Я получаю аналогичный план для вас, кроме моих серий в серийном:

 введите описание изображения здесь>> </a> </p>

<p> Я считаю, что вам нужно запросить индексированные представления с подсказкой <code>---- +: = 6 =: + ----</code>, потому что вы не находитесь в корпоративной версии. Вот один из способов сделать это: </p>

<pre><code>---- +: = 7 = + ----</code></pre>

<p> Этот запрос имеет более простой план и заканчивается менее чем на 400 мс на моей машине: </p>

<p> <a href= введите описание изображения здесь>> </a> </p>

<p> Лучшая часть заключается в том, что вам не придется менять какой-либо код приложения, загружающий данные в таблицу <code>---- +: = 8 =: + ----</code>. Вам просто нужно убедиться, что служебные данные DML для пары индексированных представлений приемлемы. </p></body></html>

ответил Joe Obbish 25 AM00000060000004931 2018, 06:23:49
2

Я действительно не думаю, что ваш нынешний подход - это все, что неэффективно. Кажется, это довольно простой способ сделать это. Другой подход может заключаться в использовании предложения UNPIVOT, но я не уверен, что это будет улучшение производительности. Я реализовал оба подхода с приведенным ниже кодом (всего более 5 миллионов строк), и каждый из них вернулся примерно через 2 секунды на моем ноутбуке, поэтому я не уверен, что в моем наборе данных по сравнению с реальным. Я даже не добавлял никаких индексов (кроме первичного ключа в 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

Что касается контрольных точек, мне кажется разумной идеей. Поскольку вы говорите, что обновления и удаления действительно нечасты, я бы просто добавил триггер на ProductPositionLog, который запускается при обновлении и удалении, и который настраивает таблицу контрольных точек. И просто чтобы быть уверенным, я периодически пересчитывал контрольную точку и кеш-таблицы с нуля.

ответил Scott M 24 PM00000040000003531 2018, 16:34:35

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132