Excessive Log Space Usage ???

I have an invoice generation process that updates an integer and a smallint column at one step and then another smallint column in another step. About 20,000 rows are being updated in each step. This process is using almost 20 MB of log space. The statements that triggers most of the log usage is like this: update ticket set (invoice, ticket_status) = ((select invoice, 1 from invoice_build where custid = ticket.custid)) where ticket_status = 0 and exists (select * from invoice_temp where date = ticket.date and tracking = ticket.tracking); FYI: The table being updated (ticket) has a row size of 351 bytes. Both columns being updated are indexed. All columns in where clauses are indexed, includeing the temporary table (created with no log). Does the set (col1, col2) = (( .... )) construct somehow make the statement more log intensive? Thanks for any help, Jeff

Is this statement in a transaction? If you are using unbuffered logging (recommended) but don't do a specific "begin work;"/"end work;" each update probably turns into an implicit transaction, which causes a flush of the log. So you end up with lots of log pages that have a couple of log records at the beginning, and the rest of the space is wasted. If this is your problem, putting an explicit "begin work;" before the statement and "commit work;" after it should greatly reduce the log space used. - Kevin Jeff Larsen <larsen@qec.com> wrote in message news:3936dbba.199937915@client.ce.news.psi.net... > I have an invoice generation process that updates > an integer and a smallint column at one step and then > another smallint column in another step. About 20,000 > rows are being updated in each step. This process > is using almost 20 MB of log space. > > The statements that triggers most of the log usage is like this: > > update ticket set (invoice, ticket_status) = > ((select invoice, 1 from invoice_build where custid = ticket.custid)) > where ticket_status = 0 > and exists (select * from invoice_temp > where date = ticket.date and tracking = ticket.tracking);> > FYI: The table being updated (ticket) has a row size of 351 bytes. Both > columns being updated are indexed. All columns in where clauses are > indexed, includeing the temporary table (created with no log). > > Does the set (col1, col2) = (( .... )) construct somehow make > the statement more log intensive? > > Thanks for any help, > > Jeff >