| Author |
Topic |
|
neo302
Starting Member
30 Posts |
 Posted - 2006-05-03 : 18:02:14
|
| Hey, What is a good way to avoid using a cursor that calls other stored procedures using the values passed in by the cursor?Each day I have ~40,000 records I cursor through. I pull the values from these records using the cursor. These pulled values are not modified. They are used to call other stored procedures, which call other stored procedures that do a bunch of calculations. Thanks in advance! Any help would be greatly appreciated! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 18:17:51
|
| It is impossible to provide a solution without seeing your current code.Tara Kizeraka tduggan |
 |
|
|
neo302
Starting Member
30 Posts |
Posted - 2006-05-03 : 18:25:26
|
| Hey, here is a simplified version with some error checking removed.The sp_Recalc_More Calls other sps, that call other sps, butI don't think it's relevant with me trying to get rid of the cursor. I may be wrong though. Thank you.DECLARE Recalc CURSOR FOR SELECT a, b, c, d, e, f, g, h, i, j FROM db.dbo.Tab1 (NOLOCK) ORDER BY a, b, c OPEN Recalc FETCH NEXT FROM Recalc INTO @A, @B, @C, @D, @E, @F, @g @h, @i, @j WHILE (@@FETCH_STATUS <> -1) BEGIN EXECUTE @ReturnCD = sp_Recalc_More 0, 0, @A, @B, @C, @D, @E, @F, @g @h, @i, @j If @ReturnCD != 0 Begin -- error rollback code. End Update DB.dbo.Tab1 Set UpdateProcess = 'DONERECALC', UpdateTS = getdate() WHERE a = @A AND b = @b FETCH NEXT FROM Recalc INTO @A, @B, @C, @D, @E, @F, @g @h, @i, @j END DEALLOCATE Recalc |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 18:37:28
|
| We need to see the code from sp_Recalc_More. Do you know that you aren't supposed to prefix your stored procedures with sp_ as it means SQL Server checks the master database first for the object then the current database? So you are receiving a slight performance hit each time.Tara Kizeraka tduggan |
|
|
|
neo302
Starting Member
30 Posts |
Posted - 2006-05-03 : 18:41:34
|
| Hey Tara, Thanks for your help so far.The code is huge. It is doing quite a bit of stuff. This is the only cursor existingin all of it. Is there anything I should do to avoid this initial cursor? The proceeding sps do affect values in the TAB1 table, but not the values passed inby the cursor. Yea, I'm aware of the SP naming thing. It's on the list to do as I didn't code the db initially. Thank you again! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 18:43:51
|
| There is no way to avoid looping if you are calling a stored procedure that can't handle sets of rows.Tara Kizeraka tduggan |
|
|
|
neo302
Starting Member
30 Posts |
Posted - 2006-05-03 : 18:46:20
|
quote:
Originally posted by tkizer
There is no way to avoid looping if you are calling a stored procedure that can't handle sets of rows.Tara Kizeraka tduggan
OK Thanks. Would it be more efficient if I used a temp table to store all the cursor values, then a while loop until I went through all the records or is that pointless?Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 18:48:54
|
| It's hard to give you an answer without seeing code. You'll have to test it out to see what's more efficient.Tara Kizeraka tduggan |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-05-03 : 20:45:15
|
| neo302 -- you are missing the point .... your stored procedure needs to directly access and join to "Tab1" (in your example) and return a set of values (not just a single return value in a variable as you are doing) in order to efficiently process your data.Storing things in a temp table and looping through that versus using a cursor will make no difference. It's not the cursor itself that is the bottleneck, it is the fact that your current algorithm/code is written to process one single value at a time and return 1 value at a time. |
|
|
|
neo302
Starting Member
30 Posts |
Posted - 2006-05-03 : 21:58:52
|
quote:
Originally posted by jsmith8858
neo302 -- you are missing the point .... your stored procedure needs to directly access and join to "Tab1" (in your example) and return a set of values (not just a single return value in a variable as you are doing) in order to efficiently process your data.Storing things in a temp table and looping through that versus using a cursor will make no difference. It's not the cursor itself that is the bottleneck, it is the fact that your current algorithm/code is written to process one single value at a time and return 1 value at a time.
Hey JSmith, Sorry, I could have made it more clear in my code. I modded my code above. The return value is for error checking only. The sp does a lot more sql and calls other sps. No values are returned to be computed with. When the other sps are called, they calculate values to update other values in the Tab1 table, but none of the modded values are ones that are passed in the cursor. |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-05-04 : 03:23:52
|
| There are two options:1. Rewrite the whole process not to use cursor.2. Optimize the procedure executed inside the loop.For both of them the code of the procedure has to be available to provide you any help. |
|
|
|
kvrsaravanan
Starting Member
1 Post |
Posted - 2008-06-18 : 08:44:25
|
| I suggest to loop the records through temp table by using while loop. I am also searching a way to do this. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
Avoiding cursors...