Re: Why update SQL in procedure did not commit?

"C Chang" <cschang_at_maxinter.net> wrote in message news:3DF02E28.25CA_at_maxinter.net...
> Justin Cave wrote:
> >
> > "C Chang" <cschang_at_maxinter.net> wrote in message
> > news:3DEEE0DA.5B4B_at_maxinter.net...
> >
> > ADO, by default, will autocommit. That is, whenever you issue a
successful
> > SQL statement, it will tell Oracle to commit it. If you do a
BeginTrans,
> > however, you will turn off this autocommit behavior & you'll have to
manage
> > your own transactions.
> >
> Thanks. Justin. But which one is more resource (DB) efficient?

Committing is a rather expensive database operation, so you'll get better performance by committing less frequently. If the choice is between you doing an explicit commit after every statement versus ADO autocommitting, the autocommit may be able to piggy-back with the statement and save a network round-trip.

Fundamentally, however, you should be committing whenever you've finished a logical transaction-- no more or less frequently. Performance is nice, but don't sacrifice the requirements of your project (and one of the requirements is the boundaries of a logical transaction, whether the requirements doc says so or not).

Justin Cave Received on Fri Dec 06 2002 - 01:20:26 CST