Appropriate Use of Stored Procedures
Hello All I can see the value of using SPs for allowing procedural logic to be performed very close to data stored in an Ifmx database. I have heard that as they are pre-compiled, they are pretty quick too & can be quicker than running straightforward SQL statements. But where does one draw the line - for e.g. would an SP with functionality to insert a row into a table execute quicker / more efficiently than simply inserting the row using an insert statement? ditto for select, delete, update etc etc When is it more appropriate to use SPs and when more appropriate to expose the SQL in it's raw form? thanks for any help
We primarily use SPs for updates to multiple tables. Informix hosts our consumer/product database and our programs usually control multiple INSERTs and SELECTs. The SPs insure that all the appropriate tables are updated with consistency throughout the database. Also, if there needs to be a change in the way something works, I can make changes in one place and not have to pull all the Java apps apart and change the code. -CZ Richard Adams <radams@cromwellmedia.co.uk> wrote in message news:3804621e.0@london.netkonect.net... > Hello All > > I can see the value of using SPs for allowing procedural logic to be > performed very close to data stored in an Ifmx database. > > I have heard that as they are pre-compiled, they are pretty quick too & can > be quicker than running straightforward SQL statements. > > But where does one draw the line - for e.g. would an SP with functionality > to insert a row into a table execute quicker / more efficiently than simply > inserting the row using an insert statement? > > ditto for select, delete, update etc etc > > When is it more appropriate to use SPs and when more appropriate to expose > the SQL in it's raw form? > > thanks for any help > >
We do a lot of things with SPs, more than we should. If you want to issue a set of SQLs, it is more efficient to put it in an SP. The SP is pre-optimised, and there is no overhead of sending every statement to the server. On the other hand, when you start adding logic to the SP (If, While, and so on), things start getting out of hand. SPL is a _very_ slow at execution and _very_ limited as a language. The thing to avoid at all cost is SPs calling other SPs. Whatever mechanism is used to pass arguments and return values is a disaster. -- Bashar Chalabi CTL, London