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).