重寫查詢速度

此查詢有效，但速度極慢，並且需要大量系統資源才能完成。有沒有不同的方法來寫這個表，所以速度是第一優先？

下面是查詢和範例 DDL

CREATE TABLE [dbo].[Costing](
   [Store] [varchar](45) NULL,
   [TotalCost] [float] NULL,
   [SaleID] [varchar](12) NULL,
   [SaleDate] [date] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[HOLD1](
   [Store] [varchar](45) NULL,
   [SaleID] [varchar](12) NULL,
   [TimesBilled] [int] NULL
) ON [PRIMARY]

Create Table MasterData
(
   [Store] [varchar](45) NOT NULL,
   TC1 float NOT NULL DEFAULT(0),
   TC2 float NOT NULL DEFAULT(0)
)
SET ANSI_PADDING OFF

INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 1', 5800, N'1234', CAST(0xA13C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 1', 50000, N'1123', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 2', 2925, N'3311', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 3', 4000, N'4411', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 3', 100000, N'2211', CAST(0xA23C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 4', 1200, N'1211', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 4', 500, N'5511', CAST(0x943C0B00 AS Date))
INSERT [dbo].[Costing] ([Store], [TotalCost], [SaleID], [SaleDate]) VALUES (N'Store 5', 3500, N'1314', CAST(0x9F3C0B00 AS Date))
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 3', N'4411', 4)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 4', N'1211', 2)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 5', N'1314', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 2', N'3311', 2)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 4', N'5511', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 1', N'1234', 1)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 3', N'2211', 5)
INSERT [dbo].[HOLD1] ([Store], [SaleID], [TimesBilled]) VALUES (N'Store 1', N'1133', 3)
Insert Into MasterData (Store) VALUES
('Store 1'), ('Store 2'), ('Store 3'), ('Store 4'), ('Store 5')

這是腳本：

SELECT [Store]
   ,[SaleID]
   ,TPC = SUM(COALESCE([TotalCost],0))      
INTO HelperTable
FROM Costing
GROUP BY Store, SaleID

Alter Table HelperTable
ADD timesbilled int NOT NULL DEFAULT(0)

UPDATE x
SET TimesBilled = y.TimesBilled
FROM HelperTable x
INNER JOIN [Hold1] y
ON x.SaleID = y.SaleID

Alter Table HelperTable
ADD TC1 As case
               when TimesBilled > 1 then TPC/TimesBilled
               else TPC
           end

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN HelperTable y
ON x.Store = y.Store


Select * FROM MasterData

即使通過 創建表SELECT ... INTO，您也可以包含稍後填充的空列。ALTER這甚至避免了一次對錶的需要：

Select * FROM MasterData
SELECT [Store]
   ,[SaleID]
   ,TPC = SUM(COALESCE([TotalCost],0))
   ,timesbilled = CAST(NULL as int)
   ,TC1 = SUM(COALESCE([TotalCost],0))
INTO HelperTable
FROM Costing
GROUP BY Store, SaleID

UPDATE t
SET t.TimesBilled = h.TimesBilled
  ,t.TC1 = CASE WHEN h.TimesBilled > 1
             THEN t.TC1/h.TimesBilled
             ELSE t.TC1
           END
FROM HelperTable t
INNER JOIN [Hold1] h
ON t.SaleID = h.SaleID

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN HelperTable y
ON x.Store = y.Store

如果HOLD1保證每個 0 或 1 條記錄SaleID，您可以進一步壓縮它，避免需要UPDATE- 此時，您實際上可以跳過HelperTable，並將子查詢直接放入MasterDate UPDATE語句中：

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN (SELECT c.[Store]
                 ,c.[SaleID]
                 ,TPC = SUM(COALESCE(c.[TotalCost],0))
                 ,TimesBilled = h.TimesBilled
                 ,TC1 = CASE WHEN h.TimesBilled > 1
                          THEN SUM(COALESCE(c.[TotalCost],0))/h.TimesBilled
                          ELSE SUM(COALESCE(c.[TotalCost],0))
                        END
             FROM Costing c
                    LEFT  JOIN [Hold1] h ON (c.SaleID = h.SaleID)
            GROUP BY c.Store, c.SaleID, h.TimesBilled
          ) y ON x.Store = y.Store

在我的系統上，執行原始查詢需要 13 毫秒（我執行了兩次，兩次相同）；我的版本HelperTable第一次執行時用了 10 毫秒，第二次用了 6 毫秒；沒有的版本HelperTable第一次用了 3 毫秒，第二次用了 0 毫秒。（注意第一次和第二次執行，因為第二次執行可能能夠使用記憶體計劃）。

引用自：https://dba.stackexchange.com/questions/172812