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())
代碼: 選擇全部
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