4

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:

  1. Stored Procedure
  2. Separate Summary Table
  3. 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)

Here's a fiddle with some sample code and data

  1. Stored Procedure

    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
    
  2. Separate Table

    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
    
  3. Indexed View

    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
    )
    
  • 2
    When data is read from Indexed View, the query doesn't hit the underlying tables[s]. This is the whole idea of view materialization. Indexed View is pretty much the same as a regular table except you cannot issue DML against it directly (at least in SQLServer up to 2012) . – a1ex07 Dec 9 '16 at 17:18
  • 1
    Can you post some sample data (you can use pastebin or gist and link it here) ? – Kin Shah Dec 9 '16 at 17:21
  • 1
    Also, do you want running total ? What version of sql server are you using and what edition ? – Kin Shah Dec 9 '16 at 18:11
  • @Kin, using SQL Server 2012 Enterprise 64-Bit. I've added a sample set of data, queries, and output in the attached fiddle. I'm not looking for a running total within the table. I want the SUM over each group, but I want to be as performant as possible. – KyleMit Dec 9 '16 at 20:47
  • What sort of data volumes are you expecting? And how often is it queried vs updated? Is there any performance goal or target? – Sir Swears-a-lot Dec 10 '16 at 19:27
3

What performance considerations should tip the scales in favor of a particular strategy?

Essentially, as any form of cache, these strategies allow to increase performance of reads at the expense of writes and disk space.

So, you should consider the following :

  1. The expected number of writes/updates vs. number of reads.
  2. What is more important: fast writes or fast reads.
  3. Whether extra disk space is available and how "cheap" it is.
  4. Whether the summary has to be always up to date, or it can be delayed.

The first strategy is a baseline. You don't duplicate the data, writes are as fast as they can be.

The last strategy (indexed view) slows down writes immediately (as they happen), but the aggregated amounts are always up to date.

The second strategy with an explicit summary table gives you greater control over when to perform the aggregation. You can delay the aggregation and perform it when the server is under less load. If you accumulate a bunch of pending changes for calculating the summary and perform these calculations in bulk, it may be more efficient than updating the summary after every single change of the source data.

On the other hand, the engine that maintains the indexed view during updates should be smart enough to update the summary by applying only the changes to the summary without reading through the whole table each time. For example, if you update only, say, 2 rows in 10M table, then to calculate the new SUM(Quantity) the engine can subtract two old values of Quantity and add two new values. I don't have an authoritative source at hand that would confirm that the engine indeed works like this, but it should be fairly easy to test and verify by measuring the amount of reads and writes of a few test statements on a large table.

This leads to another thing to consider:

  1. The type of aggregation that is done in the indexed view and whether the engine is smart enough to keep it up-to-date efficiently. For example, it is easy to keep SUM up-to-date efficiently and not so easy for MIN.
  2. How big is the table that is being aggregated and what percentage of this table changes with each update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.