CODEWITHSUNDEEP
sql-servert-sql
MojoDK
MojoDK

Reputation: 4538

Keep invoice sum right without losing performance?

I have two tables...

CREATE TABLE [dbo].[Invoice](
    [InvoiceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [TotalSumBeforeTax] [decimal](12, 2) NOT NULL,
    [TotalSumAfterTax] [decimal](12, 2) NOT NULL
)

CREATE TABLE [dbo].[InvoiceItem](
    [InvoiceItemID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InvoiceID] [uniqueidentifier] NOT NULL,
    [AmountBeforeTax] [decimal](12, 2) NOT NULL,
    [AmountAfterTax] [decimal](12, 2) NOT NULL
)

The Invoice table holds a calculated sum of all the InvoiceItems that refer to that Invoice.

I need help figuring out the best practice to keep the Invoice sum correct without losing performance.

Calculated column?

If I use a calculated column for TotalSumBeforeTax and TotalSumAfterTax in the Invoice table, that fetches values via a function from the InvoiceItem table, it's not persisted and will be very slow, since I have hundreds of thousands of rows in the InvoiceItem table.

Trigger?

I also thought about using a trigger on the InvoiceItem table and then updating the Invioce TotalSumBeforeTax TotalAfterBeforeTax columns.

What to do?

Is there a way to always keeping the TotalSumBeforeTax and TotalSumAfterTax columns up-to-date with minimal impact on performance when listing many rows of the Invoice table?

Upvotes: 0

Views: 80

Answers (2)

Dale K
Dale K

Reputation: 27451

tl;dr; Start with a view then if/when necessary modify to an indexed view.

I would initially create a view on your Invoice table e.g.

create view dbo.InvoiceView
as
SELECT I.* -- Don't actually use *, spell out all your columns
  , (select SUM(AmountBeforeTax) from dbo.InvoiceItem II where II.InvoiceId = I.InvoiceId) AS TotalSumBeforeTax
  , (select SUM(AmountAfterTax) from dbo.InvoiceItem II where II.InvoiceId = I.InvoiceId) AS TotalSumAfterTax
FROM dbo.Invoice I;

with (at least) an index on dbo.InvoiceItem.InvoiceId.

I imagine this will perform pretty well for quite some time. Then when the performance drops off, turn it into an indexed view by creating a clustered index on it e.g.

CREATE UNIQUE CLUSTERED INDEX CX_InvoiceId
   ON dbo.InvoiceView (InvoiceID); -- And any other relevant columns
GO

Which has a similar effect under the hood to creating a trigger but you don't have to manage it.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

I would argue that you shouldn't keep these columns at all. Just create an index on the InvoiceItem table that will have the InvoiceID and include the AmountBeforeTax and AmountAfterTax columns, and calculate when needed using a simple sum and group by query:

SELECT InvoiceID
    , SUM(AmountBeforeTax) AS TotalSumBeforeTax
    , SUM(AmountAfterTax) AS TotalSumAfterTax
FROM dbo.InvoiceItem
GROUP BY InvoiceID

As a rule of thumb, things that can easily be computed shouldn't be stored - because it's easier to compute than to synchronize the stored values.

BTW, if there's a tax involved shouldn't it be price and not amount?

Upvotes: 2

Related Questions