Re: Newbie question: re stored procedures in Online 5.0

On Oct 11, 3:17pm, Rang2 wrote: > Subject: Newbie question: re stored procedures in Online 5.0 > Hi, > > I am new to Informix so please forgive me if this is a very stupid question - I > was told by a consultant to my company that it is preferrable not to use stored > procedures if better performance is required - i.e. ESQL-C code without stored > procedures would perform better than code which uses stored procedures. I > thought that stored procedures were pre-compiled and stored in the database and > would improve performance - Can anyone explain ? Thanks in advance. > > Ranga. > > P.S. (I am using Informix Online 5.0) > >-- End of excerpt from Rang2 Well it depends!! Helpfull, Yes! SP's are interpreted and they also have to be loaded! This makes them slower in a direct comparison with an ESQL/C program running on the same machine. Especially as in 5.0 you can only cache 15 at a time, a restriction that disappears in 6.0+. If the SP though is going to take a lot of work that would require communications between the engine and ESQL/C program they can be faster. So the rule on a single machine is that they generally are slower but may be faster if they do a lot of complicated sql code. Across a network the situation changes. As the SP can significantly reduce network traffic by doing all the work on the Informix server it can be a lot faster than an ESQL/c program as newtwork traffic is so expensive. Then you get into three tiered archictectures and the fun really begins! Of course SP's have advantages distinct from performance issues. They provide considerable flexibility to a design. For example using individual SP's to contain all the code for each transaction in an application allows you to reuse those transactions in other applications without bothering with library code. The SP's act as your library. It also allows you to make changes to your physical design without necessarily needing to recompile and re-release your applications. A more extensive language in Informix's SP's would also make using these benefits easier. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!