> select PROPERTIES.AGENT_REFERENCE,
> OWNERS.SURNAME as OWNER_SURNAME, OWNERS.INITIALS as
> OWNER_INITIALS, OWNERS.NAME as OWNER_NAME, OWNERS.COMPANY_NAME as
> OWNER_COMPANY_NAME,
> BUYERS.SURNAME as BUYER_SURNAME, BUYERS.INITIALS as
> BUYER_INITIALS, BUYERS.NAME as BUYER_NAME, BUYERS.COMPANY_NAME as
> BUYER_COMPANY_NAME,
> PROPERTIES.ADDRESS_ROAD,
> 'xxxxx' as OUR_REFERENCE /*Calculated via another process*/,
> INSPECTIONS.DATE_INSPECTED,
> COMPANIES.ELECTRICAL_INSPECTION_COMM as COMMISSION
> from INSPECTIONS, PROPERTIES,
> PROPERTY_OWNERS
> left join OWNERS on (OWNERS.ENTITY_ID = PROPERTY_OWNERS.

ENTITY_ID),

> PROPERTY_BUYERS
> left join BUYERS on (BUYERS.ENTITY_ID = PROPERTY_BUYERS.

ENTITY_ID),

> PROPERTY_AGENTS
> left join AGENTS on (AGENTS.ENTITY_ID = PROPERTY_AGENTS.

ENTITY_ID)

> left join COMPANIES on (COMPANIES.ID = AGENTS.COMPANY_ID)
> where INSPECTIONS.INSPECTION_ID = 23 and
> INSPECTIONS.SERVICE_TYPE = 1 and
> PROPERTIES.ID = INSPECTIONS.PROPERTY_ID and
> PROPERTY_OWNERS.PROPERTY_ID = PROPERTIES.ID and
> PROPERTY_BUYERS.PROPERTY_ID = PROPERTIES.ID and
> PROPERTY_AGENTS.PROPERTY_ID = PROPERTIES.ID

What a mess! Using SQL-89 with the addition of LEFT JOIN (SQL-92) like
this certainly confuses me. Rewrite to using only SQL-92 (i.e. don't
use any commas in the WHERE part, but stick to JOIN and LEFT JOIN).

> PLAN JOIN (MERGE (SORT (JOIN (JOIN (PROPERTY_AGENTS INDEX
> (PROPERTY_AGENTS_IDX1),JOIN (JOIN (JOIN (JOIN (AGENTS ENTITIES INDEX
> (RDB$PRIMARY1),AGENTS ENTITY_TYPES INDEX (RDB$PRIMARY2)),AGENTS

PERSONS

> INDEX (RDB$PRIMARY4)),AGENTS COMPANIES INDEX (RDB$PRIMARY5)),AGENTS
> ENTITY_ROLES INDEX (RDB$PRIMARY3))),COMPANIES INDEX
> (RDB$PRIMARY5))),SORT (JOIN (PROPERTY_BUYERS INDEX
> (PROPERTY_BUYERS_IDX1),JOIN (JOIN (JOIN (JOIN (BUYERS ENTITIES INDEX
> (RDB$PRIMARY1),BUYERS ENTITY_TYPES INDEX (RDB$PRIMARY2)),BUYERS

PERSONS

> INDEX (RDB$PRIMARY4)),BUYERS COMPANIES INDEX (RDB$PRIMARY5)),BUYERS
> ENTITY_ROLES INDEX (RDB$PRIMARY3)))),SORT (JOIN (PROPERTY_OWNERS
> NATURAL,JOIN (JOIN (JOIN (JOIN (OWNERS ENTITIES INDEX
> (RDB$PRIMARY1),OWNERS ENTITY_TYPES INDEX (RDB$PRIMARY2)),OWNERS

PERSONS

> INDEX (RDB$PRIMARY4)),OWNERS COMPANIES INDEX (RDB$PRIMARY5)),OWNERS
> ENTITY_ROLES INDEX (RDB$PRIMARY3))))),JOIN (INSPECTIONS INDEX
> (INSPECTIONS_IDX2),PROPERTIES INDEX (FK_PROPERTIES)))

I assume that any slowness comes from PROPERTY_OWNERS NATURAL, it may
be easier to tell once we see the fixed SQL and whether the plan is
still the same or not.

HTH,
Set