The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Question

The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Viewing 15 posts - 136 through 150 (of 511 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

srienstr (5/13/2008)
Danielle:
Don't bother with the loop at all, substitute in a join.
----Segment Level Supply
INSERT INTO @ConversionTesting(Quarter, Source, Segment, Metric,Value)
select Q.Quarter, 'Converted' as Source, MarketsegmenttypeCode, 'Inventory', sum(NumUnits)
from dbo.tbl_BldgSegments bs
inner join lt_marketsegmenttype mst
on bs.marketsegmenttypeid = mst.marketsegmenttypeid
inner join tbl_Building b
on bs.buildingid = b.buildingid
inner join tbl_Property p
on b.propertyid = p.propertyid
cross join @DistinctQuarters Q
where legacymsacode = CASE @MSA_Code WHEN 0 THEN legacymsacode ELSE @MSA_Code END and
(bs.SurveyDate = q.Quarter
or (bs.SurveyDate < q.Quarter and (bs.TerminateDate is null OR BS.TerminateDate > q.Quarter)))
group by MarketsegmenttypeCode

Why do you need a Cross-Join on the @DistinctQuarters table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

srienstr SSCrazy Points: 2435 More actions · Answer 2

Jeff Moden (5/13/2008)
Why do you need a Cross-Join on the @DistinctQuarters table?

From my read of the code, it appeared to be storing (or intended to store) multiple time frames, with each one producing a row in the output table. Using the cross join seemed to simplest way to maintain the same output.

Now that I think more on it, would it run more efficiently using a non-equijoin, or will the optimizer handle that from the where criteria?

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

srienstr SSCrazy Points: 2435 More actions · Answer 3

dporter (5/13/2008)
Thank you so much! This took about 10 seconds off of my query. Thanks! 🙂

Out of curiosity, what is the current time and what was the previous time for this section of code? Taking 10 out of 13 is much more satisfying than 10 out of 120.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

srienstr (5/13/2008)
Jeff Moden (5/13/2008)
Why do you need a Cross-Join on the @DistinctQuarters table?
From my read of the code, it appeared to be storing (or intended to store) multiple time frames, with each one producing a row in the output table. Using the cross join seemed to simplest way to maintain the same output.
Now that I think more on it, would it run more efficiently using a non-equijoin, or will the optimizer handle that from the where criteria?

I believe you'll find that the criteria you included will turn the cross-join into a simple inner join... why not just call it that way?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

msorens SSC Veteran Points: 288 More actions · Answer 5

Excellent article--clear and concise.

(1) I recently wrote an arn article that might interest you and readers of your article, entitled "Bring Array Mapping Capabilities to SQL" available at http://www.devx.com/dbzone/Article/35790

(2) Your solution to the "split string on commas" using the tally table is sub-optimal, as I am sure you are aware. Perhaps you were trying to mirror the looping solution, but it might also pay to show the cleaner tally solution (you incorporate this into your next example but it might be helpful to others to see it standalone):

DECLARE @Parameter VARCHAR(8000)

SET @Parameter = ',Element01,Element02,Element03,Element04,Element05,'

SELECT N, SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)

FROM dbo.Tally

WHERE N < LEN(@Parameter)

AND SUBSTRING(@Parameter,N,1) = ','

ORDER BY N

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

Looks interesting... can't download the code, though, because I'm not a member and they want too much info for my liking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Crandell SSCommitted Points: 1543 More actions · Answer 7

Jeff Moden (5/15/2008)
Looks interesting... can't download the code, though, because I'm not a member and they want too much info for my liking.

Jeff, I was able to download the code and I am not a member. I have attached it for you.

Ian.

"If you are going through hell, keep going."
-- Winston Churchill

msorens SSC Veteran Points: 288 More actions · Answer 8

msorens

SSC Veteran

Points: 288

May 16, 2008 at 2:10 pm

#816727

Thanks, Ian, for sending the code bundle along.

Ian Crandell SSCommitted Points: 1543 More actions · Answer 9

No, problem. I don't know why Jeff wasn't able to get it, unless they could sense who he is and realized he is so good that he doesn't need the code! :w00t:

Ian.

