Ok, now I do have 100% proof that CTE work much slower than temp tables. This is a continuation of multiline UDF vs. SP thread. I just ran this test:
DECLARE @cCostValuation char(4),
@dtEnd DATETIME,
@iLocation INT,
@bFilterDCI BIT,
@cDepartmentFrom char(10),
@cCategoryFrom char(10),
@cItemFrom char(10),
@cDepartmentThru char(10),
@cCategoryThru char(10),
@cItemThru char(10),
@bFilterVendor BIT,
@vcVendorFrom varchar(25),
@vcVendorThru varchar(25),
@bIncludeZeroes bit
DECLARE @StartTime DATETIME
SET @StartTime = CURRENT_TIMESTAMP
SELECT @cCostValuation = 'RWAC', -- char(4)
@dtEnd = '20110519 23:59:59', -- datetime
@iLocation = null, -- int
@bFilterDCI = 1, -- bit
@cDepartmentFrom = 'RT34HANDW ', -- char(10)
@cCategoryFrom = '059OTHER ', -- char(10)
@cItemFrom = '5916 ', -- char(10)
@cDepartmentThru = 'RT34HANDW ', -- char(10)
@cCategoryThru = '059OTHER ', -- char(10)
@cItemThru = '5916 ', -- char(10)
@bFilterVendor = 0, -- bit
@vcVendorFrom = NULL, -- varchar(25)
@vcVendorThru = NULL, -- varchar(25)
@bIncludeZeroes = 1 -- bit
-- UDF Only
;with Inventory as (SELECT i.pri_key, i.ref_no,
DENSE_RANK() OVER (PARTITION BY i.department, i.category, i.item, i.invent_ID, i.locatn_ID
ORDER BY i.date_time, i.po_link) AS Fifo_Rank,
i.locatn_id, i.date_time,
i.department, i.category, i.item, i.invent_id,
i.trans_type, i.quantity, i.unit_cost
FROM i_invent i
JOIN siriusv_RetailVendorByDCI vv ON vv.department = i.department
AND vv.category = i.category
AND vv.item = i.item
JOIN i_vendor iv ON iv.vendor_id = vv.vendor_id
WHERE i.date_time <= @dtEnd
AND i.locatn_id = ISNULL(@iLocation, i.locatn_id)
AND i.department BETWEEN CASE @bFilterDCI WHEN 0 THEN i.department ELSE ISNULL(@cDepartmentFrom, i.department) END
AND CASE @bFilterDCI WHEN 0 THEN i.department ELSE COALESCE(@cDepartmentThru, @cDepartmentFrom, i.department) END
AND i.category BETWEEN CASE @bFilterDCI WHEN 0 THEN i.category ELSE ISNULL(@cCategoryFrom, i.category) END
AND CASE @bFilterDCI WHEN 0 THEN i.category ELSE COALESCE(@cCategoryThru, @cCategoryFrom, i.category) END
AND i.item BETWEEN CASE @bFilterDCI WHEN 0 THEN i.item ELSE ISNULL(@cItemFrom, i.item) END
AND CASE @bFilterDCI WHEN 0 THEN i.item ELSE COALESCE(@cItemThru, @cItemFrom, i.item) END
AND iv.full_name BETWEEN CASE @bFilterVendor WHEN 0 THEN iv.full_name ELSE ISNULL(@vcVendorFrom, iv.full_name) END
AND CASE @bFilterVendor WHEN 0 THEN iv.full_name ELSE COALESCE(@vcVendorThru, @vcVendorFrom, iv.full_name) END
),
LastCost AS (SELECT department, category, item, invent_id, LastCost
FROM siriusfn_RetailLastCost(@cDepartmentFrom, @cCategoryFrom, @cItemFrom, NULL,
@cDepartmentThru, @cCategoryThru, @cItemThru, NULL)
),
cteInventorySum
AS (
SELECT Department,
Category,
Item,
Invent_ID,
Locatn_ID,
SUM(Quantity) AS TotalInventory
FROM Inventory
GROUP BY Department,
Category,
Item,
Invent_ID,
Locatn_ID
), cteReverseInSum
AS (
/* Perform a rolling balance ( in reverse order ) through the inventory movements in */
SELECT s.Department,
s.Category,
s.Item,
s.Invent_ID,
s.Locatn_ID,
s.Fifo_Rank,
(
SELECT SUM(i.Quantity)
FROM Inventory AS i
WHERE i.Department = s.Department and
i.Category = s.Category and
i.Item = s.Item and
i.Invent_ID = s.Invent_ID and
i.Locatn_ID = s.Locatn_ID
AND i.Trans_Type IN ('P', 'A', 'T')
AND i.Fifo_Rank >= s.Fifo_Rank
) AS RollingInventory,
SUM(s.Quantity) AS ThisInventory
FROM Inventory AS s
WHERE s.Trans_Type IN ('P', 'A', 'T')
GROUP BY s.Department,
s.Category,
s.Item,
s.Invent_ID,
s.Locatn_ID,
s.Fifo_Rank
), cteWithLastTranDate
AS (
SELECT w.Department,
w.Category,
w.Item,
w.Invent_ID,
w.Locatn_ID,
w.TotalInventory,
coalesce(LastPartialInventory.Fifo_Rank,0) as Fifo_Rank,
coalesce(LastPartialInventory.InventoryToUse,0) as InventoryToUse,
coalesce(LastPartialInventory.RunningTotal,0) as RunningTotal,
w.TotalInventory - COALESCE(LastPartialInventory.RunningTotal,0) + COALESCE(LastPartialInventory.InventoryToUse,0) AS UseThisInventory
FROM cteInventorySum AS w
OUTER APPLY (
SELECT TOP(1) z.Fifo_Rank,
z.ThisInventory AS InventoryToUse,
z.RollingInventory AS RunningTotal
FROM cteReverseInSum AS z
WHERE z.Department = w.Department and
z.Category = w.Category and
z.Item = w.Item and
z.Invent_ID = w.Invent_ID and
z.Locatn_ID = w.Locatn_ID and
z.RollingInventory >= w.TotalInventory
ORDER BY z.Fifo_Rank DESC
) AS LastPartialInventory
), cteSource
AS (
SELECT y.Department,
y.Category,
y.Item,
y.Invent_ID,
y.Locatn_ID,
y.TotalInventory as QuantityOnHand,
SUM(CASE WHEN e.Fifo_Rank = y.Fifo_Rank
THEN y.UseThisInventory
ELSE e.Quantity END * Price.Unit_Cost) AS CostOfGoodsOnHand,
LastCost.LastCost
FROM cteWithLastTranDate AS y
LEFT JOIN Inventory AS e ON e.Department = y.Department
AND e.Category = y.Category
AND e.Item = y.Item
AND e.Invent_ID = y.Invent_ID
AND e.Locatn_ID = y.Locatn_ID
AND e.Fifo_Rank >= y.Fifo_Rank
AND e.Trans_Type IN ('P', 'A', 'T')
LEFT JOIN LastCost LastCost
ON y.Department = LastCost.Department
AND y.Category = LastCost.Category
AND y.Item = LastCost.Item
AND y.Invent_ID = LastCost.Invent_ID
OUTER APPLY (
/* Find the Price of the item in */
SELECT TOP(1) p.Unit_Cost
FROM Inventory AS p
WHERE p.Department = e.Department and
p.Category = e.Category and
p.Item = e.Item and
p.Invent_ID = e.Invent_ID and
p.Locatn_ID = e.Locatn_ID and
p.Fifo_Rank <= e.Fifo_Rank and
p.Trans_Type IN ('P', 'A', 'T')
ORDER BY p.Fifo_Rank DESC
) AS Price
GROUP BY y.Department,
y.Category,
y.Item,
y.Invent_ID,
y.Locatn_ID,
y.TotalInventory,
LastCost.LastCost
)
SELECT Department,
Category,
Item,
Invent_ID,
Locatn_ID,
QuantityOnHand,
coalesce(CostOfGoodsOnHand,0) as CostOfGoodsOnHand,
case when QuantityOnHand <> 0 and CostOfGoodsOnHand <> 0
then CostOfGoodsOnHand / QuantityOnHand
else LastCost end as AverageCost
from cteSource
WHERE @bIncludeZeroes = 1 or (@bIncludeZeroes = 0 and CostOfGoodsOnHand <> 0)
ORDER BY Department,
Category,
Item,
Invent_ID,
Locatn_ID
PRINT 'New UDF - FIFO only ' + CAST(DATEDIFF(ms, @StartTime, CURRENT_TIMESTAMP) AS varchar(10))
-- SP Only
--DECLARE @StartTime DATETIME
SET @StartTime = CURRENT_TIMESTAMP
IF OBJECT_ID('TempDb..#tblLastCost', 'U') IS NOT NULL
DROP TABLE #tblLastCost
CREATE TABLE #tblLastCost
(
department CHAR(10) COLLATE DATABASE_DEFAULT ,
category CHAR(10) COLLATE DATABASE_DEFAULT ,
item CHAR(10) COLLATE DATABASE_DEFAULT ,
invent_id INT ,
LastCost MONEY ,
PRIMARY KEY ( department, category, item, invent_id )
)
INSERT INTO #tblLastCost
SELECT department ,
category ,
item ,
invent_id ,
LastCost
FROM siriusfn_RetailLastCost(@cDepartmentFrom, @cCategoryFrom,
@cItemFrom, NULL, @cDepartmentThru,
@cCategoryThru, @cItemThru, NULL)
--print 'Last Cost table created'
IF OBJECT_ID('TempDb..#tbli_invent', 'U') IS NOT NULL
DROP TABLE #tbli_invent
CREATE TABLE #tbli_Invent
(
pri_key INT PRIMARY KEY NONCLUSTERED ,
ref_no NUMERIC(17, 0) ,
locatn_id INT ,
date_time DATETIME ,
fifo_rank INT ,
department CHAR(10) COLLATE DATABASE_DEFAULT ,
category CHAR(10) COLLATE DATABASE_DEFAULT ,
item CHAR(10) COLLATE DATABASE_DEFAULT ,
invent_id INT ,
trans_type CHAR(1) COLLATE DATABASE_DEFAULT ,
quantity NUMERIC(8, 2) ,
unit_cost MONEY
)
IF @bFilterVendor = 1
INSERT INTO #tbli_invent
( pri_key ,
ref_no ,
locatn_id ,
date_time ,
fifo_rank ,
department ,
category ,
item ,
invent_id ,
trans_type ,
quantity ,
unit_cost
)
SELECT i.pri_key ,
i.ref_no ,
i.locatn_id ,
i.date_time ,
DENSE_RANK() OVER ( PARTITION BY i.department,
i.category, i.item, i.invent_id,
i.locatn_id ORDER BY i.date_time, i.po_link ) ,
i.department ,
i.category ,
i.item ,
i.invent_id ,
i.trans_type ,
i.quantity ,
i.unit_cost
FROM i_invent i
JOIN siriusv_RetailVendorByDCI vv ON vv.department = i.department
AND vv.category = i.category
AND vv.item = i.item
JOIN i_vendor iv ON iv.vendor_id = vv.vendor_id
WHERE i.date_time <= @dtEnd
AND i.locatn_id = ISNULL(@iLocation, i.locatn_id)
AND i.department BETWEEN CASE @bFilterDCI
WHEN 0 THEN i.department
ELSE ISNULL(@cDepartmentFrom,
i.department)
END
AND CASE @bFilterDCI
WHEN 0 THEN i.department
ELSE COALESCE(@cDepartmentThru,
@cDepartmentFrom,
i.department)
END
AND i.category BETWEEN CASE @bFilterDCI
WHEN 0 THEN i.category
ELSE ISNULL(@cCategoryFrom,
i.category)
END
AND CASE @bFilterDCI
WHEN 0 THEN i.category
ELSE COALESCE(@cCategoryThru,
@cCategoryFrom,
i.category)
END
AND i.item BETWEEN CASE @bFilterDCI
WHEN 0 THEN i.item
ELSE ISNULL(@cItemFrom, i.item)
END
AND CASE @bFilterDCI
WHEN 0 THEN i.item
ELSE COALESCE(@cItemThru,
@cItemFrom, i.item)
END
AND iv.full_name BETWEEN CASE @bFilterVendor
WHEN 0 THEN iv.full_name
ELSE ISNULL(@vcVendorFrom,
iv.full_name)
END
AND CASE @bFilterVendor
WHEN 0 THEN iv.full_name
ELSE COALESCE(@vcVendorThru,
@vcVendorFrom,
iv.full_name)
END
ELSE
INSERT INTO #tbli_invent
( pri_key ,
ref_no ,
locatn_id ,
date_time ,
fifo_rank ,
department ,
category ,
item ,
invent_id ,
trans_type ,
quantity ,
unit_cost
)
SELECT i.pri_key ,
i.ref_no ,
i.locatn_id ,
i.date_time ,
DENSE_RANK() OVER ( PARTITION BY i.department,
i.category, i.item, i.invent_id,
i.locatn_id ORDER BY i.date_time, i.po_link ) ,
i.department ,
i.category ,
i.item ,
i.invent_id ,
i.trans_type ,
i.quantity ,
i.unit_cost
FROM i_invent i
WHERE i.date_time <= @dtEnd
AND i.locatn_id = ISNULL(@iLocation, i.locatn_id)
AND i.department BETWEEN CASE @bFilterDCI
WHEN 0 THEN i.department
ELSE ISNULL(@cDepartmentFrom,
i.department)
END
AND CASE @bFilterDCI
WHEN 0 THEN i.department
ELSE COALESCE(@cDepartmentThru,
@cDepartmentFrom,
i.department)
END
AND i.category BETWEEN CASE @bFilterDCI
WHEN 0 THEN i.category
ELSE ISNULL(@cCategoryFrom,
i.category)
END
AND CASE @bFilterDCI
WHEN 0 THEN i.category
ELSE COALESCE(@cCategoryThru,
@cCategoryFrom,
i.category)
END
AND i.item BETWEEN CASE @bFilterDCI
WHEN 0 THEN i.item
ELSE ISNULL(@cItemFrom, i.item)
END
AND CASE @bFilterDCI
WHEN 0 THEN i.item
ELSE COALESCE(@cItemThru,
@cItemFrom, i.item)
END
--print 'Created tbli_invent table'
--==============================================
;
WITH cteInventorySum
AS ( SELECT Department ,
Category ,
Item ,
Invent_ID ,
Locatn_ID ,
SUM(Quantity) AS TotalInventory
FROM #tbli_Invent
GROUP BY Department ,
Category ,
Item ,
Invent_ID ,
Locatn_ID
),
cteReverseInSum
AS (
/* Perform a rolling balance ( in reverse order ) through the inventory movements in */ SELECT
s.Department ,
s.Category ,
s.Item ,
s.Invent_ID ,
s.Locatn_ID ,
s.Fifo_Rank ,
( SELECT
SUM(i.Quantity)
FROM
#tbli_Invent AS i
WHERE
i.Department = s.Department
AND i.Category = s.Category
AND i.Item = s.Item
AND i.Invent_ID = s.Invent_ID
AND i.Locatn_ID = s.Locatn_ID
AND i.Trans_Type IN (
'P', 'A', 'T' )
AND i.Fifo_Rank >= s.Fifo_Rank
) AS RollingInventory ,
SUM(s.Quantity) AS ThisInventory
FROM
#tbli_Invent AS s
WHERE
s.Trans_Type IN (
'P', 'A', 'T' )
GROUP BY s.Department ,
s.Category ,
s.Item ,
s.Invent_ID ,
s.Locatn_ID ,
s.Fifo_Rank
),
cteWithLastTranDate
AS ( SELECT w.Department ,
w.Category ,
w.Item ,
w.Invent_ID ,
w.Locatn_ID ,
w.TotalInventory ,
COALESCE(LastPartialInventory.Fifo_Rank, 0) AS Fifo_Rank ,
COALESCE(LastPartialInventory.InventoryToUse,
0) AS InventoryToUse ,
COALESCE(LastPartialInventory.RunningTotal,
0) AS RunningTotal ,
w.TotalInventory
- COALESCE(LastPartialInventory.RunningTotal,
0)
+ COALESCE(LastPartialInventory.InventoryToUse,
0) AS UseThisInventory
FROM cteInventorySum AS w
OUTER APPLY ( SELECT TOP ( 1 )
z.Fifo_Rank ,
z.ThisInventory AS InventoryToUse ,
z.RollingInventory AS RunningTotal
FROM cteReverseInSum AS z
WHERE z.Department = w.Department
AND z.Category = w.Category
AND z.Item = w.Item
AND z.Invent_ID = w.Invent_ID
AND z.Locatn_ID = w.Locatn_ID
AND z.RollingInventory >= w.TotalInventory
ORDER BY z.Fifo_Rank DESC
) AS LastPartialInventory
),
cteSource
AS ( SELECT y.Department ,
y.Category ,
y.Item ,
y.Invent_ID ,
y.Locatn_ID ,
y.TotalInventory AS QuantityOnHand ,
SUM(CASE WHEN e.Fifo_Rank = y.Fifo_Rank
THEN y.UseThisInventory
ELSE e.Quantity
END * Price.Unit_Cost) AS CostOfGoodsOnHand ,
LastCost.LastCost
FROM cteWithLastTranDate AS y
LEFT JOIN #tbli_Invent AS e ON e.Department = y.Department
AND e.Category = y.Category
AND e.Item = y.Item
AND e.Invent_ID = y.Invent_ID
AND e.Locatn_ID = y.Locatn_ID
AND e.Fifo_Rank >= y.Fifo_Rank
AND e.Trans_Type IN (
'P', 'A', 'T' )
LEFT JOIN #tblLastCost LastCost ON y.department = LastCost.department
AND y.category = LastCost.category
AND y.item = LastCost.item
AND y.invent_id = LastCost.invent_id
OUTER APPLY (
/* Find the Price of the item in */ SELECT TOP ( 1 )
p.Unit_Cost
FROM
#tbli_Invent AS p
WHERE
p.Department = e.Department
AND p.Category = e.Category
AND p.Item = e.Item
AND p.Invent_ID = e.Invent_ID
AND p.Locatn_ID = e.Locatn_ID
AND p.Fifo_Rank <= e.Fifo_Rank
AND p.Trans_Type IN (
'P', 'A', 'T' )
ORDER BY p.Fifo_Rank DESC
) AS Price
GROUP BY y.Department ,
y.Category ,
y.Item ,
y.Invent_ID ,
y.Locatn_ID ,
y.TotalInventory ,
LastCost.LastCost
)
SELECT Department ,
Category ,
Item ,
Invent_ID ,
Locatn_ID ,
QuantityOnHand ,
COALESCE(CostOfGoodsOnHand, 0) AS CostOfGoodsOnHand ,
CASE WHEN QuantityOnHand <> 0
AND CostOfGoodsOnHand <> 0
THEN CostOfGoodsOnHand / QuantityOnHand
ELSE LastCost
END AS AverageCost
FROM cteSource
WHERE @bIncludeZeroes = 1
OR ( @bIncludeZeroes = 0
AND CostOfGoodsOnHand <> 0
)
ORDER BY Department, category, item, locatn_id
PRINT 'SP - FIFO only ' + CAST(DATEDIFF(ms, @StartTime, CURRENT_TIMESTAMP) AS varchar(10))
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Thanks in advance.
