cursor is running too slow

I have a cursor that is taking about 4:00 minutes to run on 100 records. Th
e
cursor works on 11 different tables and inserts on average around 25 records
per table. Does this time frame sound right? When I do the same thing with
a temp table it only takes about 2:00 minutes for 1000 records. I can't use
temp tables though because I want to be able to rollback each transaction
independently if there is a problem. Any suggestions on decreasing the time
my cursor takes to run?Cursors are inherently slow. You haven't posted your DDL and what you're
really trying to do.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
I have a cursor that is taking about 4:00 minutes to run on 100 records.
The
cursor works on 11 different tables and inserts on average around 25 records
per table. Does this time frame sound right? When I do the same thing with
a temp table it only takes about 2:00 minutes for 1000 records. I can't use
temp tables though because I want to be able to rollback each transaction
independently if there is a problem. Any suggestions on decreasing the time
my cursor takes to run?|||"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
>I have a cursor that is taking about 4:00 minutes to run on 100 records.
>The
> cursor works on 11 different tables and inserts on average around 25
> records
> per table. Does this time frame sound right? When I do the same thing
> with
> a temp table it only takes about 2:00 minutes for 1000 records. I can't
> use
> temp tables though because I want to be able to rollback each transaction
> independently if there is a problem. Any suggestions on decreasing the
> time
> my cursor takes to run?
Usually the best cursor optimization is to get rid of the cursor...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If re-writing this T-SQL to something set based is not practical at the
moment, then test if specifying the FAST_FORWARD and READ_ONLY options when
declaring the cursor help improve it's performance.
Read up on DECLARE CURSOR in SQL Server Books Online.
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:7A67BFAB-A4F1-49F6-9C9B-1FC6FD988804@.microsoft.com...
>I have a cursor that is taking about 4:00 minutes to run on 100 records.
>The
> cursor works on 11 different tables and inserts on average around 25
> records
> per table. Does this time frame sound right? When I do the same thing
> with
> a temp table it only takes about 2:00 minutes for 1000 records. I can't
> use
> temp tables though because I want to be able to rollback each transaction
> independently if there is a problem. Any suggestions on decreasing the
> time
> my cursor takes to run?