"If you are going through hell, keep going."
-- Winston Churchill

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

Ian Crandell (5/16/2008)
Jeff Moden (5/15/2008)
Looks interesting... can't download the code, though, because I'm not a member and they want too much info for my liking.
Jeff, I was able to download the code and I am not a member. I have attached it for you.

Must be because I won't take cookies, either... :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

Ian Crandell (5/16/2008)
No, problem. I don't know why Jeff wasn't able to get it, unless they could sense who he is and realized he is so good that he doesn't need the code! :w00t:

Heh... Must've been my computer that did it... it's kind'a allergic to While loops. Thanks for downloading and posting the code, Ian. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 12

Michael,

It'll take me a bit to digest the "Map", but very nice on the documentation! Nice to see something so well documented!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

msorens SSC Veteran Points: 288 More actions · Answer 13

Just an FYI: I realized the error of my ways in nomenclature: my next release (due out the end of May) will rename SP_map to just Map. Should not have used the prefix.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 14

All right Jeff, I was having fun with your tally table example and trying to think set-based on how to clean up a column that included non-alpha characters. I figured it out, but now my pea-brain is stumped as to how the reconstruction of the string is occurring. How does SQL know to piece this back together in its entirety in one SELECT, without having to go through a WHILE loop OR join to a Tally Table? (I left in what I thought I was going to have to do, commented out, which I figured out I didn't need, but am not sure why)

Thanks!

--=============================================================================

-- Create and populate a Tally table --By Jeff Moden, 2008/05/07 http://www.sqlservercentral.com

--=============================================================================

--===== Conditionally drop and create the table/Primary Key

IF OBJECT_ID('Tempdb..#Tally') IS NOT NULL BEGIN DROP TABLE #Tally END

CREATE TABLE #Tally (N INT)

--===== Create and preset a loop counter

DECLARE @Counter INT, @upperLimit INT

SET @Counter = 1

SET @upperLimit = 11000 -- 5-19-08 Jon Crawford - change parameter here for upperLimit, rather than hard-coding

--===== Populate the table using the loop and counter

WHILE @Counter <= @upperLimit

BEGIN

INSERT INTO #Tally (N) VALUES (@Counter)

SET @Counter = @Counter + 1

END

--===========================================END TALLY TABLE SETUP========================================

IF object_id('Tempdb..#MyHead') IS NOT NULL BEGIN DROP TABLE #MyHead END

CREATE TABLE #MyHead

(PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

theValue VARCHAR(500))

INSERT INTO #MyHead

SELECT 'The 10/quick 1/brown 2008fox' UNION ALL

SELECT ' jumped over 9' UNION ALL

SELECT 'the/ lazy ' UNION ALL

SELECT 'd0og'

SELECT * from #MyHead

IF object_id('Tempdb..#holdMe') IS NOT NULL BEGIN DROP TABLE #holdMe END

SELECT N,

mh.PK row,

substring(mh.theValue,N,1) value

INTO #holdMe

FROM #MyHead mh

CROSS JOIN #Tally

WHERE N < LEN(mh.theValue)+2

AND substring(mh.theValue,N,1) NOT LIKE '[0-9/]'

ORDER BY mh.PK,N

SELECT * FROM #holdMe

DECLARE @rebuildMe varchar(2000), @iteration int

SET @rebuildMe = ''

--SET @iteration = 1

--WHILE len(@rebuildMe)<(SELECT Max(N) FROM #holdMe)

--BEGIN

SELECT @rebuildMe = @rebuildMe + #holdMe.value

FROM #holdMe --CROSS JOIN #Tally

--WHERE #Tally.N<2--(SELECT max(N) FROM #holdMe)+1

--ORDER BY #Tally.N, #holdMe.row

--SET @iteration = @iteration + 1

--END

SELECT @rebuildMe

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

David Jackson SSCertifiable Points: 6414 More actions · Answer 15

Using your test data, what would you expect this to return?

DECLARE @rebuildMe varchar(2000), @iteration int

SET @rebuildMe = ''

SELECT @rebuildMe = @rebuildMe + cast(n as varchar(10)) + ' '

FROM #tally

SELECT @rebuildMe

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."