Stored Procedures

How many people out there are using stored procedures to do any significant amount of work? We are producing a Client Server version of an existing 4gl product and response times are an issue. We are trying to determine how much stored procedures can give us here. We would like to push most of our code onto the server this way and only keep the presentation logic on the client. Alternately we may find that only the transactions and certain high use selects are suitable for this treatment - which still leaves most of the code on the client. We were toying with the idea of writing RPCs in 4gl (mostly anyway) but were hoping Stored Procedures would give us what we needed with less effort. If any of you have used them in anger I'd appreciate a response. Here are some initial thoughts: 1. At first glance it looks like version and release management could be more complicated than with 'traditional' 4gl because of the way they have to be shipped as sql and 'pushed' into the schema. This wouldnt be a problem if we were developing software for in-house use but we have to keep track of about 20 customer sites. Not a big number by a lot of peoples standards but enough not to want an extra level of complexity. 2. If a large proportion of work is to be carried out as stored procedures the language looks a bit weak. I say that as we are hoping to move business logic onto the server this way. At the moment we use most of the functionality of 4gl. SPL only seems to deal with SQL and a bit of logic flow. There does not seem to be any way of linking in C or 4gl functions to enrich the language. 3. I cant make my mind up on how to expect them to perform with 100 users. We have 250K lines of code. If much of that becomes Stored Procedures thats a lot of code for the relational tables to handle. I have talked to Informix support about it but they cant give us enough help to let us feel our way forward. 4. The documentation is close to non existant (some notes in "Informix Guide To SQL: Reference" and "Using Triggers"). Informix have more helpfull advice to give on Unix Termcap than their own SPL language. Support tell me that that doesnt really matter as I can phone them for help! Either SPL is very simple or we are are going to find that a problem. 5. Preliminary measures suggest that SPLs are fast when used repeatedly on a multi table statement. There is a non trivial overhead with opening the cursor in the first place and in the first fetch or two. Opening a cursor based on an SPL and then fetching just one row is much slower than issuing an inline SQL statement let alone a statement prepared in the program. For the two table select it is out performing the prepared statement. For the single table select it isnt. Informix have said 'they are worthwhile if they contain more than 4 sql statements'. 6. Stored procedures can be memory hungry. I got a helpful reply from Informix on this subject which boils down to: SE - Stored Procedures are inefficient as they have to be read from disk causing serious I/O problems. ONLINE v6 and earlier - Stored Procedures consume memory proportional to the number used times the number of users using them. ONLINE v7 - Stored procedures consume memory proportional to the number used. (Unfortunately other constraints stop us from using v7) Stored procedures exist for the whole of the user session and are parsed the first time they are called. This implies that stored procedures must be frequently used to be of benefit, infrequently used procedures will persist ... using memory whilst they are not in use. Excessive Nesting of stored procedure code may have adverse performance implications as will a large number of system calls made from procedures. So it would seem that the best use of Stored Procedures would be for frequently used SQL and logic requests that will be a large no. of users frequently during a single session. Hopefully I will find time to continue the tests and make them a bit more serious. If any of you have moved a significant amount of code to SPL please tell - a success story would cheer us all up.