Subject Re: [firebird-support] Performing bulk update taking time.
Author Mark Rotteveel
On 27-4-2017 09:03, 'Joje' joje@...
[firebird-support] wrote:
> Is there anyway using which we could perform bulk update in firebird.
> I have two tables A & B. Both tables are referenced by a *varchar(100)*
> *AR_ID* column.
> - Total number of rows in each table is around *1 million*. Now
> in order to get faster result I created a *bigint* column *ref_A_ID* in
> Table B that is referenced to *A_ID* column of *table A.*
> - Now, to reference existing data in table B. I want to update
> column *ref_A_ID* of table B with value of *A_ID* of table A for proper
> linking to table A.
> - I created a query for updating table B is as below
> - Is there a way through which this query can be further
> optimised as this query taking time for completion more than 3 -4 hours.
> Could I use batch based update in this query…?
> Thanks.

You should try MERGE instead of that EXECUTE BLOCK, it is likely (but
not guaranteed) to perform better.

eg something like:

merge into table_b as b
using table_a as a
on b._id = a._id and b.ar_id = a.ar_id and b.ref_a_id is null
when matched then
update set b.ref_a_id = a.ref_a_id


How well that really performs depends on the presence of a suitable
index in table_b.

Mark Rotteveel