Update Statistics for Stored Procedures
Hi All I have around 80 User defined Stored procedures in our informix database (9.21 on Solaris). All SPL's are very simple ones having 1 or 2 sqls, few of them have some calculations, no recursive calls , not having nested procs. At the moment no Update stats running for the SPLs . Do i need to run Update stats even for SPLs also? If so when i write new SPLs when i should run update stats ? Is there any performance issues with Simple SPLs if i don't run Update stats? Do i have to run Individually or is there any command which will run for all SPLs. Please advise me the Guidelines for the above as i am not a Informix Person. Your suggestions are highly appreciated. Thanks in Advance Kalpana Pai
You should run Update Stats on the stored procedures as often as you run update statistics on the underlying tables, in order that the execution plan for the queries reflects the most current statistical information. You also need to do it if you make any schema changes to the tables or their indexes, otherwise the queries will return an error. In my view people run Update Statistics far too often. It's only necessary if the data has changed in a profound enough way for the optimiser to change its mind as to the best way to run a query. A 10% increase in the number of rows is extremely unlikely to do this. For most sites I've been on, monthly or even quarterly would be often enough for all but the most volatile tables. Also check the guidelines in the manuals for when you should be running MEDIUM or HIGH. Andy kalpanapai@hotmail.com (KalpanaPai) wrote in message news:<8b77f6f5.0312081921.57cbc2a2@posting.google.com>... > Hi All > > I have around 80 User defined Stored procedures in our informix > database (9.21 on Solaris). > > All SPL's are very simple ones having 1 or 2 sqls, few of them have > some calculations, no recursive calls , not having nested procs. > > At the moment no Update stats running for the SPLs . > Do i need to run Update stats even for SPLs also? If so when i write > new SPLs when i should run update stats ? Is there any performance > issues with Simple SPLs if i don't run Update stats? > Do i have to run Individually or is there any command which will run > for all SPLs. > > Please advise me the Guidelines for the above as i am not a Informix > Person. > > Your suggestions are highly appreciated. > > Thanks in Advance > Kalpana Pai