RE: Stored Procedures

Dave Cook writes: >Do you have any hints/tips/advice on Stored Procedures called from Informix >4gl? >I have worked out how to prepare and execute them but I am now trying to >assess >what impact they will have on my application. >How does the performance of SP's compare to using Views? Never compared them with views, but comparing them against 4GL/ESQL-C written to do the same task, performance was up to 50% slower. (V5.02+). For RDS applications the performance degradiation is only 5-20% from my testing [not extensive]. >What are the known problems? Don't make them too large, they will become very inefficient. >Is there a limit to the number of SP's I can hold in the database? Not as far as I know. There is a practical limit to how many you can have in use though. I believe this is around 50. It depends on the size of the procedures. >If I have a large number of SP's what sort of impact will this have? Make your application *SLOW*. >Initially I will be using version 5.x of the Standard Engine but I will also >be >using them under On-line in the near future. Forget standard engine for SP [my opinion]. Having said all this I still recommend the use of SP, especially in an environment where the DB maybe changed frequently. You will need to modify the application less often. Small procedures execute quite quickly. You gain more advantages in a client/server environment due to a reduction in network traffic. (Do your selections and filtering in SP on server). Version 7 caches SPs in memory, so you get quite good performance there I understand [never tried it]. Mark Denham BBC London, UK Mark.Denham@bbc.co.uk