How to speed up a query using function and indexin
Good Morning,
I need to speed up this query:
set isolation to dirty read;SELECT NVL(hr_dati1, '---'), NVL(cm_t_stoc, -1) as type,
LPAD(cm_mod,3, 0)||LPAD(cm_col,3, 0) as coord,
NVL(cm_segmento, 0) as seg, NVL(cm_lres, 0)
FROM informix.artind, informix.coordman
LEFT OUTER JOIN informix.hmdescr ON cm_t_stoc = hr_key_soc AND
hr_key_pref = 'MGZ' AND hr_key_suff = 'STOCC'
WHERE cm_magaz = '5' AND ad_code = '2065498' AND
((cm_code = '2065498') OR
((cm_code = ad_coor_r) AND ( SUBSTR(NVL(ad_coor_r, ' '), 1, 2) = 'MU' )
AND
( SUBSTR(NVL(cm_code, ' '), 1, 2) = 'MU' ) ))
ORDER BY type, seg, coord;
The above query was faster when I used as filter only
(cm_code = '2065498')
instead of
((cm_code = '2065498') OR
((cm_code = ad_coor_r) AND ( SUBSTR(NVL(ad_coor_r, ' '), 1, 2) = 'MU')
AND
( SUBSTR(NVL(cm_code, ' '), 1, 2) = 'MU' ) ))
but now a I need to add this new condition.
In the search for an optimization, and I believe that the slowness is due to
the substr I have created this function:
create function informix.substr2_ad_coor_r(coor_r char(8))
returning char(8) with (NOT VARIANT);return substr(coor_r, 1, 2);
end function;
and the these indices:
create index informix.artind_idx_sub_coor_r on informix.artind
( informix.substr2_ad_coor_r(ad_coor_r));
create index informix.coordman_idx_sub_codifa on informix.coordman
( informix.substr2_ad_coor_r(cm_codifa));
but I have not improved the speed enough, also the strange thing is that the
query seems faster if I use, in the filter condition, substr
eg.(SUBSTR(NVL(ad_coor_r, ' '), 1, 2) = 'MU') instead of substr2_ad_coor_r eg.
substr2_ad_coor_r(ad_coor_r) = 'MU'
Can you try to rewrite following line ( SUBSTR(NVL(cm_code, ' '), 1, 2) = 'MU' ) )) as cm_code[1,2]='MU'
Thank you for your answer,
I speeded up the query by splitting it into 2 using the union
set isolation to dirty read;SELECT NVL(hr_dati1, '---'), NVL(cm_t_stoc, -1) as type,
LPAD(cm_mod,3, 0)||LPAD(cm_col,3, 0) as coord,
NVL(cm_segmento, 0) as seg, NVL(cm_lres, 0)
FROM informix.artind, informix.coordman
LEFT OUTER JOIN informix.hmdescr ON cm_t_stoc = hr_key_soc AND
hr_key_pref = 'MGZ' AND hr_key_suff = 'STOCC'
WHERE cm_magaz = '5' AND ad_code = '2065498' AND
(cm_code = '2065498')
UNION
SELECT NVL(hr_dati1, '---'), NVL(cm_t_stoc, -1) as type,
LPAD(cm_mod,3, 0)||LPAD(cm_col,3, 0) as coord,
NVL(cm_segmento, 0) as seg, NVL(cm_lres, 0)
FROM informix.artind, informix.coordman
LEFT OUTER JOIN informix.hmdescr ON cm_t_stoc = hr_key_soc AND
hr_key_pref = 'MGZ' AND hr_key_suff = 'STOCC'
WHERE cm_magaz = '5' AND ad_code = '2065498' AND
((cm_code = ad_coor_r) AND ( SUBSTR(NVL(ad_coor_r, ' '), 1, 2) = 'MU' )
AND
( SUBSTR(NVL(cm_code, ' '), 1, 2) = 'MU' ) )
ORDER BY 1, 3, 2;