This has been fixed by a few rewrites of the query. Still not sure why the original caused so many issues, but since it was only on an older version of Firebird I'm not worried about it anymore.

--- In firebird-support@yahoogroups.com, "cantak3" wrote:
>
> I have a stored procedure that is extremely slow, but only in Firebird 2.0. In 2.5 it works fine. Here are the performance results. Note - I've removed other tables from the result that were exactly the same.
>
> Table IndexedFB20 IndexedFB25 NonIndexedFB20 NonIndexedFB25
> Employee 12181818 968 1350 1350
> Header 39744 56592 0 0
> Header_Hours 194 1274 17535744 0
>
> As you can see, 2.0 has FAR FAR more indexed and non indexed reads. Resulting time is 171ms on 2.5, and 25756ms on 2.0.
>
> I compared the plans for this using IBExpert and narrowed it to one single statement inside the stored procedure. This statement is the culprit. I took the plan from 2.5 and made it a part of the statement thereby enforcing my own plan. 2.5 works fine. 2.0 gives the error "index RDB$PRIMARY6 cannot be used in the specified plan". This index is specifically on the Employee table.
>
> Here is the SQL statement causing the issue from inside the procedure:
> select case when hh.h_department in ('T','R','O') then
> hh.h_virtual_available
> else hh.h_hours_available
> end - coalesce(sum(hh2.h_virtual_total), 0)
> from header h
> inner join employee e on (((:Department <> 'M') and (h.h_m_techid = e.e_employee_id)) or
> ((:Department <> 'F') and (h.h_f_techid = e.e_employee_id)) or
> ((:Department <> 'S') and (h.h_s_techid = e.e_employee_id)) or
> ((:Department <> 'G') and (h.h_g_techid = e.e_employee_id)) or
> ((:Department <> 'E') and (h.h_e_techid = e.e_employee_id)) or
> ((:Department <> 'I') and (h.h_i_techid = e.e_employee_id)) or
> ((:Department <> 'R') and (h.h_r_techid = e.e_employee_id)) or
> ((:Department <> 'T') and (h.h_t_techid = e.e_employee_id)))
> inner join header_hours hh on hh.h_header_id = h.h_header_id and
> e.e_department starting with hh.h_department and
> ((hh.h_hours_available > 0) or (hh.h_virtual_available > 0)) and
> ((hh.h_total_hours <> 0) or (hh.h_virtual_total <> 0)) and
> hh.h_department <> :Department
> left join department d on d.parentcode = hh.h_department
> left join header_hours hh2 on hh2.h_header_id = h.h_header_id and hh2.h_department = d.code and
> hh2.h_department <> d.parentcode and
> ((hh2.h_total_hours <> 0) or (hh.h_virtual_total <> 0))
> where cast(coalesce(h.h_startdate, h.h_arrivaldate, h.h_scheduled_arrival_date) as date) = :aDate + :counter and
> h.h_shopid = :ShopID and
> h.h_status in ('Converted to RO','In-Process','Waiting','Parts Ordered','Received','Partially Received') and
> e.e_department starting with :Department
> group by hh.h_hours_available,
> hh.h_virtual_available,
> case when hh.h_department in ('T','R','O') then
> hh.h_virtual_available
> else hh.h_hours_available
> end
> having hh.h_hours_available + hh.h_virtual_available - coalesce(sum(hh2.h_total_hours), 0) > 0
>
> Here is the plan generated by Firebird 2.0:
> PLAN SORT (JOIN (JOIN (JOIN (HH NATURAL, E INDEX (IDX_EMPLOYEE_DEPT), H INDEX (RDB$PRIMARY8)), D INDEX (DEPARTMENT_PARENTCODE_IDX1)), HH2 INDEX (PK_HEADER_HOURS)))
>
> Here is the plan generated by Firebird 2.5:
> PLAN SORT (JOIN (JOIN (JOIN (H INDEX (IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS), HH INDEX (PK_HEADER_HOURS), E INDEX (RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6)), D INDEX (DEPARTMENT_PARENTCODE_IDX1)), HH2 INDEX (PK_HEADER_HOURS)))
>
> When I specify this plan in Firebird 2.0 I get the error mentioned. One last (and very odd) note: If I remove just ONE of the employee join OR clauses, the new plan is generated by Firebird2.0 automatically, and works fine. I still can't specify it manually though! So if the employee join OR area goes from 8 items to 7, all is generated fine, but specifying the plan still fails.
>
> So, how can I specify the correct plan, or fix the query so the optimizer chooses the best plan?
>