Subject Exists function
Author
Post date 2016-12-08T04:02:11Z

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