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
