"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