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

  • Jeff Moden

    SSC Guru

    Points: 995687

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 995687

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 64655

    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: 995687

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    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

    *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: 2366

    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: 995687

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    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: 995687

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    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

    END

  • PaladinTech

    Mr or Mrs. 500

    Points: 577

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

    Hawkeye67

  • srienstr

    SSCrazy

    Points: 2366

    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

    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: 995687

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

Viewing 15 posts - 121 through 135 (of 498 total)

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