Hans wrote:

> select distinct o.mud_name oil_co,
> s.lsd,
> s.date_started,
> m.mud_name
> from sites s
> join tickets t on (t.soldtosite_uniquenum = s.site_uniquenum
> or t.shiptosite_uniquenum = s.site_uniquenum )
> and t.ref_ticket_uniquenum is null
> join mudcompanies o on o.mud_uniquenum = s.cust_uniquenum
> join warehouses w on w.house_uniquenum = s.house_uniquenum
> and w.training <> 'T'
> join mudcompanies m on m.mud_uniquenum = t.mud_uniquenum
> where s.wellcompleted <> 'T' and current_date - s.date_started <= 120
> order by o.mud_name, s.lsd, m.mud_name
>
> FB1.5
>
> PLAN SORT (SORT (JOIN (M NATURAL,T INDEX
> (TICKETS_REF_TICKET_NO,TICKETS_MUD_UNIQUENUM),S INDEX
> (RDB$PRIMARY12,RDB$PRIMARY12),W INDEX (RDB$PRIMARY21),O INDEX
> (RDB$PRIMARY5))))
>
> FB2.0
>
> PLAN SORT (SORT (JOIN (JOIN (W NATURAL, S INDEX (SITES_HOUSE_LSD_CUST), O
> INDEX (RDB$PRIMARY5)), JOIN (T INDEX (TICKETS_REF_TICKET_NO), M INDEX
> (RDB$PRIMARY5)))))
>
> FB1.5: runtime 29 Secs ODS 10.1
> FB2.0: runtime 240 Secs ODS 11.0

This means that you could try to eliminate the SITES_HOUSE_LSD_CUST to
tell Firebird 2.0 to choose another plan, e.g. by doing

join warehouses w on w.house_uniquenum = s.house_uniquenum+0
and w.training <> 'T'

(it will get you closer to finding the 'right' plan, but I don't know
whether this initial attempt will get a better or worse plan initially.

> select distinct o.mud_name oil_co,
> s.lsd,
> s.date_started,
> m.mud_name
> from sites s
> join tickets t on (t.soldtosite_uniquenum = s.site_uniquenum
> or t.shiptosite_uniquenum = s.site_uniquenum )
> and t.ref_ticket_uniquenum is null
> left outer
> join mudcompanies o on o.mud_uniquenum = s.cust_uniquenum
> join warehouses w on w.house_uniquenum = s.house_uniquenum
> and w.training <> 'T'
> left outer
> join mudcompanies m on m.mud_uniquenum = t.mud_uniquenum
> where s.wellcompleted <> 'T' and current_date - s.date_started <= 120
> order by o.mud_name, s.lsd, m.mud_name
>
> FB1.5
>
> PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (T INDEX (TICKETS_REF_TICKET_NO),S
> INDEX (RDB$PRIMARY12,RDB$PRIMARY12)),O INDEX (RDB$PRIMARY5)),W INDEX
> (RDB$PRIMARY21)),M INDEX (RDB$PRIMARY5))))
>
> FB2.0
>
> PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (S NATURAL, T INDEX
> (TICKETS_REF_TICKET_NO)), O INDEX (RDB$PRIMARY5)), W INDEX (RDB$PRIMARY21)),
> M INDEX (RDB$PRIMARY5))))
>
> FB1.5: runtime 3 Secs
> FB2.0: runtime 29 Secs

Hmm, I doubt I can improve this one at all. You could do some changes:

move 'and t.ref_ticket_uniquenum is null' from the JOIN clause to the
WHERE clause (I'd say that's where it belongs), do the same with 'and
w.training <> 'T'' and move
'join warehouses w on w.house_uniquenum = s.house_uniquenum
and w.training <> 'T''
to before any of the left outer joins.

Though I am surprised by this difference. Are statistics up-to-date and
the data identical in the two databases? If they are, I hope Arno or
Dmitry will be able to explain this difference, at first glance it
doesn't look like an improvement or bug fix (though it might be, I don't
know the internals of Firebird).

HTH,
Set