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

Reply via email to