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
