| Subject | Re: [firebird-support] Monitoring query question |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2006-10-27T20:20:10Z |
lec_sas wrote:
anything (even in auto commit mode). Autocommit is not what you
want on a bulk load - there's a cost to starting and ending transactions
which becomes significant when the operation is done very frequently
with very little work accomplished between the start and end.
External tables are not under transaction control so you can see
changes there instantly. Unfortunately, you can't update them, so
you'd have to count the entries to figure out how many rows were
inserted. Not viable.
transaction commits. One function of events is to coordinate with
non-transactional systems (e.g. check printers) so only changes that
actually make it into the database cause events to fire.
records it intends to insert and how many times it's invoked the insert,
why can't it handle the progress bar without help from the database?
One alternative, I guess, is to have a counter table and run your
inserts through a stored procedure that inserts the row, updates the
counter, and returns the new counter number. You can make that work
with multiple inserters by giving the counter table two fields - a
transaction id (CURRENT_TRANSACTION) and count. If there's a row
for the transaction, increment the counter, otherwise store a new
row with a count of zero. Periodically delete all the rows in the
counter table.
Regards,
Ann
>Right. Commits are handled by the client - a trigger won't commit
> My first idea was to put an AFTER INSERT trigger to update an
> integer field in a random table and each time the trigger would
> execute, it would increase the count by 1, I could then query that
> field in real time. This didn't appear to work and I assumed it was
> because all the trigger updates didn't get committed until the parent
> query (insert into select from) was completed.
anything (even in auto commit mode). Autocommit is not what you
want on a bulk load - there's a cost to starting and ending transactions
which becomes significant when the operation is done very frequently
with very little work accomplished between the start and end.
External tables are not under transaction control so you can see
changes there instantly. Unfortunately, you can't update them, so
you'd have to count the entries to figure out how many rows were
inserted. Not viable.
>Events are under transaction control - they don't fire until the
> My second idea was to use events, however I read that events are
> also under transaction control, and that recurring events are not even
> queued up.
transaction commits. One function of events is to coordinate with
non-transactional systems (e.g. check printers) so only changes that
actually make it into the database cause events to fire.
>I guess I'm somewhat confused. If your application knows how many
records it intends to insert and how many times it's invoked the insert,
why can't it handle the progress bar without help from the database?
One alternative, I guess, is to have a counter table and run your
inserts through a stored procedure that inserts the row, updates the
counter, and returns the new counter number. You can make that work
with multiple inserters by giving the counter table two fields - a
transaction id (CURRENT_TRANSACTION) and count. If there's a row
for the transaction, increment the counter, otherwise store a new
row with a count of zero. Periodically delete all the rows in the
counter table.
Regards,
Ann