問題描述

我有兩張桌子...

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
) 

發(fā)票表保存了所有引用該發(fā)票的 InvoiceItems 的計算總和.

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

我需要幫助找出最佳做法，以在不損失性能的情況下保持發(fā)票金額正確.

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

計算列?

如果我對 Invoice 表中的 TotalSumBeforeTax 和 TotalSumAfterTax 使用計算列，則通過 InvoiceItem 表中的函數(shù)獲取值，它不會持久化并且會很慢，因為我在 InvoiceItem 表中有數(shù)十萬行.

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.

觸發(fā)?

我還考慮在 InvoiceItem 表上使用觸發(fā)器，然后更新 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?

推薦答案

我認(rèn)為您根本不應(yīng)該保留這些列.只需在 InvoiceItem 表上創(chuàng)建一個索引，該索引將具有 InvoiceID 并包含 AmountBeforeTax 和 AmountAfterTax 列，并且需要時使用簡單的 sum 和 group by 查詢進(jìn)行計算:

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

根據(jù)經(jīng)驗，不應(yīng)該存儲容易計算的東西 - 因為計算比同步存儲的值更容易.

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.

順便說一句，如果涉及稅收，不應(yīng)該是price而不是amount嗎?

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

這篇關(guān)于在不損失性能的情況下保持發(fā)票金額正確?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！