在不损失性能的情况下保持发票金额正确?

2021-09-10 00:00:00 tsql sql-server

我有两张桌子...

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
)

发票表保存了所有引用该发票的 InvoiceItems 的计算总和.

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.

计算列?

如果我对 Invoice 表中的 TotalSumBeforeTax 和 TotalSumAfterTax 使用计算列，则通过 InvoiceItem 表中的函数获取值，它不会持久化并且会很慢，因为我在 InvoiceItem 表中有数十万行.

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.

触发?

我还考虑在 InvoiceItem 表上使用触发器，然后更新 Invioce TotalSumBeforeTax TotalAfterBeforeTax 列.

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

怎么办?

在列出 Invoice 表的多行时，有没有办法始终保持 TotalSumBeforeTax 和 TotalSumAfterTax 列最新，同时对性能的影响最小?

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?

推荐答案

我认为您根本不应该保留这些列.只需在 InvoiceItem 表上创建一个索引，该索引将具有 InvoiceID 并包含 AmountBeforeTax 和 AmountAfterTax 列，并且需要时使用简单的 sum 和 group by 查询进行计算:

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.

顺便说一句，如果涉及税收，不应该是price而不是amount吗?

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

相关文章