RE: "lazy"stored procedures

OK, but the tables do not change so much so often.=20 Besides, Informix says that the procedure plan has a "dependencies list" = that tells the procedure when it needs to self-reoptimize itself. (what = does not seem to be doing well) Besides, I have run the query from dbaccess with set explain on, and it = does use the index. The index is always available. At some moment, it seems, the procedure " decides" not to use the index. = Or perhaps the engine itself takes a looong time to locate the = procedure???=20 Should I run update statistics for procedure ... every three hours???? = It does not seem very reasonable. Oscar -----Mensaje original----- De: Denham.M@amstr.com [SMTP:Denham.M@amstr.com] Enviado el: Wednesday, May 28, 1997 10:00 PM Para: ogolde@impsat1.com.ar CC: informix-list@rmy.emory.edu Asunto: "lazy"stored procedures Oscar you have found the answer to the problem yourself, run update statistics = for the procedure. UPDATE STATISTICS FOR PROCEDURE <procname> You need to do this because the procedure is pre-optimised when it is = created. As the tables that it accessed change, so the optimisation chosen for the = procedure needs to be changed too, thus the need to reoptimise. Run the statement as often as you feel necessary/can afford to. Mark MarkDenham@aol.com (home) Denham.M@amstr.com (work) --------------------------( Forwarded letter 1 follows = )--------------------- Date: Thursday, 29 May 1997 8:06am Date: Thu, 29 May 1997 10:47:33 -0300 To: informix-list@rmy.emory.edu From: Oscar Goldes <ogolde@impsat1.com.ar> Subject: "lazy"stored procedures Sender: informix-list-owner@rmy.emory.edu Reply-To: ogolde@impsat1.com.ar X-Informix-List-Id: <list.14693> Hello, The performance of several stored procedures degrades sharply with time. That is, I have procedures that do very simple queries over big tables (200000 rows) that return immediatly when executed short time after = being created. However, some time (hours) later, the procedures seem to "slow down", taking a lot of time to execute (say, 45 seconds for a simple select!). The same query run directly from dbaccess executes immediatly. The table has (and has always had) the appropiate index, and no massive data loads have been performed. Data is rather static. Dropping and recreating the procedure, or simply running update = statistics for procedure xxxx solves the problem... for some hours. What should I look for? I emphasize that I have not added, dropped or recreated the index, nor altered any significative ammount of data in = the table. Engine is Online 7.2 Thanks in advance. -- Oscar Goldes -- Orden Argentina SA