You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
/* INIT */
create table tmp_tbl1 (fld1 integer, fld2 integer, fld3 integer);
create index tmp_tbl1_fld1 on tmp_tbl1(fld1);
create index tmp_tbl1_fld2 on tmp_tbl1(fld2);
commit;
create table tmp_tbl2 (fld1 integer, fld2 integer);
create index tmp_tbl2_fld1 on tmp_tbl2(fld1);
create index tmp_tbl2_fld2 on tmp_tbl2(fld2);
commit;
create table tmp_tbl3 (fld1 integer);
create index tmp_tbl3_fld1 on tmp_tbl3(fld1);
commit;
set term ^;
create or alter procedure tmp_sp1
returns (fld1 integer)
as begin
fld1=1;
suspend;
end ^
create or alter procedure tmp_sp2
as
declare variable I integer;
begin
i=0;
while (i<10000) do begin
i=i+1;
insert into tmp_tbl1 values (:i, 1, 3);
end
i=0;
while (i<10) do begin
i=i+1;
insert into tmp_tbl2 values (:i, 2);
end
insert into tmp_tbl3 values (3);
end ^
set term ;^
commit;
execute procedure tmp_sp2;
commit;
SET STATISTICS INDEX TMP_TBL1_FLD1;
SET STATISTICS INDEX TMP_TBL1_FLD2;
SET STATISTICS INDEX TMP_TBL2_FLD1;
SET STATISTICS INDEX TMP_TBL2_FLD2;
SET STATISTICS INDEX TMP_TBL3_FLD1;
commit;
-- Test:
select t2.fld1
from tmp_tbl2 t2
join tmp_tbl1 t1 on t1.fld1=t2.fld1
join tmp_sp1 p1 on p1.fld1=t1.fld2
join tmp_tbl3 t3 on t3.fld1=t1.fld3
where t2.fld2=2;
FB 1.5:
PLAN JOIN (TMP_SP1 NATURAL,JOIN (T2 INDEX (TMP_TBL2_FLD2),T1 INDEX (TMP_TBL1_FLD2,TMP_TBL1_FLD1),T3 INDEX (TMP_TBL3_FLD1)))
Reads = 0
Writes 0
Fetches = 366
FB 2.5:
PLAN JOIN (JOIN (TMP_SP1 NATURAL, T1 INDEX (TMP_TBL1_FLD2)), T2 INDEX (TMP_TBL2_FLD1), T3 INDEX (TMP_TBL3_FLD1)) <---- different plan
Reads = 0
Writes 0
Fetches = 40094 <----- causes significant performance degradation when executed many times under load
====== Test Details ======
29.11.2016:
Found poor performance in 4.0; waiting for fix (letter to dimitr, 29.11.2016 10:07)
30.11.2016:
Test added, but current FB 4.0 snapshots should FAIL on this test.
Commit was done after discuss with dimitr (letter 30.11.2016 10:21)
The text was updated successfully, but these errors were encountered:
I see a different plan for v1.5:
PLAN JOIN (TMP_SP1 NATURAL,JOIN (T2 INDEX (TMP_TBL2_FLD2),T1 INDEX (TMP_TBL1_FLD1),T3 INDEX (TMP_TBL3_FLD1)))
The one you have provided is reported by v2.5 accessing the ODS10 database.
Also, if the procedure generates 1000 records instead of one, number of fetches changes:
v1.5 = 72000
v2.5 = 43000
And this is quite expected, because the v1.5 plan looks actually worse, because stream T2 doesn't depend on SP1 and thus will be retrieved (along with T1 and T3) per every record returned by SP1.
Hence I'm not convinced this is a regression, especially provided that the engine cannot estimate cardinalities for stored procedures.
Test Details: Found poor performance in 4.0; waiting for fix (letter to dimitr, 29.11.2016 10:07) => 29.11.2016:
Found poor performance in 4.0; waiting for fix (letter to dimitr, 29.11.2016 10:07)
30.11.2016:
Test added, but current FB 4.0 snapshots should FAIL on this test.
Commit was done after discuss with dimitr (letter 30.11.2016 10:21)
Submitted by: @pcisar
Is related to CORE3451
Test case:
/* INIT */
create table tmp_tbl1 (fld1 integer, fld2 integer, fld3 integer);
create index tmp_tbl1_fld1 on tmp_tbl1(fld1);
create index tmp_tbl1_fld2 on tmp_tbl1(fld2);
commit;
create table tmp_tbl2 (fld1 integer, fld2 integer);
create index tmp_tbl2_fld1 on tmp_tbl2(fld1);
create index tmp_tbl2_fld2 on tmp_tbl2(fld2);
commit;
create table tmp_tbl3 (fld1 integer);
create index tmp_tbl3_fld1 on tmp_tbl3(fld1);
commit;
set term ^;
create or alter procedure tmp_sp1
returns (fld1 integer)
as begin
fld1=1;
suspend;
end ^
create or alter procedure tmp_sp2
as
declare variable I integer;
begin
i=0;
while (i<10000) do begin
i=i+1;
insert into tmp_tbl1 values (:i, 1, 3);
end
i=0;
while (i<10) do begin
i=i+1;
insert into tmp_tbl2 values (:i, 2);
end
insert into tmp_tbl3 values (3);
end ^
set term ;^
commit;
execute procedure tmp_sp2;
commit;
SET STATISTICS INDEX TMP_TBL1_FLD1;
SET STATISTICS INDEX TMP_TBL1_FLD2;
SET STATISTICS INDEX TMP_TBL2_FLD1;
SET STATISTICS INDEX TMP_TBL2_FLD2;
SET STATISTICS INDEX TMP_TBL3_FLD1;
commit;
-- Test:
select t2.fld1
from tmp_tbl2 t2
join tmp_tbl1 t1 on t1.fld1=t2.fld1
join tmp_sp1 p1 on p1.fld1=t1.fld2
join tmp_tbl3 t3 on t3.fld1=t1.fld3
where t2.fld2=2;
FB 1.5:
PLAN JOIN (TMP_SP1 NATURAL,JOIN (T2 INDEX (TMP_TBL2_FLD2),T1 INDEX (TMP_TBL1_FLD2,TMP_TBL1_FLD1),T3 INDEX (TMP_TBL3_FLD1)))
Reads = 0
Writes 0
Fetches = 366
FB 2.5:
PLAN JOIN (JOIN (TMP_SP1 NATURAL, T1 INDEX (TMP_TBL1_FLD2)), T2 INDEX (TMP_TBL2_FLD1), T3 INDEX (TMP_TBL3_FLD1)) <---- different plan
Reads = 0
Writes 0
Fetches = 40094 <----- causes significant performance degradation when executed many times under load
====== Test Details ======
29.11.2016:
Found poor performance in 4.0; waiting for fix (letter to dimitr, 29.11.2016 10:07)
30.11.2016:
Test added, but current FB 4.0 snapshots should FAIL on this test.
Commit was done after discuss with dimitr (letter 30.11.2016 10:21)
The text was updated successfully, but these errors were encountered: