Re-Write Query For Speed This query works, but it is extremely slow, and takes a lot of system resources to complete.  Is there a different way to write this table so that speed is #1 priority?

Below is the query and sample 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')


This is the script:

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 &gt; 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
Even when creating a table via SELECT ... INTO, you can include empty columns to be populated later. This avoids the need to ALTER the table even once:

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 &gt; 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


If HOLD1 is guaranteed to have 0 or 1 records for each SaleID, you can compress this even further, avoiding the need for the UPDATE - at which point, you can actually skip HelperTable, and put a subquery directly into the MasterDate UPDATE statement:

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 &gt; 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


On my system, the original query took 13 ms to execute (I ran it twice, same both times); My version with HelperTable took 10 ms the first time I ran it, and 6 the second; and the version without HelperTable took 3 ms the first time, and 0 ms the second. (Noting first and second runs as the second run may be able to use a cached plan).