| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2010-08-24 : 05:09:09
|
| Hi,There is a stored procedure (SP) which uses read only cursor.Is it true that if this SP uses while loop instead of the cursor then it makes the SP faster?Thanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-08-24 : 05:11:52
|
quote:
Originally posted by arkiboys
Hi,There is a stored procedure (SP) which uses read only cursor.Is it true that if this SP uses while loop instead of the cursor then it makes the SP faster?Thanks
Not neccessarily. It depends on what type of operations done by the procedure. Can you post the code?MadhivananFailing to plan is Planning to fail |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-08-24 : 05:16:01
|
| The existing SP does something simiilar to the following:1- Build the cursor from a select with several joins to four other tables.2-in each loop within the cursor, there are several if statements to check values of tables and if there is a match then an update happens |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-24 : 05:18:32
|
It sounds like you can do the whole thing setbased.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-08-24 : 05:25:48
|
| I see.Thank you |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-08-24 : 05:31:50
|
| Could this be one reason to use the while loop instead?to prevent transaction log blowing up |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-24 : 05:52:49
|
It depends. On a number of things. To keep consistency you would need a transaction around every loop anyway.How many records are updated total?Which are the datatypes being updated? quote:
Originally posted by madhivanan
Can you post the code?
+1 to that!
N 56°04'39.26"E 12°55'05.63" |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-08-24 : 06:03:03
|
| Hi,There is no specific SP that I am working on right now but in general I am thinking that there is a select in the loop and then updates...Any way, I see what you mean now.Thank you all for your help. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-08-24 : 06:10:37
|
| Even it is a vast amount of updates then you wouldn't ever want to do it "one at a time" -- if you were paging it you would generally set a batch size and loop over that batch size using set based method.You should post the code.It's not clear from your reply what you are agreeing that you understand.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
cursor / while loop