Poor performance of updating when use explicit cursor -----------------------------------------------------
Key: CORE-4379 URL: http://tracker.firebirdsql.org/browse/CORE-4379 Project: Firebird Core Issue Type: Bug Components: Engine Reporter: Pavel Zotov Priority: Minor DDL: ==== drop sequence g; create sequence g; recreate table t(id int primary key, f01 int); commit; delete from t; insert into t select gen_id(g,1), gen_id(g,0)*10 from rdb$types,rdb$types rows 20000 ; set heading off; select count(*) from t; commit; Suppose that we have to replace in all records field F01 with values of this field in the "next" record in order of ascending field ID. So, for rowset: ID F01 === ============ 1 10 2 20 3 30 4 40 - result shoud be following: ID F01 === ============ 1 20 2 30 3 40 4 50 (record with max ID will contain NULL in F01). Var-1. Pure SQL: update t a set f01 = (select f01 from t x where x.id>a.id order by id rows 1); Trace: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ PLAN (X ORDER RDB$PRIMARY30 INDEX (RDB$PRIMARY30)) PLAN (A NATURAL) 0 records fetched 12738 ms, 1 write(s), 644931 fetch(es), 48792 mark(s) Table Natural Index Update Insert Delete ********************************************************************************* RDB$INDICES 2 T 20000 39973 20000 Var-2. PSQL with implicit FOR-SELECT cursor - performance also OK: execute block as declare v_next_f01 int; declare v_id int; begin for select id,(select f01 from t x where x.id>a.id order by id rows 1) from t a into v_id, :v_next_f01 do update t set f01 = :v_next_f01 where id = :v_id; end ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ PLAN (X ORDER RDB$PRIMARY24 INDEX (RDB$PRIMARY24)) PLAN (T INDEX (RDB$PRIMARY24)) PLAN (A NATURAL) 0 records fetched 13497 ms, 1 write(s), 744955 fetch(es), 48792 mark(s) Table Natural Index Update Insert ************************************************************************ RDB$INDICES 2 T 20000 59973 20000 Var-3. Explicit cursor, using UPDATE ... WHERE CURRENT OF ...: performance is VERY poor. execute block as declare c_cur cursor for (select (select f01 from t x where x.id>a.id order by id rows 1) from t a); declare v_next_f01 int; begin open c_cur; while (1=1) do begin fetch c_cur into v_next_f01; if (row_count = 0) then leave; update t set f01 = :v_next_f01 where current of c_cur; end close c_cur; end ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ PLAN (X ORDER RDB$PRIMARY29 INDEX (RDB$PRIMARY29)) PLAN (A NATURAL) PLAN (X ORDER RDB$PRIMARY29) 0 records fetched 689480 ms, 1 write(s), 600541233 fetch(es), 48792 mark(s) Table Natural Index Update Insert Delete Backout Purge ******************************************************************************************************** RDB$INDICES 2 T 20000 200029999 20000 Var-4. Explicit cursor, using UPDATE + RDB$DB_KEY: performance also very bad: execute block as declare v_key char(8); declare c_cur cursor for (select a.rdb$db_key, (select f01 from t x where x.id>a.id order by id rows 1) from t a); declare v_next_f01 int; begin open c_cur; while (1=1) do begin fetch c_cur into v_key, v_next_f01; if (row_count = 0) then leave; update t set f01 = :v_next_f01 where rdb$db_key = :v_key; end close c_cur; end ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ PLAN (X ORDER RDB$PRIMARY31 INDEX (RDB$PRIMARY31)) PLAN (A NATURAL) PLAN (X ORDER RDB$PRIMARY31) PLAN (T INDEX ()) 0 records fetched 643594 ms, 1 write(s), 600581233 fetch(es), 48792 mark(s) Table Natural Index Update Insert Delete Backout Purge Expunge *************************************************************************************************************** RDB$INDICES 2 T 20000 200049999 20000 Tested on: WI-V2.5.3.26726, SuperClassic LI-T3.0.0.30981, SuperServer (FW = OFF in both). -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel