Recently I have had to create an admin task that needs to be run on our 24gb database each day.  Unfortunately due to our company security I cannot show the DDL but will do my best to explain...

In this task (50000 lines of TSQL) I have had to use several cursors to complete the task.  This task is broken down into several smaller or sub tasks to perform the entire operation.

Though I admit I have not had the chance to fully analyze the task as yet to identify scans and the like (which I will get to shortly) however I wish to try and rid myself of the cursors first.

In one 'sub' task I managed to do this by converting a cursor into a simple insert statement:

Insert Into dbo.tbl

Select col1, col2, col3

From dbo.workingtbl

This managed to cut time down from 11 minutes to 3.  Working with approx 500,000 rows in the sql above.

The remaining cursors are becoming a little hard to work our.  In the cursor I open approximately 20 variables which are used throughout the loop..

Declare #x cursor for

select col1, col2... col20

from dbo.table

order by col1

fetch next from #x into @var1, @var2... @var20

while.....  (you all know the rest)

In the while loop above I perform close to 100 calculations, 5 to 10 updates and inserts etc....

With this many calculations and updates/inserts could anyone suggest a better method of this loop without using a cursor?  Or perhaps this is one of those isolated cases where a cursor is best?

Either way, any suggestions would be great!