Re: stored procs slower than 4GL?

Joe Lumbley (jlumbley@netcom.com) wrote: : One of our subsidiaries has some developers who are swearing that they are : getting faster results using 4GL than they are with stored procedures. The : stored procs are returning massive amounts of data with each fetch, but : so is the 4GL. Does anyone know of any structural reasons or limitations : that would cause the stored procs to be slower? : From a theoretical standpoint, the stored procs are avoiding a lot of : pipe traffic and parsing time. I can't see how equivalent 4GL (compiled) : could be faster than the stored procs. Am I missing something? : Joe : -- : --------------------------------------------------------------------------- : Joe Lumbley(jlumbley@netcom.com) Dallas, Texas : ---------------------------------------------------------------------------- The productivity of SP depends on the number of SQL commands inside SP. Executing SP has some overhead - finding SP code and plan for particular SP (it is necessery to look for sysprocedures, sysprocbody, sysprocplan and also for sysprocauth tables - in case that SP was not called before and is not in memory of sqlturbo process). There is important to have three and more SQL commands in the SP body to overcome this overhead. The more work inside SP, the more speed. Another thing - optimization of SP. Adding new index doesn`t apply on SP plan, because sysprocplan is updated only after UPDATE STATISTICS command. Direct SQL command is optimized during execution, stored procedure not. Fragmentation of SP tables: If you have many SP`s, I think it is important to increase NEXT SIZE parameter before you create SP`s - especially for sysprocbody and sysprocplan tables - to avoid fragmentation of this tables. UPDATE STATISTICS has bug in all versions of INFORMIX ONLINE I have worked on:versions 5.0x and 6.0x - sometimes repeating delete and insert number of rows into table and running UPDATE STATISTICS does strange thing: column "npused" in systables which is one of columns the optimizer uses for its work can have values less then zero! Optimizer is confused by this and it is necessary to update this column directly from dbaccess (as user informix) with correct value acquired from tbcheck -pT table (count the right number of used pages). Sometimes it may happen for sysprocbody and sysprocplan (also for other tables) and this could be another reason for slow SP executing. We have generator for creating SP`s and if we want to change SP`s, we delete procs and create new. And npused is < 0 after UPDATE STATISTICS! Mirek -- ================================= Miroslav Lorenc Database Administrator Software Applications and Systems Internet: Miroslav.Lorenc@sasprg.cz Tiskarska 10/257 108 28 Prague 10 - Malesice Tel: +42 2 705134 Czech Republic Fax: +42 2 705128