Re: Stored Procedures

On Jul 10, 6:19pm, P Watson Computer A wrote: } Subject: Stored Procedures } } 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. Yes, but there are a number of case tools around that could help you manage this. For example ERwin with PCS version control. } > 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. Yes. You would need to provide an application layer on the server above the database as SP's are not up to doing major application processing. } 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. As per 2, where the SP's contain SQL that is used repetitively they perform better than 4gl. Where they are called infrequently and/or contain a lot of language processing they are significantly slower as they have a loading and interpreting overhead. } 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. SPL is just a small subset of 4gl and is a very simple language } } 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. The open and fetch should perform slightly faster than 4GL as you don't have the pipe traffic. You lose if you have to read the SP into memory to start with as there is a setup time to check whether it needs to be re-optimised. This should be less than the time to prepare the equivalent set of SQL statements in 4GL. You gain significantly if the SP is already in memory. This is obvious with single or simple select statements as the effort to read the SP into memory from disk, check it and then run it will almost always exceed a 4gl direct access which involves no disk access other than the table. } } Informix have said 'they are worthwhile if they contain more than 4 sql } statements'. That's reasonable though you can go lower if you use it a lot. } 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. That I think should be V5 and early. V6 and above moved to sharing SP's between users. } } 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. Yes. } > 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. } Yes We did it for a small system with a server application layer for the stuff the SPL couldn't handle. It works but could be faster. Currently running under V5 it would do much better under V7. The ability to link in your own code and have a compile capability would have been nice maybe in V8/9? 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!