Stored Procedures

The answer to your questions are as follows : - 1. What's the use of update statistics for procedure? When you perform an update statistics on a stored procedure the procedure is re-optimised and hence a new query path may be produced which is now more relevant to the changes made within your database. 2. How does Informix handle stored procedures if I don't make the update? They will still work, however as with any query they may well choose an incorrect query path resulting perhaps in sequential scans. 3. Do I get bad performance? Probably if not definitely. I hope this helps to answer your questions. Regards Sean Monika Rabold S.u.S.E. Linux 5.3 wrote in message <7aeh5u$na4$1@news.xmission.com>... > >Hi, >what's the use of update statistics for procedure? >How does Informix handle stored procedures if I don't make the update? >Do I get bad performance? > >Thanks in advance, > >MoRa >

Hi, what's the use of update statistics for procedure? How does Informix handle stored procedures if I don't make the update? Do I get bad performance? Thanks in advance, MoRa

Monika Rabold S.u.S.E. Linux 5.3 wrote: > > Hi, > what's the use of update statistics for procedure? It creates a new query plan for the procedure so that the first user of the procedure will not have to wait for the automatic re-optimization. > How does Informix handle stored procedures if I don't make the update? The first user of a stored procedure not in cache (or one whose cache entry has become invalid due to a change in a component table) will force a reoptimization. However, there are some circumstances when the engine cannot detect that it should invalidate the cache entry in which case executing the procedure will result in an error and you will have to update statistics for the procedure. > Do I get bad performance? Only for that first user (unless you are getting errors of course). You can check the status of the Stored Procedure Cache with: onstat -g prc Art S. Kagel

Update Statistics for a stored procedure optimizes the procedure plan inthe sysprocplan system table. "This optimization will also occur if any of the objects that the procedure references have changed." --from the answers online cd, SQL syntax. I interpret that to mean if you update stats for a table that a procedure references (or make any other changes to the "objects") and do not update stats on the procedure itself, the first time that procedure is invoked, their will be a performance hit. To avoid that type of optimization, run the update stats for the procedure. So the short answer to your question (as far as I can tell) is yes, you do get bad performance, but only once, after a change to any "objects" that a procedure references. Does anyone know exactly what these "objects" are that a stored procedure references? I assume them to be anything except the addition and deletion of data in a referenced table.....but who knows? -- Regards, Robert Monika Rabold S.u.S.E. Linux 5.3 <mora@swh600.langen.bull.de> wrote in article <7aeh5u$na4$1@news.xmission.com>... > > Hi, > what's the use of update statistics for procedure? > How does Informix handle stored procedures if I don't make the update? > Do I get bad performance? > > Thanks in advance, > > MoRa > >