Hi all,

Within a transaction, second update to a table is much slower.

I shall explain.
The sequence of execution is .

1)Start a transaction.

2) Run the query -

UPDATE trg_trg
SET trg_trg.trg_prsd = 2
WHERE trg_trg.trg_prsd = 0
AND EXISTS (SELECT 1
FROM emp_emp
WHERE emp_emp.emp_wgr_id = 101
AND emp_emp.emp_id = trg_trg.trg_emp_id)

(executes in about 2 seconds).

3) Do some processing on some other tables... reading the updated
records from the db.

4) Run the query

UPDATE trg_trg
SET trg_trg.trg_prsd = 1
WHERE trg_trg.trg_prsd = 2
AND EXISTS (SELECT 1
FROM emp_emp
WHERE emp_emp.emp_wgr_id = 101
AND emp_emp.emp_id = trg_trg.trg_emp_id)

5)Commit transaction.

The problem is the second update in the 4 th step is much slower.
Takes about 16 seconds if executes in within the transaction. If I
commit the transaction before executing it,
The second query as fast as the first update. The table trg_trg has
about 26000 records in the test database and emp_emp table has 19 records.

We use Windows 2000(SP 4) Delphi 6 - ADO - IB Free provider, FireBird
2.1.2 super server ( the same problem was in 2.1.1 so i updated the server)

The same behavior can be reproduced in FlameRobin ( without doing the
3rd step)

Any idea why this behavior? How to handle this?

Sorry for posting a lengthy query. English is not my native language.

Thanks
Jaison.