Try this code. you might save remaining 12 Minutes also..

UPDATE
 B
SET
 b.id = A.id
FROM
 dbo.many_tbl A inner join dbo.OutpUt_tbl B
 ON A.id = B.ID

Jeswanth

I can't see the purpose of the ORDER BY clause.  If you were to remove this from your select statement, you may find that you improve the performance.  It may also be the case that the outer loop has an ORDER BY clause that can also be removed.

Profile the procedure to determine what indexes are being used on the tables.  Ensure that the indexes in use are the most efficient. You may be able to improve the performance yet again by creating covering indexes.

If you don't believe this example take a look at the example I posted which Noeld and Remi helped me convert:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=197948

This went from 2 hours to 6:46 to 0:26 in the final iteration!!

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!

Great article.  A lot of people make the mistake of trying to use SQL as a procedural language instead of a declarative language.  It's hard for some to think in terms of SQL set-based statements as opposed to iterative looping and cursors.

I'd estimate that cursors could be eliminated and replaced with set-based SQL statements 99.9999% of the time that they are used.

In addition to what was already stated about creating a temp table to hold intermediate results, if your calculations are conditional using IF..ELSE constructs, you might look at converting them to CASE expressions and/or modifying your WHERE clauses.

Without seeing your DDL it's impossible to get much more detailed than that.

Hi,

Good Article!

In my opinion cursor usage should depend on "How Often". Cursors provide an easy programming solution when the extensive row processing should be done. If something should be done only a couple of times like in one time data migration tasks, I ususally opt for cursors. If something has to run every day and takes a lot of time than the set approach should be researched and maybe some database architecture changes including de-normalizing the database should be done.

Yelena