We have an
InventoryActivity table that holds transactional changes to item quantity:
CREATE TABLE dbo.InventoryActivity( InventoryActivity_uid int IDENTITY(1,1) NOT NULL PRIMARY KEY, Organization_uid int NOT NULL, MasterInventory_uid int NOT NULL, AdjustmentType_cd varchar(20) NULL, AdjustmentReason_cd varchar(20) NULL, Quantity int NULL )
We would like an
InventorySummary which should aggregate to the current quantity for each. The summary counts should always be derivable from the the sum of the transactional records, but we have several different approaches on how to calculate aggregate counts:
- Stored Procedure
- Separate Summary Table
- Indexed View
What performance considerations should tip the scales in favor of a particular strategy?
What best practices exist? *(I know best practices borders on discussion, but I want to know what considerations would come into play to help make the decision)
Simplest option is just perform the SUM operation fresh everytime. But involves no caching and would likely cause performance issues over time.
CREATE PROCEDURE dbo.GetInventorySummary AS SELECT Organization_uid, MasterInventory_uid, SUM(Quantity) AS Quantity FROM dbo.InventoryActivity GROUP BY Organization_uid, MasterInventory_uid
We could create a table to store the current quantities. The plus side is fetching this data would be trivial. The downside is we would have to manually maintain it and keep the records in sync everytime we do a write to the InventoryActivity table.
CREATE TABLE dbo.InventorySummary( Organization_uid int NOT NULL, MasterInventory_uid int NOT NULL, Quantity int NOT NULL, PRIMARY KEY (Organization_uid, MasterInventory_uid) )
Triggers could help alleviate some of that maintenance.
CREATE TRIGGER dbo.InventoryActivity_I ON dbo.InventoryActivity AFTER INSERT AS CREATE TABLE #InsertSummaryTemp ( Organization_uid int, MasterInventory_uid int, Quantity int ) INSERT INTO #InsertSummaryTemp SELECT Organization_uid, MasterInventory_uid, SUM(Quantity) AS Quantity FROM INSERTED GROUP BY Organization_uid, MasterInventory_uid -- UPDATE EXISTING RECORDS UPDATE InventorySummary SET Quantity = s.Quantity + i.Quantity FROM InventorySummary s JOIN #InsertSummaryTemp i ON s.Organization_uid = i.Organization_uid AND s.MasterInventory_uid = i.MasterInventory_uid -- INSERT NEW RECORDS INSERT INTO InventorySummary (Organization_uid, MasterInventory_uid, Quantity) SELECT i.Organization_uid, i.MasterInventory_uid, i.Quantity FROM #InsertSummaryTemp i LEFT JOIN InventorySummary s ON i.Organization_uid = s.Organization_uid AND i.MasterInventory_uid = s.MasterInventory_uid WHERE s.MasterInventory_uid IS NULL
Borrowed from this and this we could create an
Indexed View. Which lowers the maintenance cost associated with option 2. However there is a concern about performance that we are still aggregating all records through the entire history. As opposed to a simple read from a table.
CREATE VIEW dbo.InventorySummaryView WITH SCHEMABINDING AS SELECT Organization_uid, MasterInventory_uid, SUM(Quantity) AS Quantity, COUNT_BIG(*) AS Count FROM dbo.InventoryActivity GROUP BY Organization_uid, MasterInventory_uid GO CREATE UNIQUE CLUSTERED INDEX PK_InventorySummaryView ON dbo.InventorySummaryView ( Organization_uid, MasterInventory_uid )