| Author |
Topic |
|
Ex
Posting Yak Master
166 Posts |
 Posted - 2005-01-04 : 01:02:08
|
| Hey allI am just in the process of testing cursors vs using while loopscurrently i have a few cursors within triggers and stored procs unfortunately all cursors i have used have stored procedure calls within them and couldn’t be converted into set base operationsafter much testing i have found using while loops seems to be more efficient just wanted to make sure I was right before I changed them all ( I will test all vs cursors for efficiency)but just wondering my 2nd method consists of1) create a table datatype( with extra identity col) with all the values that would be passed into the stored proc (that lies within the cursor)2) using the identify col i scroll through the table datatype and call the stored procedure row by rowjust wondering is there any instance anyone could think of where the use of the while loop would in fact be slower than the cursor? Note the cursors are very complex they have a lot of selects updates etc as well as stored procedure calls:) ta |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-04 : 01:10:31
|
| >> unfortunately all cursors i have used have stored procedure calls Maybe you should consider trying to redesign that.Note that a stored proc can act on a recordset as input - doesn't have to be parameters.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Ex
Posting Yak Master
166 Posts |
Posted - 2005-01-04 : 17:06:49
|
| by recordset this similar to result set? sorry not to sure?do you mean passing in a table variable as the parameter to the sp? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-04 : 17:15:37
|
| Cursors and while loops both perform looping. Cursors are slightly slower as they use a server side object which requires memory. I wouldn't change your code from cursors to while loops as you won't get much performance improvement. If you have the time to do it, then sure go ahead. It would be much better if you spent your time on redesigning your system though. Just because it makes stored procedure calls doesn't mean that it can't be set-based. You wouldn't use those stored procedures as they can only handle one at a time, so you would create new ones that can handle sets.Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-04 : 17:20:33
|
| try to write your procs in such as manner that they accept arguements that give it enough info to go and find the rows it needs to operate on all at once, instead of finding those rows outside of the stored proc and passing them in one by one.If the stored proc that operates on 1 row at a time is doing something external to SQL Server, such as sending an email or importing a file or something like that, then don't bother trying to optimize, in those cases enumerating through rows with a cursor is fine.- Jeff |
|
|
|
Ex
Posting Yak Master
166 Posts |
Posted - 2005-01-04 : 17:36:37
|
| Problem is the tables are really random cant really pass info into the sp so it knows the rows to operate onWould it be better to create a temp table and the sp access this ( alough I guess it would need to be deleted when finished)Or create a table datatype and pass it into the sp? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-04 : 18:01:45
|
| Why is a single stored proc potnetially working on many tables? A single stored proc should have a focused purpose that operates on a clear entity, and all of your data that is of the same entity should be stored in the same table. For example, you should not have 1 table of invoices per customer or things like that -- they should all be in 1 table.Unless this stored proc is a DBA maintenance-type proc, a stored procedure should NOT accept table names or column names ever as input, and should never dynamically need to determine which tables to operate on. These things very often indicate a bad database design. Perhaps you should give us more information on what you are trying to do, with an example, some sample data, and the DDL of the relevant tables.- Jeff |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-04 : 18:30:51
|
| >> Would it be better to create a temp table and the sp access this yes( alough I guess it would need to be deleted when finished)No - it will be dropped automatically when the creating batch terminates - but it's good form to execute the explicit drop in that batch.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Cursors vs While loop