Hi,

> Did anyone had this problem, I'm trying to do a select with a left join on

a

> view with Firebird, also tested it on Interbase here is the case:
> First select, contacts_view is a view with 3 tables left joined : city,
> province and country.

I try to reproduce your situation and don't discover such a difference
between Interbase and Firebird. I run the same queries on Interbase 6.0.1.6
/ 6.5 & Firebird 1.0-821 / 1.5A4. Between all the engines the lowest was
10:35 sec. and the highest 11:48 sec.

Are you sure that you had the right gds32.dll on both tests ?
If yes could you give all necessary metadata to exactly reproduce your case.

Regards,
Arno

> select * from rooms r
> left join contacts_view cv on cv.c_contact_code = r.owner ^
> interbase : time 00:00:00:0071
> PLAN JOIN (R NATURAL,JOIN (JOIN (JOIN (CV C NATURAL,CV CI INDEX
> (CITY_CITY_ID)),CV P INDEX (PRO_PROVINCE)),CV CO INDEX (COUNTRY_COUNTRY)))
> firebird : time 00:00:12:0488
> PLAN JOIN (R NATURAL,JOIN (JOIN (JOIN (CV C NATURAL,CV CI INDEX
> (CITY_CITY_ID)),CV P INDEX (PRO_PROVINCE)),CV CO INDEX (COUNTRY_COUNTRY)))
>
> This one works great on both server:
> select * from rooms r
> left join contacts c on c.contact_code = r.owner
> left join city ci on ci.city_id = c.city_id
> left join provinces p on p.province = ci.province
> left join country co on co.country = c.country ^
>
>
> select * from rooms r
> left join contacts_view cv on cv.c_contact_code = r.owner
> order by r.room_number^
>
>
> Interbase : .0080 SEC
> PLAN SORT (JOIN (R NATURAL,JOIN (JOIN (JOIN (CV C NATURAL,CV CI INDEX
> (CITY_CITY_ID)),CV P INDEX (PRO_PROVINCE)),CV CO INDEX

(COUNTRY_COUNTRY))))

>
> Firebird TIME : 12 SEC
> PLAN SORT (JOIN (R NATURAL,JOIN (JOIN (JOIN (CV C NATURAL,CV CI INDEX
> (CITY_CITY_ID)),CV P INDEX (PRO_PROVINCE)),CV CO INDEX

(COUNTRY_COUNTRY))))

>
>
> --Samy