SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (427K reputation)SSC Guru (427K reputation)SSC Guru (427K reputation)SSC Guru (427K reputation)SSC Guru (427K reputation)SSC Guru (427K reputation)SSC Guru (427K reputation)SSC Guru (427K reputation)

Group: General Forum Members
Points: 427260 Visits: 43428
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Shamshad Ali
Shamshad Ali
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2430 Visits: 590
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
Christopher Ford-327546
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 454
Another most excellent and fantastic article Jeff.

Cool

Christopher Ford

David McKinney
David McKinney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4985 Visits: 2094
Couldn't be more clear...well done!
RyanRandall
RyanRandall
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7199 Visits: 4652
Nice work, Jeff. That plural table name must've been difficult for you to come to terms with BigGrin

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29795 Visits: 12834
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Calvin Lawson
Calvin Lawson
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3914 Visits: 102
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
Christopher Ford-327546
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 454
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
Shamshad Ali
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2430 Visits: 590
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
Hugo Kornelis
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29795 Visits: 12834
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search