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

  • Jeff Moden

    SSC Guru

    Points: 993376

    Comments posted to this topic are about the item The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

    --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."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Shamshad Ali

    SSCarpal Tunnel

    Points: 4822

    Hello,

    My quewtion is related to replacing loop, I am very interested on this. coz i really want to improve performance and I can’t find a way to replace cursor. Here is my requirement and i want if there is any way to replace cursor with Tally Table, let me know.

    I have a table that has many fields and one is bit, there is a nightly job that checks this tables bit column if found 1 then it iterates and send other columns data to an Stored procedure, that SP checks if there is any session found between that parameter it calls another Stored procedure that is basically calling a C# (3.5) dll which recovery some information and dump in other tables.

    so here is some graphical presentation

    1- select startTime, endTime, schedule from tbl_Schedules where bit=1

    2- EXEC SP_FindUserSession @startTime, @endTime, @schedule

    3- EXEC SP_CallDLLToPostUserCredit (@SessionID, User)

    at the Top, I am using CURSOR_tbl_Schedule, and iterate then send information to Step 2, that is also Finding Session (Thousands of users session hit during that startTime and endTime) and then finally i send those sessions with the help of CURSOR_Session to SP that recovers Users Credit and Update Users marks in specific table.

    I would like to know if i can replace cursor. Right now I am in testing phase and planned to implement on production. But I really worried about those cursors, that may slow down performance.

    If you have any questions or require more details, let me know.

    I hope there is a way to solve this via Tally Table logic.

    Shamshad Ali.

  • Christopher Ford-327546

    Default port

    Points: 1482

    Another most excellent and fantastic article Jeff.

    😎

    Christopher Ford

  • David McKinney

    SSChampion

    Points: 10358

    Couldn’t be more clear…well done!

  • RyanRandall

    SSChampion

    Points: 13623

    Nice work, Jeff. That plural table name must’ve been difficult for you to come to terms with 😀

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Hugo Kornelis

    SSC Guru

    Points: 64605

    Shamshad Ali (5/7/2008)


    Hello,

    My quewtion is related to replacing loop, I am very interested on this. coz i really want to improve performance and I can't find a way to replace cursor. Here is my requirement and i want if there is any way to replace cursor with Tally Table, let me know.

    I have a table that has many fields and one is bit, there is a nightly job that checks this tables bit column if found 1 then it iterates and send other columns data to an Stored procedure, that SP checks if there is any session found between that parameter it calls another Stored procedure that is basically calling a C# (3.5) dll which recovery some information and dump in other tables.

    so here is some graphical presentation

    1- select startTime, endTime, schedule from tbl_Schedules where bit=1

    2- EXEC SP_FindUserSession @startTime, @endTime, @schedule

    3- EXEC SP_CallDLLToPostUserCredit (@SessionID, User)

    at the Top, I am using CURSOR_tbl_Schedule, and iterate then send information to Step 2, that is also Finding Session (Thousands of users session hit during that startTime and endTime) and then finally i send those sessions with the help of CURSOR_Session to SP that recovers Users Credit and Update Users marks in specific table.

    I would like to know if i can replace cursor. Right now I am in testing phase and planned to implement on production. But I really worried about those cursors, that may slow down performance.

    If you have any questions or require more details, let me know.

    I hope there is a way to solve this via Tally Table logic.

    Shamshad Ali.

    Hi Shamshad Ali,

    First: don’t gve your stored procedures names that start with “SP_”. This prefix is rexserved for system stored procedures. Using this name will lose you some performance (because SQL Server will first look for a stored proc with that name in the master database), plus you run the risk of sudden errors if a future service pack introduces a system stored procedure with the same name!

    Second: If you must use stored procedures to operate on one row from the schedules table at a time, then a cursor is pretty much your only option. However, if you want better performance, you will want to rewrite the logic in the FindUserSession and CallDLLToPostUserCredit procedures to operate on a whole set at once. Whether this is possible in your case, and whether this will indeed improve performance in your case, is something I can’t judge from here. (Hint: it mostly IS possible, and it mostly WILL improve performance – but there are exceptions).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Calvin Lawson

    SSChampion

    Points: 11030

    OMG, everybody out of the pool, quick! Read this article, now! Do not write a single piece of SQL code until you’ve finished it, and understand it.

    Top notch as usual, Jeff. If I could give you six stars I would.

    Signature is NULL

  • Christopher Ford-327546

    Default port

    Points: 1482

    Hugo Kornelis (5/7/2008)


    Shamshad Ali (5/7/2008)


    Hello,

    My quewtion is related to replacing loop, I am very interested on this. coz i really want to improve performance and I can't find a way to replace cursor. Here is my requirement and i want if there is any way to replace cursor with Tally Table, let me know.

    I have a table that has many fields and one is bit, there is a nightly job that checks this tables bit column if found 1 then it iterates and send other columns data to an Stored procedure, that SP checks if there is any session found between that parameter it calls another Stored procedure that is basically calling a C# (3.5) dll which recovery some information and dump in other tables.

    so here is some graphical presentation

    1- select startTime, endTime, schedule from tbl_Schedules where bit=1

    2- EXEC SP_FindUserSession @startTime, @endTime, @schedule

    3- EXEC SP_CallDLLToPostUserCredit (@SessionID, User)

    at the Top, I am using CURSOR_tbl_Schedule, and iterate then send information to Step 2, that is also Finding Session (Thousands of users session hit during that startTime and endTime) and then finally i send those sessions with the help of CURSOR_Session to SP that recovers Users Credit and Update Users marks in specific table.

    I would like to know if i can replace cursor. Right now I am in testing phase and planned to implement on production. But I really worried about those cursors, that may slow down performance.

    If you have any questions or require more details, let me know.

    I hope there is a way to solve this via Tally Table logic.

    Shamshad Ali.

    Hi Shamshad Ali,

    First: don't gve your stored procedures names that start with "SP_". This prefix is rexserved for system stored procedures. Using this name will lose you some performance (because SQL Server will first look for a stored proc with that name in the master database), plus you run the risk of sudden errors if a future service pack introduces a system stored procedure with the same name!

    Second: If you must use stored procedures to operate on one row from the schedules table at a time, then a cursor is pretty much your only option. However, if you want better performance, you will want to rewrite the logic in the FindUserSession and CallDLLToPostUserCredit procedures to operate on a whole set at once. Whether this is possible in your case, and whether this will indeed improve performance in your case, is something I can't judge from here. (Hint: it mostly IS possible, and it mostly WILL improve performance - but there are exceptions).

    My guess is that he’d achieve this with a CROSS APPLY and re-writing some of the logic into a function that gets the session information and joins it to the schedules that he’s described.

    Christopher Ford

  • Shamshad Ali

    SSCarpal Tunnel

    Points: 4822

    Thank you all for your valuable replies. I would like to appreciate if some one make a scenario (simplest one mapped to my problem) and provide me solution/steps to be developed one by one. I need some sort of code + hints that may improve performance by removing Cursor, if possible.

    Plz. give some time to make such scenario (very simple one) and put in SQL code that solve cursor issue.

    SP as prefix is usually used alot and included in serveral websites tutorails so I brought my scenario in mind for everyone to understand what I am trying to explain. Thanks anyway.

    I would like to appreciate help from Jeff and Christopher – Plz.

    We are using SQL Server 2000 (SP4) (Development/Testing and at production)

    Shamshad Ali.

  • Hugo Kornelis

    SSC Guru

    Points: 64605

    Shamshad Ali (5/7/2008)


    Thank you all for your valuable replies. I would like to appreciate if some one make a scenario (simplest one mapped to my problem) and provide me solution/steps to be developed one by one. I need some sort of code + hints that may improve performance by removing Cursor, if possible.

    Hi Shamshad,

    I missed the fact that your last stored procedure actuallly calls a C# DLL to do stuff outside of SQL Server. Rewriting that in a setbased fashion might prove very hard, and will probably not be worth the effort. So disregard my previous advise for that stored proc. However, you might still see performance gain if you manage to replace steps 1 and 2 to write a single setbased query to identify just the information to send to your DLL. (And that setbased query would then be the source for, indeed, a cursor, processing rows one by one and feeding them to your DLL).

    Plz. give some time to make such scenario (very simple one) and put in SQL code that solve cursor issue.

    Without knowing what the stored procedure actually does, there is very little that anyone can do, short of posting some wild guesses that might or might not happen to be useful.

    SP as prefix is usually used alot and included in serveral websites tutorails

    Just SP is fine (though I personally dislike the habit – as if anything other than a stored procedure might follow an EXEC keyword). It’s SP_ (with an underscore directly following the SP) that turns it into the special prefix reserved for system stored procedures.

    I would like to appreciate help from Jeff and Christopher - Plz.

    Not from me? :crying:


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Christopher Ford-327546

    Default port

    Points: 1482

    Hugo Kornelis (5/7/2008)


    Shamshad Ali (5/7/2008)


    Thank you all for your valuable replies. I would like to appreciate if some one make a scenario (simplest one mapped to my problem) and provide me solution/steps to be developed one by one. I need some sort of code + hints that may improve performance by removing Cursor, if possible.

    I would like to appreciate help from Jeff and Christopher - Plz.

    Not from me? :crying:

    haha, well…you do kind of sound crazy you know…have you read your blog lately? Downed one too many energy drinks I think. =)

    EDIT:

    (P.S. But it’s all extremely great info Hugo…didn’t want you to think I was insulting you)

    Maybe I should put down the energy drinks…. :w00t:

    Christopher Ford

  • Christopher Ford-327546

    Default port

    Points: 1482

    Shamshad,

    Unfortunately, your question and explanation are still too broad to provide code or examples.

    You mentioned you’re calling a C# (ver. 3.5) DLL??

    If you’ve started down that path, you might as well start using SQL Server Integration Services (SSIS) to do this nightly job that you have. This would be a much better solution than using a Tally table.

    You can use SSIS to remove your cursor logic essentially doing everything in one pass, plus it will be in managed code.

    You wouldn’t need to migrate any current databases, just need to install Integration Services and the Business Intelligence Studio that comes with SQL 2005 to author your package. You could then invoke the custom package in .Net without running SQL 2005.

    That’s if you’re bent on sticking with SQL 2000.

    But, in short, my vote is no…I have no idea how to resolve your problem without upgrading you to a better Database System. 🙂

    Christopher Ford

  • ChiragNS

    One Orange Chip

    Points: 26077

    nice article jeff.

    Here’s another one with more uses of tally table.

    http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/

    "Keep Trying"

  • Shamshad Ali

    SSCarpal Tunnel

    Points: 4822

    Well, very first i did thank to all, and ur included in that. But i saw some one is providing me a hint(looks like dreams comes true), so i put those two names coz they understand exactly wat my problem was and if someone know the problem then he post replies. So I would like if some spend time providing me that hint in practically proving the solution.

    Thanks to every one once again.

    I really apprecaite if some one take action and help me solve my problem (obviously first knowing the scenaio and making its solution)

    Shamshad Ali.

  • karthik M

    One Orange Chip

    Points: 29139

    Jeff,

    Excellent Article!

    It seems that you have written for me at the right time.:) Really, It is very useful for me.

    Because so far i have replaced more than 5 cursors and used Tally table in that places.

    Thank You Sql teacher.:)

    karthik

Viewing 15 posts - 1 through 15 (of 469 total)

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