|Subject||Performing bulk update taking time.|
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
- EXECUTE BLOCK
- DECLARE VARIABLE REF_A_ID BIGINT;
- DECLARE VARIABLE AR_ID VARCHAR(200);
- DECLARE VARIABLE _ID BIGINT;
- FOR SELECT A. AR_ID, A.A_ID, A. _ID
- FROM TABLE A
- INNER JOIN TABLE B ON B. AR_ID = A. AR_ID
- INTO :AR_ID, : REF_A_ID, :_ID
- UPDATE TABLE B
- SET B. REF_A_ID = : REF_A_ID
- WHERE ( B. REF_A_ID IS NULL AND B. _ID = :_ID) AND (B. AR_ID = :AR_ID);
- 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…?