Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2008 9:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:46 PM
Points: 36,944, Visits: 31,446
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #496042
Posted Wednesday, May 7, 2008 12:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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.



Post #496083
Posted Wednesday, May 7, 2008 12:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 15, 2013 10:05 PM
Points: 74, Visits: 442
Another most excellent and fantastic article Jeff.



Christopher Ford
Post #496095
Posted Wednesday, May 7, 2008 1:00 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:39 AM
Points: 646, Visits: 1,861
Couldn't be more clear...well done!
Post #496101
Posted Wednesday, May 7, 2008 1:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Nice work, Jeff. That plural table name must've been difficult for you to come to terms with :D


Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #496113
Posted Wednesday, May 7, 2008 1:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 5,967, Visits: 8,219
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
Post #496118
Posted Wednesday, May 7, 2008 2:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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
Post #496130
Posted Wednesday, May 7, 2008 2:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 15, 2013 10:05 PM
Points: 74, Visits: 442
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
Post #496131
Posted Wednesday, May 7, 2008 2:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 6, 2013 5:54 AM
Points: 526, 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.



Post #496137
Posted Wednesday, May 7, 2008 2:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 5,967, Visits: 8,219
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?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #496141
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse