-
-
Notifications
You must be signed in to change notification settings - Fork 212
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
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovsummary: Poor performance of updating when use explicit cursor => Poor performance of explicit cursors containing correlated subqueries in the select list |
Commented by: @dyemanov The issue has nothing to do with updates, I've adjusted the ticket subject accordingly. |
Commented by: @dyemanov Fixed in v3.0 only so far, please test and report back. |
Modified by: @dyemanovVersion: 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 ] |
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) Table Natural Index Update Insert Delete T 20000 39999 20000 2) Explicit cursor with WHERE CURRENT OF clause: execute block as Table Natural Index Update 3) Explicit cursor with RDB$DB_KEY: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Table Natural Index Update 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) Table Natural Index Update T 1000000 1999999 1000000 2) case of RDB$DB_KEY: PLAN (X ORDER RDB$PRIMARY305) Table Natural Index Update |
Commented by: @dyemanov Backport into v2.x is possible but a bit complicated, I'm deferring it for a while. |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Beta 1 [ 10332 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] Test Details: est uses MON$ tables to gather statistics snapshot before and after each of query. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
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.
The text was updated successfully, but these errors were encountered: