> Erm, I don't think so. One connection can support as many
> transactions as you need. When you commit, the transaction
> is finished, so you have to start a new one to do the next
> batch of updates. That's what was so weird about that
> pseudocode that you posted originally.

The code for transaction process was not included in my previous
pseudo-code.

> I hope you can do this task in less than 1 minute (unless
> you are doing some really complicated updates)

Yes, I'm doing a pretty complicated updates. In my case, I can't do
such:

UPDATE a_table SET a_field = a_constant
WHERE a_field = match_condition;

for many rows at once. Because, the a_constant is not a really constant
value, it depends on the value of some other fields of the row. And the
(a_field = match_condition) is not as simple as it looks. Both are taken
from result of a SELECT command on some tables. I have to analyze every
row of the a_table to know whether it need to be updated or not, if it
does then I have to set a_constant to a correct value based on the
appropriate data from SELECT command result. Then if it need to be
updated, I have to update and commit it right away because some users
(or applications) may want to see that it has been updated by another
user (or application). With almost 600,000 record in the table, I
realized that it'd be a not fast process.

> make sure you are not recreating the statement every time
> this loop runs. Use parameters!!!!

Yup, that's another task of optimization job and I'm still working on
it.

Yesterday, I've found some other weird behaviours of Firebird. I've
searching articles and manual on the web about it but I can't find
nothing. I have sql statements like these:

#1:
UPADTE table SET a_field = a_constant
WHERE a_field = another_constant;
COMMIT;

#2:
SELECT count(DISTINCT a_field) FROM a_table
WHERE a_field IN (SELECT a_field FROM another_table
WHERE a_field = a_constant);

Command #1 took about 35 secs (with about 100,000 - 200,000 rows
affected), though it's a very simple update statement. And the command
#2 took about 15 secs. Is Firebird that slow?

It seems I have many troubles with Firebird, eh??? :)

Regards,

-Bee-



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.486 / Virus Database: 284 - Release Date: 29-05-2003