Re: Stored Procedures

Kerry Sainsbury (kerry@kcbbs.gen.nz) wrote: : compans@cix.compulink.co.uk ("P Watson Computer A") wrote: : > How many people out there are using stored procedures to do any : > significant amount of work? We are producing a Client Server version of : > ... : You don't have to use stored procedures. A technique I've used quite : successfully is to split your 4GL program into two parts: A data entry : portion, and a database update program, then at the end of your data entry : program you execute a stored procedure which runs the backend program on : the server. : The stored procedure consists of: : SYSTEM "fglgo backend.4gi" : ^------ or whatever the correct SPL command is, RUN ? EXECUTE ? SYSTEM is correct. The only problem I see with this is you have the overhead of firing up the SPL (as well as the 4GL), which can be costly. Also, as pointed out later in the mail, communication with front-end and back-end could get tricky. So I would use this method only if: 1) You have a lot of legacy 4GL code you would rather not trouble to convert to SPLs. or B) You need functionality provided in 4GL not available in SPL (subscripting strings with variables, to pick on my pet peeve) Informal timing tests by MPIC David Berg suggests that SPLs can give a performance benefit over client-level database calls if it executes 3 or more database statements (SELECT, FOREACH, INSERT, etc). : This means you don't have any network traffic because the back-end is : being run on the server, not the client. : Of course you need to devise come sort of communication method between the : front-end and back-end (so that the back end know what to do, and so the : back-end can tell the front-end when things go horribly wrong) : If this isn't clear, ask me to clarify (but don't expect a reply for a : month or so - I'm off on holiday^H^H^H^H^H^H^conference. : ------------------------------------,------------------------------------------ : Kerry Sainsbury, kerry@kcbbs.gen.nz | THE INFORMIX FAQ v2.2 Mar 95 : Quanta Systems, Auckland | kcbbs.gen.nz:/informix/informix.[faq|apx] : New Zealand. Work: +64 9 377-4473 | mathcs.emory.edu:/pub/informix/faq/ " " : Home: +64 9 279-3571 | http://www.garpac.com/informix.html -- ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Senior Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer.