Re: Executing stored procedures - performance problem

Oleg V. Besedin wrote: > > recently we found a kind of interesting behaviour of our Informix system. > Stored procedures which normally work within 2 - 3 minutes started > to take 8 - 9 hours to complete (180 times performance drop - ha-ha). > <SNIP> > The only way we have found to solve this situation is to DROP DISTRIBUTION, > UPDATE STATISTICS MEDIUM, DROP PROCEDURE, CREATE PROCEDURE, UPDATE STATISTICS> FOR PROCEDURE... Sometimes it works, sometimes - not, but in any case > the effect is seems to be unstable. (Plus it takes forever > to update statistics). You should be running UPDATE STATISTICS for the whole database at regular intervals. If you are running it for selected tables only, then check that all the tables used in your query (or what ever your SP is doing) are being updated. If UPDATE STATISTICS takes a long time then run it over night. Hope that helps, -- Mark. +-------------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | | |mailto:marks@west.co.za +------------------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work... force it! | |Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! | |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+------------------------------------------------+