search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.26k stars 216 forks source link

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

Closed firebird-automations closed 9 years ago

firebird-automations commented 10 years ago

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: FirebirdSQL/firebird@0e7d43a7f9a8018a45d035c71a259e196507153c FirebirdSQL/fbt-repository@41aefef11650743b615fbd7939c285e215f3c16d

====== 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 commented 10 years ago
Modified by: @dyemanov assignee: Dmitry Yemanov \[ dimitr \]
firebird-automations commented 10 years ago
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 commented 10 years ago

Commented by: @dyemanov

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

firebird-automations commented 10 years ago

Commented by: @dyemanov

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

firebird-automations commented 10 years ago
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 commented 10 years ago

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 commented 10 years ago

Commented by: @dyemanov

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

firebird-automations commented 10 years ago
Modified by: @dyemanov status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Fixed \[ 1 \] Fix Version: 3\.0 Beta 1 \[ 10332 \]
firebird-automations commented 9 years ago
Modified by: @pavel-zotov status: Resolved \[ 5 \] =\> Resolved \[ 5 \] QA Status: Done successfully
firebird-automations commented 9 years ago
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 commented 9 years ago
Modified by: @pcisar status: Resolved \[ 5 \] =\> Closed \[ 6 \]