I have a sql script that does an update on a large table using an un-
optimized where clause.  If I don't use transactions then it completes in 7
seconds.  If I use a transaction it takes 13 minutes!

Is there anything I can do?

--
Thanks,
Michael Binette

I just tried changing the index so that it WAS optimized and it was still
slow.

Michael,

Yes, don't use a transaction. Queries implicitly use optimized
restricted transactions on the updated table (have a look at chapter
2.12 SQL and Transactions of the manual). I don't think that you will
get any better performance if you place transactions on your own.

Arnd

Michael Binette wrote:

> I have a sql script that does an update on a large table using an un-
> optimized where clause.  If I don't use transactions then it completes in 7
> seconds.  If I use a transaction it takes 13 minutes!
>
> Is there anything I can do?
>
> --
> Thanks,
> Michael Binette

Michael,

<< I have a sql script that does an update on a large table using an
un-optimized where clause.  If I don't use transactions then it completes in
7 seconds.  If I use a transaction it takes 13 minutes!

Is there anything I can do? >>

Arnd is correct.  UPDATEs already use transactions for the commit batches
during their execution, and their done in an optimal way.  If the UPDATE is
interrupted due to an improper shutdown, etc. then just run the UPDATE again
and it will begin where it left off before.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:70C7C0CA-A259-4574-98AF-2CAE0A0CD9AD@news.elevatesoft.com:

> Michael,
>
> << I have a sql script that does an update on a large table using an
> un-optimized where clause.  If I don't use transactions then it
> completes in 7 seconds.  If I use a transaction it takes 13 minutes!
>
>  Is there anything I can do? >>
>
> Arnd is correct.  UPDATEs already use transactions for the commit
> batches during their execution, and their done in an optimal way.  If
> the UPDATE is interrupted due to an improper shutdown, etc. then just
> run the UPDATE again and it will begin where it left off before.
>

Yes but my update script contains 3 update statements and I also have a
table that is being posted to.  That is why I am using a transaction.  I
want it all to either pass or fail.  It doesn't do me much good if it
fails in the middle.  Then I have to try to determine at which step it
failed at.

Michael,

<< Yes but my update script contains 3 update statements and I also have a
table that is being posted to.  That is why I am using a transaction.  I
want it all to either pass or fail.  It doesn't do me much good if it fails
in the middle.  Then I have to try to determine at which step it failed at.
>>

In that case you'll have to deal with the performance impact of the
transaction caching a very large number of updates at one time.

--
Tim Young
Elevate Software
www.elevatesoft.com