Imagine that you have the following table structure:
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
and ToPositionId
are stock positions.
Some position ID:s have special meaning, for example 0
. An event from or to 0
means that stock was created or removed. From 0
could be stock from a delivery and to 0
could be a shipped order.
This table currently holds around 5.5 million rows. We calculate the stock value for each product and position into a cache table on a schedule using a query that looks something like this:
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
Even though this completes in a reasonable amount of time (around 20 seconds), I feel like this is a pretty inefficient way of calculating the stock values.
We rarely do anything but INSERT
:s in this table, but sometimes we go in and adjust the quantity or remove a row manually due to mistakes by the people generating these rows.
I had an idea of creating "checkpoints" in a separate table, calculating the value up to a specific point in time and using that as a start value when creating our stock quantity cache table:
ProductId | PositionId | Date | Quantity
-------------------------------------------------------
123 | 10002 | 2018-01-07 21:08:56 | 2
The fact that we sometimes change rows poses a problem to this, in that case we must also remember to remove any checkpoint created after the log row we changed. This could be solved by not calculating the checkpoints up until now, but leave a month between now and the last checkpoint (we very very rarely make changes that far back).
The fact that we sometimes need to change rows are hard to avoid and I would like to be able to still do this, it's not shown in this structure but the log events are sometimes tied to other records in other tables, and adding another log row to get the right quantity is sometimes not possible.
The log table is, as you can imagine, growing pretty fast and the time to calculate will only increase with time.
So to my question, how would you solve this? Is there a more efficient way of calculating the current stock value? Is my idea of checkpoints a good one?
We're running SQL Server 2014 Web (12.0.5511)
Execution plan: https://www.brentozar.com/pastetheplan/?id=Bk8gyc68Q
I actually gave the wrong execution time above, 20s was the time that the complete update of the cache took. This query takes somewhere around 6-10 seconds to run (8 seconds when I created this query plan). There's also a join in this query that was not in the original question.