Re: [firebird-support] Performing bulk update taking time.
27.04.2017 9:03, 'Joje' j...@codework-solutions.com [firebird-support] wrote: > 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 > qu ery…? Query in FOR SELECT does not need JOIN. MERGE will do that in one shoot. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Performing bulk update taking time.
On 27-4-2017 09:03, 'Joje' j...@codework-solutions.com [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 See https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html How well that really performs depends on the presence of a suitable index in table_b. -- Mark Rotteveel
[firebird-support] Performing bulk update taking time.
Hello everyone, 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 - AS - DECLARE VARIABLE REF_A_ID BIGINT; - DECLARE VARIABLE AR_ID VARCHAR(200); - DECLARE VARIABLE _ID BIGINT; - - BEGIN - FORSELECT 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 - DO -BEGIN - UPDATE TABLEB - SETB. REF_A_ID=: REF_A_ID - WHERE ( B. REF_A_IDISNULL AND B. _ID = :_ID) AND (B. AR_ID = :AR_ID); -END - END - 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. With Regards, Joje
