Re: Stored Procedures

P Watson Computer A (compans@cix.compulink.co.uk) 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 : 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. I have, with great success. The biggest benefit is the reduction of network traffic by encapsulating multiple SQL statements and the associated business logic into one stored procedure. A second benefit is that change control becomes much less a nightmare because you have only to revise one instance of code on the server rather than distributing changes to all the clients. A third benefit is the security you can impose on the database by using only stored procedures for end-user access to the database. : 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. This is no more a problem than maintaining configuration control over 4GL or any other client tool. I keep all stored procedures under SCCS control and use a Makefile to maintain them on the server. (I use a directory of zero-length files as the Makefile targets.) I'll supply a sample Makefile upon request. : 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. It is true there are some features of SPL conspicuous by their absence. But, where there's a will there's a way. You still have cursor control, conditional logic, transaction management, and, when used creatively, excellent error handling. : 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. Assuming you're running under DSA 7.1 performance should not be an issue. When a stored procedure is executed the first time, it is cached in shared memory for future access by all other users. Procedures are maintained in shared memory on an LRU basis. : 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. I don't have the 7.1 documentation in front of me, but I know the documentation for 5.0 was fairly complete. I have to believe it is no less complete in the 7.1 docs. : 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. Keep in mind that stored procedures are optimized when they are created and re-optimized every time UPDATE STATISTICS is run or when any object on which the SPL is dependent is changed. The optimized query plan is stored in the table sysprocplan. By maintaining the query plan in shared memory, there is very little overhead in executing them. The cost of opening an SPL cursor will be no more than that of opening a 4GL cursor. : Informix have said 'they are worthwhile if they contain more than 4 sql : statements'. It all depends on the statements and whether you are transporting data back and forth across the network. In a client/server environment, I would suggest that they are worthwhile if they contain more than one SQL statement. : 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. At least they are _less_ efficient. : ONLINE v6 and earlier - Stored Procedures consume memory proportional to : the number used times the number of users using them. True. : ONLINE v7 - Stored procedures consume memory proportional to the number : used. (Unfortunately other constraints stop us from using v7) that is...the number used by all users. In 5.0 and earlier, one copy of the procedure is read from disk for each user executing it. In 6.0 and later, one copy of the procedure is read from disk only the first time it is executed by any user. : 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. Correction. They are parsed and optimized when they are created and when UPDATE STATISTICS is run on them. The optimized query plan is stored in the database. The only time they are re-optimized at runtime is if any object in the dependency tree for the procedure has changed (i.e. ALTER TABLE, CREATE INDEX,....) since the last time the procedure was optimized. Infrequently used procedures will be purged from memory on an LRU basis. : Excessive Nesting of stored procedure code may have adverse performance : implications as will a large number of system calls made from procedures. True, but much less so with DSA than with O.L. 5.0. : 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. With careful design, SPL can be made to be useful for most situations. I think the benefits cited above outweigh the occasional procedure that will be less efficien