Re: Stored Procedures in Client/Server Env.

govardha@plains.nodak.edu (Jogendra Govardhanagiri) wrote: > I have couple of questions on using SPs in the client/server environment. > I like to get some info. on this. >1) Is there any limit on the number of clients calling a single > procedure at the same time? There is no limit although using stored procedures in version 5 will have impact on your application's memory usage. In version 5 Informix maintains separate stored procedure cache for each user. Version 7 has a one common cache shared by all the users. >2) How the locking mechanism works for the system tables which have these > stored procedures? The system tables are locked in exclusive mode at the time the stored procedure is compiled. This occurs when: - stored procedure is created - when update statistics run against any of the tables used by stored procedure - at the time of execution, if the structure of the tables or indexes referenced has changed - In early version of 5.x stored procedures are recompiled every time they are executed if: - they create explicit temporary tables - stored procedure does not contain SQL which performs database access. For example stored procedure which used to return current server time to the client. The above two bugs have been fixed in later versions. >3) Can we expect performance improvement using SPs in the large client/server > environment? As you probably know stored procedures reduce network traffic, the biggest impact is when you have transactions made up of multiple SQL statements and the intermediate results have to be send between the client and the server for each SQL statement within the transaction. Stored procedures also save you from parsing and recompiling the SQL every time it is executed.. There are some overheads of executing stored procedures: - At the time sp is executed it has to check if any of the database objects it references have not changed. - Executable of the sp has to be fetched from system tables if it is not in the procedure cache. >-Jo If you like more information I wrote a paper on using informix stored procedures which describes in more detail the above issues and others that should be considered before using Informix stored procedures on a large scale. If any body likes a copy please send email to : rafal@dataspace.com.au ----------------------------------------------------------------------------------------------------------------- Rafal Czerniawski Dataspace Consulting Pty Ltd