Issue Details (XML | Word | Printable)

Key: CORE-4379
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Poor performance of explicit cursors containing correlated subqueries in the select list

Created: 29/Mar/14 04:55 PM   Updated: 23/Sep/15 11:21 AM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.1.2, 2.1.3, 2.5.0, 2.1.4, 2.5.1, 2.1.5, 2.5.2, 2.1.5 Update 1, 2.5.2 Update 1, 3.0 Alpha 1, 3.0 Alpha 2
Fix Version/s: 3.0 Beta 1

QA Status: Done successfully
Test Details:
est uses MON$ tables to gather statistics snapshot before and after each of query.
Database that is restored (''mon-stat-gathering-N_M.fbk') contains procedures for gathering statistics and view for displaying results in convenient form. In particular, this view has columns that show DIFFERENCE of natural and indexed reads, and thus we can estimate performance.
Confirmed for LI-T3.0.0.30981 (29-mar-2014): 200049999 indexed reads instead of 60'000-1 = 59'999.


 Description  « Hide
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).

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no subversion log entries for this issue yet.