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 - 121 through 135 (of 511 total)

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

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

ALZDBA (5/10/2008)
Jeff Moden (5/9/2008)
Mine's a nice little ol' man's bike... Honda VTX 1300 S (retro with spoked wheels) and something I love dearly... shaft drive. Took me a long time to riding in a "chair-like" position and the foot-boards are so low to the ground that even on what I think are semi-gentle turns, I sometime scrape them on the ground... scares the heck out of me everytime.
So that'll be about to only RBAR you like:
Ride Bike And Revive :w00t:

'Zactly... you know the feeling.

--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: 1003861 More actions · Answer 2

creeds (5/9/2008)

Thanks for the feedback and the code you posted. What version of SQL Server are you using? I might be able to turn a treat for you...[/quote]

Jeff,

We're running the whole gamit on versions. We've got a bunch of servers and they're running everything from 6.5 to 2008. Any words of wisdom or assistance in any way would be greatly appreciated.

Thanks,

CReeds[/quote]

Well, not sure what to say, now. I guess my recommendation would be that, since you're using 6.5 and 7, which have no chance of using the UDF you posted, I think I'd make a Tally 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)

David Burrows SSC Guru Points: 65138 More actions · Answer 3

cry out “Tally Ho”!

Only when he gets on his high horse 😉

btw Jeff, excellent article, as always 😀

Only the Best eh :hehe:

Far away is close at hand in the images of elsewhere.
Anon.

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

Thanks, David... real good to see that you're still around, too! 🙂

--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)

dporter-623719 Newbie Points: 3 More actions · Answer 5

OK, I clearly don't understand how to use the formatting since it doesn't seem to be working for me, so I've removed it. Pretty sad for a programmer. In any case, when I posted, part of my sql code disappeared too -- I had greater than and less than symbols. I'll have to type it out....please forgive me!! 🙁 And can someone tell me what I did wrong posting? This is my first time...

Hello,

Thank you for the article! I’m trying to implement it in a stored procedure that I have a loop in, but I’m having some problems with the concept…

I have a temp table with the distinct quarter end dates that I loop through. (They aren’t always “real” quarter end dates – it could be “12/30/2007” instead of 12/31/2007”.)

Temp table definition:

Declare @DistinctQuarters TABLE

(

ID int IDENTITY,

Quarter datetime

)

I then have several queries like the one below that are generating different metrics that I insert into one table to bring back to the gui.

I need to replace the text that relates to @QuarterEndDate somehow with your idea if possible, but I’m just not sure how to go about it.

INSERT INTO @ConversionTesting(Quarter, Source, Segment, Metric,Value)

SELECT @QuarterEndDate, '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

WHERE p.legacymsacode = CASE @MSA_Code WHEN 0 THEN p.legacymsacode ELSE @MSA_Code END and

(bs.SurveyDate = @QuarterEndDate

or (bs.SurveyDate LT @QuarterEndDate and (bs.TerminateDate is null OR BS.TerminateDate GT @QuarterEndDate)))

GROUP BY MarketsegmenttypeCode

Thoughts?

Thanks in advance!

Danielle

jburkman Right there with Babe Points: 768 More actions · Answer 6

*sigh*

Ok, so I guess I'm the only one who doesn't get this part, but:

in

SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

what is the purpose of the cross-join? It doesn't run any faster or slower when referencing the SysColumns table once.

srienstr SSCrazy Points: 2435 More actions · Answer 7

jburkman (5/13/2008)
*sigh*
Ok, so I guess I'm the only one who doesn't get this part, but:
in
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
what is the purpose of the cross-join? It doesn't run any faster or slower when referencing the SysColumns table once.

It produces up to about 24 million rows, where a single reference would only produce about 4900 rows.

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

Absolutely correct about the cross-join... change the 11,000 to 1,000,000 and see how fast it works.

--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)

jburkman Right there with Babe Points: 768 More actions · Answer 9

Ah, I see my confusion, thank you. Without the cross-join there will be 11,260 rows populated. So in the 11,000 example it really doesn't make a difference.

Thanks 🙂

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

dporter (5/13/2008)
I need to replace the text that relates to @QuarterEndDate somehow with your idea if possible, but I’m just not sure how to go about it.

Danielle...

Hard to tell because you didn't include the "loop" part... could you include that as well, please?

--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)

dporter-623719 Newbie Points: 3 More actions · Answer 11

Declare @ConversionTesting TABLE

(

[Quarter] [smalldatetime] NOT NULL,

Source varchar(25),

[MSA] [nvarchar](255),

MSA_Code int,

[Segment] [nvarchar](255),

[Metric] [nvarchar](255),

[Value] [float] NULL

)

Declare @DistinctQuarters TABLE

(

ID int IDENTITY,

Quarter datetime

)

INSERT INTO @DistinctQuarters(Quarter)

SELECT distinct QuarterEndDate

from tbl_Conversion

order by QuarterEndDate

DECLARE @QuarterEndDate Datetime

DECLARE @iNextRowID int, @iCurrentRowID int, @iLoopControl int

SELECT @iLoopControl = 1

SELECT @iNextRowID = MIN(ID) FROM @DistinctQuarters

SELECT @QuarterEndDate = Quarter

FROM @DistinctQuarters

WHERE ID = @iNextRowID

WHILE @iLoopControl = 1

BEGIN

SELECT @iCurrentRowID = @iNextRowID

----Segment Level Supply

INSERT INTO @ConversionTesting(Quarter, Source, Segment, Metric,Value)

select @QuarterEndDate, '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

where legacymsacode = CASE @MSA_Code WHEN 0 THEN legacymsacode ELSE @MSA_Code END and

(bs.SurveyDate = @QuarterEndDate

or (bs.SurveyDate < @QuarterEndDate and (bs.TerminateDate is null OR BS.TerminateDate > @QuarterEndDate)))

group by MarketsegmenttypeCode

--Other metrics are defined here, but not necessary for concept

SELECT @iNextRowID = null

SELECT @iNextRowID = MIN(ID) FROM @DistinctQuarters WHERE ID > @iCurrentRowID

IF ISNULL(@iNextRowID, 0) = 0

BEGIN

SET @iLoopControl = 0

select Quarter, Source, MSA, MSA_Code, Segment, Metric,Value

from @ConversionTesting

where segment not in ('stnc','ial')

END

ELSE

BEGIN

SELECT @QuarterEndDate = Quarter

FROM @DistinctQuarters

WHERE ID = @iNextRowID

END

PaladinTech Mr or Mrs. 500 Points: 577 More actions · Answer 12

A great and most helpful article. Keep up the good work.

Hawkeye67

srienstr SSCrazy Points: 2435 More actions · Answer 13

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

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

dporter-623719 Newbie Points: 3 More actions · Answer 14

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

Thank you so much! This took about 10 seconds off of my query. Thanks! 🙂

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 15

Hawkeye67 (5/13/2008)
A great and most helpful article. Keep up the good work.

Thanks, Hawkeye. I appreciate the feedback.

--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)