Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Poor performance of explicit cursors containing correlated subqueries in the select list [CORE4379] #4701

Closed
firebird-automations opened this issue Mar 29, 2014 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

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 http://x.id>http://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 http://x.id>http://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 http://x.id>http://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 http://x.id>http://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).

Commits: 0e7d43a FirebirdSQL/fbt-repository@41aefef

====== 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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Poor performance of updating when use explicit cursor => Poor performance of explicit cursors containing correlated subqueries in the select list

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The issue has nothing to do with updates, I've adjusted the ticket subject accordingly.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Fixed in v3.0 only so far, please test and report back.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Alpha 2 [ 10560 ]

Version: 3.0 Alpha 1 [ 10331 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.1.5 Update 1 [ 10522 ]

Version: 2.5.2 [ 10450 ]

Version: 2.1.5 [ 10420 ]

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> Fixed in v3.0 only so far, please test and report back.

Checked on LI-T3.0.0.31003, result: OK.

Trace statistics:

1) Direct SQL command:

update t a set f01 = (select f01 from t x where http://x.id>http://a.id order by id rows 1)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (X ORDER RDB$PRIMARY305)
PLAN (A NATURAL)
0 records fetched
284 ms, 329341 fetch(es), 40178 mark(s)

Table Natural Index Update Insert Delete
xpunge
**********************************************************************************
******
RDB$INDICES 2

T 20000 39999 20000

2) Explicit cursor with WHERE CURRENT OF clause:

execute block as
declare c_cur cursor for (select (select f01 from t x where http://x.id>http://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$PRIMARY305)
PLAN (A NATURAL)
PLAN (X ORDER RDB$PRIMARY305)
0 records fetched
451 ms, 472 write(s), 373201 fetch(es), 60268 mark(s)

Table Natural Index Update
*****************************************************
T 20000 39999 20000

3) Explicit cursor with RDB$DB_KEY:
execute block as
declare v_key char(8);
declare c_cur cursor for (select a.rdb$db_key, (select f01 from t x where http://x.id>http://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$PRIMARY305)
PLAN (A NATURAL)
PLAN (X ORDER RDB$PRIMARY305)
PLAN (T INDEX ())
0 records fetched
438 ms, 360579 fetch(es), 20000 mark(s)

Table Natural Index Update
***************************************************
T 20000 59999 20000

PS. Interesting statistics can be seen in case when table contains 1'000'000 rows instead of 20'000: variant with RDB$DB_KEY wins the WHERE CURRENT OF one, despite of indexed reads value is greater on 1'000'000 in RDB$DB_KEY:.

Plans and statistics:

1) case of WHERE CURRENT OF:

PLAN (X ORDER RDB$PRIMARY305)
PLAN (A NATURAL)
PLAN (X ORDER RDB$PRIMARY305)
0 records fetched
20665 ms, 16290 read(s), 20548 write(s), 19709259 fetch(es), 3016001 mark(s)

Table Natural Index Update
*****************************************************
RDB$PAGES 1

T 1000000 1999999 1000000

2) case of RDB$DB_KEY:

PLAN (X ORDER RDB$PRIMARY305)
PLAN (A NATURAL)
PLAN (X ORDER RDB$PRIMARY305)
PLAN (T INDEX ())
0 records fetched
19960 ms, 14829 read(s), 19029634 fetch(es), 1000000 mark(s)

Table Natural Index Update
***************************************************************
T 1000000 2999999 1000000

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Backport into v2.x is possible but a bit complicated, I'm deferring it for a while.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment