[MSSQL]更快的主從資料表異動

[tim]

在主從資料結構中, 在主資料表中有時會有一個 redundant 欄位, 來記錄 detail 資料的總合, 例如訂單的小計, 或是物品的總重, 有時會需要進行該欄位的重算, 原則上使用一個普通的 command 即可完成, 例如在資料表 mmaster 為 master, mdetail 為 detail, 產生指令如後, 若要異動 mmaster 的 totalweight , 會要計算所有的 mdetail 的 weight 加總即可, 一般正常的下法為:

update mmaster set totalweight = (select isnull(sum(weight),0) from mdetail where code = mmaster.code)

但是這樣一來, 若是無 detail 項的 master 資料也是要再取出 update, 如此效能比較不理想, 修正為僅對有 detail 項的 master 才做 update, 如此效能會更好, 調整後的指令如下:

update mmaster set totalweight = d.totalweight from (select code, sum(weight) as totalweight from mdetail group by code) d inner join mmaster m on d.code=m.code

比較複雜的是 update 的 from 子句, 不過仔細觀察, 先得組合出關連表後再異動, 那兩者的效能到底誰高進低呢?

利用

代碼: 選擇全部

declare @i int
declare @start datetime
set @start=getdate()
set @i=1
set nocount on
while(@i < 100)
begin
 -----command
 set @i=@i+1
end
print datediff(millisecond, @start, getdate())

來執行比較, 發現在資料量 detail 和 master 落差大的時候, 效能明顯後者勝出, 但若是 detail 和 master 相當平均時, 後者只快一點點, 但原則上還是有較快. 給大家做參考.

代碼: 選擇全部

CREATE TABLE [dbo].[mdetail] (
[code] [int] NOT NULL ,
[seq_no] [int] NOT NULL ,
[weight] [int] NOT NULL ,
[weight2] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mmaster] (
[code] [int] NOT NULL ,
[totalweight] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[mdetail] WITH NOCHECK ADD
CONSTRAINT [PK_mdetail] PRIMARY KEY  CLUSTERED
(
[code],
[seq_no]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[mmaster] WITH NOCHECK ADD
CONSTRAINT [PK_mmaster] PRIMARY KEY  CLUSTERED
(
[code]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[mdetail] ADD
CONSTRAINT [FK_mdetail_mmaster] FOREIGN KEY
(
[code]
) REFERENCES [dbo].[mmaster] (
[code]
)
GO