If anyone can see why these two queries perform differently it would be appreciated. (BTW, the DDL below is an example only).

Both queries seem straightforward and are performing STARTING WITH tests on a base (person) table. There are 114k records. The surprising thing is that query with an extra join (A1) performs fine whereas the simpler query (B1/B2) is ~ 10x slower.

I've tried to cut it right down but haven't resolved it. Also the person table has now had 2 additional fields added to try to improve things, they seem to help a bit but still disappointing performance. Having seen how good FB is, it seems something is working against things here but I cannot see what it might be.

/*--------------------------- STATS -------------------------*/
FB version (2.1.3.18185 live) & (2.1.4.18368 dev) in Winserver 2003

person 113,914
companyperson 59,795
extendedperson 52,923
company 12,971

person (companypersonid>0 ) 60,825
person (extendedpersonid>0) 52,969

/*---------------------------- SQL -------------------------*/
(A1) person query + company join - fast (1-2 secs, ~2000 rows)
select cc.id,co.name as company,cn.firstname,cn.lastname,cc.jobtitle,cc.kind,cc.category,cc.status,cn.name,co.id as companyid
from companyperson cc
join company co on cc.companyid=co.id
join person cn on cc.personid=cn.id
where (upper(cn.name) starting with 'S')

PLAN JOIN (CN INDEX (I_PERSON_NAME, I_PERSON_COMPANYPERSONID), CC INDEX (FK_COMPANYPERSON_PERSONID), CO INDEX (K_COMPANY))

(B1) person query - slow (11-12 secs, ~2000 rows)
select ca.id,cn.firstname,cn.lastname,cn.email1,cn.dateofbirth, ca.kind,ca.category,ca.status,cn.name
from extendedperson ca
join person cn on ca.personid=cn.id
where (upper(cn.name) starting with 'S')

PLAN JOIN (CN INDEX (I_PERSON_NAME), CA INDEX (FK_EXTENDEDPERSON_PERSONID))

(B2) person query - slightly quicker but still too slow (10 secs, ~2000 rows) uses additional clause on person table (and cn.extendedpersonid>0 ) to use I_PERSON_EXTENDEDPERSONID index

select ca.id,cn.firstname,cn.lastname,cn.email1,cn.dateofbirth, ca.kind,ca.category,ca.status,cn.name
from extendedperson ca
join person cn on ca.personid=cn.id and cn.extendedpersonid>0
where (upper(cn.name) starting with 'S')

PLAN JOIN (CN INDEX (I_PERSON_NAME, I_PERSON_EXTENDEDPERSONID), CA INDEX (FK_EXTENDEDPERSON_PERSONID))

/*----------------------------- DDL -------------------------*/
CREATE TABLE PERSON
(
ID INTEGER DEFAULT 0 NOT NULL,
FIRSTNAME VARCHAR(50) DEFAULT 'TBA' NOT NULL,
LASTNAME VARCHAR(50) NOT NULL,
...
/* these next 2 fields are 'back-pointers' to key tables
for app convenience/practical purposes */
EXTENDEDPERSONID INTEGER DEFAULT 0,
COMPANYPERSONID INTEGER DEFAULT 0
);
ALTER TABLE PERSON ADD CONSTRAINT K_PERSON PRIMARY KEY (ID);
CREATE INDEX I_PERSON_NAME ON PERSON COMPUTED BY (UPPER(LASTNAME));
CREATE INDEX I_PERSON_EXTENDEDPERSONID ON PERSON (EXTENDEDPERSONID);
CREATE INDEX I_PERSON_COMPANYPERSONID ON PERSON (COMPANYPERSONID);


CREATE TABLE EXTENDEDPERSON
(
ID INTEGER DEFAULT 0 NOT NULL,
PERSONID INTEGER DEFAULT 0 NOT NULL,
....
);
ALTER TABLE EXTENDEDPERSON ADD CONSTRAINT K_EXTENDEDPERSON PRIMARY KEY (ID);
ALTER TABLE EXTENDEDPERSON ADD CONSTRAINT FK_EXTENDEDPERSON_PERSONID FOREIGN KEY (PERSONID) REFERENCES PERSON (ID);

CREATE TABLE COMPANYPERSON
(
ID INTEGER DEFAULT 0 NOT NULL,
COMPANYID INTEGER DEFAULT 0 NOT NULL,
PERSONID INTEGER DEFAULT 0 NOT NULL,
...
);
ALTER TABLE COMPANYPERSON ADD CONSTRAINT K_COMPANYPERSON PRIMARY KEY (ID);
ALTER TABLE COMPANYPERSON ADD CONSTRAINT FK_COMPANYPERSON_PERSONID FOREIGN KEY (PERSONID) REFERENCES PERSON (ID);
ALTER TABLE COMPANYPERSON ADD CONSTRAINT FK_COMPANYPERSON_COMPANYID FOREIGN KEY (COMPANYID) REFERENCES COMPANY (ID);


CREATE TABLE COMPANY
(
ID INTEGER DEFAULT 0 NOT NULL,
NAME VARCHAR(200) DEFAULT '',
...
);
ALTER TABLE COMPANY ADD CONSTRAINT K_COMPANY PRIMARY KEY (ID);
CREATE UNIQUE INDEX I_COMPANY_NAME ON COMPANY COMPUTED BY (UPPER(NAME));