Hi again Bee!

>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.

Sounds like a stored procedure would be useful for you!

>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?

I guess #1 would be influenced by the indexes you have defined (make them
selective, that is always important, especially for updates). I never do
update 200000 records in one statement, so I really cannot tell how quick
it should be and whether 35 seconds for changing that many records is slow
or not. Though Firebird need to create a new copy of every row you modify
due to its architecture (remember, it is a client/server database, and
other concurrent transactions may want to look at the new or old values),
so I wouldn't be surprised if this was normal speed.

#2 I guess could be rewritten in a couple of ways:

1)
SELECT count(DISTINCT a.a_field) FROM a_table a
JOIN another_table a2 ON a2.a_field = a.a_field
WHERE a2.a_field = a_constant);

2)
SELECT count(DISTINCT a_field) FROM a_table a
WHERE exists(SELECT * FROM another_table a2 WHERE a.a_field = a2.a_field
AND a2.a_field = a_constant);

3)
SELECT count(DISTINCT a_field) FROM a_table a
WHERE a.a_field = a_constant
AND exists(SELECT * FROM another_table a2 WHERE a.a_field = a2.a_field)

The last option is only possible if you really join on the same column that
you use to compare to a_constant. I don't know whether they will be faster
than the select you currently have (using IN <subselect> used to be slow),
but it is worth a try.

HTH,
Set