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?
