Can this WHILE loop be replaced with a Tally table?

Question

Post reply

Can this WHILE loop be replaced with a Tally table?

cmjj

SSC Enthusiast

Points: 103
More actions
August 11, 2013 at 6:16 pm

#278473

I'm making some changes to a report written with a while loop, and thought I would look at some performance improvements. I've been doing some reading on tally tables, and how they can in a lot of instances operate more efficiently than a WHILE loop. However, I'm having trouble getting my head around how it would work with a looping date logic.
I've written a query below using adventure works, which is very similar to the structure of the the data I'm working with (note the date logic around start and end dates on the product dimension).
Can anyone point me in the right direction on how a tally table might replace the WHILE loop?
USE [AdventureWorksDW2008R2]
DECLARE @OrderDate DATETIME, @EndDate DATETIME
SELECT @OrderDate = '20070625', @EndDate = '20070705'
DECLARE @ResultsTable TABLE (SalesOrderNumber nvarchar(20),EnglishProductName nvarchar(50),EnglishDescription nvarchar(400), DealerPrice money, ProductStandardCost money, SalesAmount money, TaxAmt money, Freight money)
WHILE @OrderDate <= @EndDate
BEGIN
INSERT INTO @ResultsTable
SELECT fis.SalesOrderNumber
, dp.EnglishProductName
, dp.EnglishDescription
, dp.DealerPrice
, ProductStandardCost
, SalesAmount
, TaxAmt
, Freight
FROM dbo.FactInternetSales fis
INNER JOIN dbo.DimDate orderdate
ON fis.OrderDateKey = orderdate.DateKey
INNER JOIN dbo.DimProduct dp
ON fis.ProductKey = dp.ProductKey
AND dp.StartDate <= orderdate.FullDateAlternateKey
AND ( dp.EndDate IS NULL OR dp.EndDate > orderdate.FullDateAlternateKey )
WHERE orderdate.FullDateAlternateKey = @OrderDate
AND dp.ProductAlternateKey = 'BK-M68B-38'
SET @OrderDate = @OrderDate + 1
END
SELECT * FROM @ResultsTable

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

I don't think you'll need a tally/numbers table for this. A single query should do it.

INSERT INTO @ResultsTable

SELECT fis.SalesOrderNumber

, dp.EnglishProductName

, dp.EnglishDescription

, dp.DealerPrice

, ProductStandardCost

, SalesAmount

, TaxAmt

, Freight

FROM dbo.FactInternetSales fis

INNER JOIN dbo.DimDate orderdate

ON fis.OrderDateKey = orderdate.DateKey

INNER JOIN dbo.DimProduct dp

ON fis.ProductKey = dp.ProductKey

AND dp.StartDate <= orderdate.FullDateAlternateKey

AND ( dp.EndDate IS NULL OR dp.EndDate > orderdate.FullDateAlternateKey )

WHERE orderdate.FullDateAlternateKey BETWEEN '20070625' AND '20070705'

AND dp.ProductAlternateKey = 'BK-M68B-38'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537