thp_pkmi@yahoo.com [firebird-support]
2016-12-08 04:02:11 UTC
Dear all,
I compare two SQL select statements via flamerobin:
tables definition:
create table Master
( Gen integer constraint PK_Master primary key,
F1 varchar(20),
F2 varchar(50),
F3 varchar(50),
Entry timestamp,
User varchar(10) );
create table Detail1
( Gen1 integer constraint PK_Detail1 primary key,
Gen integer constraint FK_Detail1 references Master(Gen) on update cascade,
TrDate date,
Qty integer,
Entry timestamp,
User varchar(10) );
create table Detail2
( Gen2 integer constraint PK_Detail2 primary key,
Gen integer constraint FK_Detail2 references Master(Gen) on update cascade,
TrDate date,
Qty integer,
Amount bigint,
Entry timestamp,
User varchar(10) );
FIRST STATEMENT:
select Master.*, iif(exists(select 1 from Detail1 where Gen=Master.Gen rows 1) or exists(select 1 from Detail2 where Gen=Master.Gen rows 1), 1, 0) Ref from Master;
SECOND STATEMENT:
select Master.*, iif(exists(select 1 from Detail1 where Gen=Master.Gen) or exists(select 1 from Detail2 where Gen=Master.Gen), 1, 0) Ref from Master;
I expect the first select with "rows 1" will be faster and consumes less fetches than the second, but surprisingly flamerobin reports no difference in statistics of both. I observe statistics after two times execution of each statement.
I guess firebird always optimizes the EXISTS function, am I right ?
I appreciate every comment on this, thank you.
Best Regards,
Tjioe Hian Pin
I compare two SQL select statements via flamerobin:
tables definition:
create table Master
( Gen integer constraint PK_Master primary key,
F1 varchar(20),
F2 varchar(50),
F3 varchar(50),
Entry timestamp,
User varchar(10) );
create table Detail1
( Gen1 integer constraint PK_Detail1 primary key,
Gen integer constraint FK_Detail1 references Master(Gen) on update cascade,
TrDate date,
Qty integer,
Entry timestamp,
User varchar(10) );
create table Detail2
( Gen2 integer constraint PK_Detail2 primary key,
Gen integer constraint FK_Detail2 references Master(Gen) on update cascade,
TrDate date,
Qty integer,
Amount bigint,
Entry timestamp,
User varchar(10) );
FIRST STATEMENT:
select Master.*, iif(exists(select 1 from Detail1 where Gen=Master.Gen rows 1) or exists(select 1 from Detail2 where Gen=Master.Gen rows 1), 1, 0) Ref from Master;
SECOND STATEMENT:
select Master.*, iif(exists(select 1 from Detail1 where Gen=Master.Gen) or exists(select 1 from Detail2 where Gen=Master.Gen), 1, 0) Ref from Master;
I expect the first select with "rows 1" will be faster and consumes less fetches than the second, but surprisingly flamerobin reports no difference in statistics of both. I observe statistics after two times execution of each statement.
I guess firebird always optimizes the EXISTS function, am I right ?
I appreciate every comment on this, thank you.
Best Regards,
Tjioe Hian Pin