Slowdown executing SPLs in SELECT list
Dear colleagues,
I'm experiencing huge delays executing SPLs in SELECT list even when
SPLs are simple. For example,
SELECT f1, f2, f3, SPL1(f1,f2,f3) FROM my_table
<SPL>
CREATE PROCEDURE SPL1( f1 CHAR(3), f2 CHAR(2), f3 CHAR(1))
RETURNING CHAR(3);
DEFINE new_code CHAR(3) ;
select station into new_code from cis
where f1 = 'XXX' and country = f2 and f3 <> 'Y'
RETURN new_code ;
END PROCEDURE;
GRANT EXECUTE ON SPL1 TO public;
UPDATE STATISTICS FOR PROCEDURE SPL1;</SPL>
Without SPL in SELECT list, it returns 185k+ rows in several seconds.
With SPL added, it takes 6 times longer. If I'm adding second and
third SPLs, execution time raises geometrically. It doesnt really
matter whether SPL contain database SELECT inside or just does some
string or condition processing.
IDS version is 7.31UC6 on HP-UX 11.0, database statistics for
tables/procedures/database updated using dostats and IDS is restarted
then.
Is it a common behavior [of IDS 7.x] or it is possible to do something
with it ?
Thanks in advance and regards,
Sergei Turin
Sergei Turin wrote:
> Dear colleagues,
>
> I'm experiencing huge delays executing SPLs in SELECT list even when
> SPLs are simple. For example,
>
> SELECT f1, f2, f3, SPL1(f1,f2,f3) FROM my_table>
> <SPL>
> CREATE PROCEDURE SPL1( f1 CHAR(3), f2 CHAR(2), f3 CHAR(1))
> RETURNING CHAR(3);>
> DEFINE new_code CHAR(3);
>
> select station into new_code from cis
> where f1 = 'XXX' and country = f2 and f3 <> 'Y'
>
> RETURN new_code;
>
> END PROCEDURE;
Ouch! Why not:
SELECT m.f1, m.f2, m.f3, c.station
FROM my_table m., OUTER cis c
WHERE m.f1 = 'XXX' AND c.country = m.f2 AND m.f3 <> 'Y';
I'd be curious to know how that compares with the 'without SPL in the
SELECT list'. I'd expect it to be slower than simply selecting the
fields from my_table, but quicker than using SPL. If the rows for
which there is no value in the cis table do not matter, you can use an
inner join instead of an outer, which should speed things up.
> GRANT EXECUTE ON SPL1 TO public;
> UPDATE STATISTICS FOR PROCEDURE SPL1;> </SPL>
>
> Without SPL in SELECT list, it returns 185k+ rows in several seconds.
> With SPL added, it takes 6 times longer. If I'm adding second and
> third SPLs, execution time raises geometrically. It doesnt really
> matter whether SPL contain database SELECT inside or just does some
> string or condition processing.
>
> IDS version is 7.31UC6 on HP-UX 11.0, database statistics for
> tables/procedures/database updated using dostats and IDS is restarted
> then.
>
> Is it a common behavior [of IDS 7.x] or it is possible to do something
> with it ?
SPL will be slower than inline SQL, if only because the optimizer does
not poke around inside the SPL and simply invokes it once per row,
with the concomitant repeated execution of an independent SELECT
statement. That can't be good for performance!
Try replacing the SPL with something that just computes with the
parameters -- returns the 3 values concatenated, for example. That
too will be slower than simply selecting direct from my_table.
SPL procedures classically give performance benefits when they
contain multiple SQL statements, preferably without returning much
data to the application.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/