Re: Slowdown executing SPLs in SELECT list

it would be nice to know how the sqexplain.out looks like eq: set explain on; UPDATE STATISTICS FOR PROCEDURE SPL1; if you see seq scans then you are most likely in deep ..... then check the indexes on cis and make sure your datatypes match with the ones in the spl. see you Superboer. Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<T9Ggd.6466$kM.1999@newsread3.news.pas.earthlink.net>... > 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.