Slow Query When Used in Stored Procedure
hi,
informix version : 11.10
os : solaris
i have this problem wherein a query against a particular table is slow when it
is run in a stored procedure.
however, when i try to run the query normally, it's now slow.
is there anyway that I can improve this?
this is how it looks like within the stored procedure.
when i do an explain plan, it seems to use the index properly when it is not
inside a stored procedure.
however, when it is in a stored procedure, it suddenly runs slow.
the table size is around 5 million records.
if i use the sp, it would take around 20 to 30 seconds.
if i use the sql directly, it would take less than a second.
is there any reason why my stored procedure is slow?
is it possibly not using the index?
let's say the name of the table is tbl_sample.
I have index on
(ind_first_nm, ind_last_nm)
// SP SNIPPET
FOREACH
SELECT cl.no, ind_first_nm, ind_middle_nm, ind_last_nm, ind_birth_dt, corp_nm
INTO v_no, v_ind_first_nm, v_ind_middle_nm,
v_ind_last_nm, v_ind_birth_dt, v_ind_corp_nm
FROM tbl_sample cl
WHERE cl.type = p_type
AND ind_last_nm LIKE UPPER(p_search_string)
AND ind_first_nm LIKE UPPER(p_first_name)
AND CAST(ind_birth_dt as DATE) = p_birthday
ORDER BY ind_last_nm, ind_first_nm
ENDFOREACH
//END OF SP SNIPPET
however, in the onstat logs, it suddenly appears this way and noticeably runs
longer than usual.
select cl.no, ind_first_nm, ind_middle_nm, ind_last_nm, ind_birth_dt, corp_nm
from tbl_sample as cl
where (and (and (= cl.type, p_type), (like ind_last_nm, (<procedure> upper,
p_search_string))), (like ind_first_nm, (<procedure> upper, p_first_name)))
order by ind_last_nm, ind_first_nm
here is the direct sql version
// BEGIN DIRECT SQL -- MUCH MUCH FASTER
SELECT cl.no, ind_first_nm, ind_middle_nm, ind_last_nm, ind_birth_dt, corp_nm
FROM tbl_sample cl
WHERE cl.type = p_type
AND ind_last_nm LIKE UPPER(p_search_string)
AND ind_first_nm LIKE UPPER(p_first_name)
AND CAST(ind_birth_dt as DATE) = p_birthday
ORDER BY ind_last_nm, ind_first_nm
// END DIRECT SQL
Thanks
here's the explain plan on the test table. the current row count is 14. Procedure: informix.fnc_get_sample Statement id: 0 Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 cl type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 1 14 00:00.00 3 type rows_sort est_rows rows_cons time ------------------------------------------------- sort 0 0 0 00:00.00 ---------- Procedure: informix.fnc_get_sample Statement id: 1 Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 cl type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 1 14 00:00.00 3 type rows_prod est_rows rows_cons time ------------------------------------------------- group 1 0 0 00:00.00
here's something I find odd. I think that the indexes are not used if there is upper on the right hand side of the where in the like statement. i'm not particularly sure but it seems that way...