The "Numbers" or "Tally" Table: What it is and how it replaces a loop
-
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.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) -
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.
-
Another most excellent and fantastic article Jeff.
😎
Christopher Ford
-
Couldn't be more clear...well done!
-
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.
-
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/ -
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
-
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
-
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.
-
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/ -
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
-
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
-
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"
-
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.
-
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 511 total)
You must be logged in to reply to this topic. Login to reply