How to improve the performance of update statistics

Dear all, IDS : 7.31.UC2 OS: Solaris 2.7 Is there a way to expedite the update statistics processing time?? Currently it took more than 2 hours to complete the entire database. Can I use PDQPRIORITY and PSORT_NPROCS parameter to improve the performance?? Pls advise. Thanks & regards, Miyaki __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com

advice 1: If you do not run the 'update statistics' that is the best performance you can get, at least for running update statistics. Do not repeatedly run the stats for every table. If a table is does not change very much it does not need stats run again. There is no reason to run stats every day unless you have an application where the data changes drastically every day and all indexes are dropped and created every day. Statistics and optimizer performance will not differ that much between a table that has 5 million rows in a certain distribution and 6 million rows with roughly the same distribution of data. So stop running update statistics. advice 2: If a table changes slowly, run the stats on that table at long intervals like once per quarter or year. advice 3: Run just a few tables at a time, not all tables at one time. advice 4: Run only the tables that make a difference to the applications going against the data. Example: If you have a big history table of old data, but it is not used very much in any application, then skip running statistics on it. advice 5: run only the low and/or medium, they are quickest and it is usually better than no update statistics run at all.

miyaki wrote: > IDS : 7.31.UC2 > OS: Solaris 2.7 > > Is there a way to expedite the update statistics > processing time?? Currently it took more than 2 hours > to complete the entire database. Can I use PDQPRIORITY > and PSORT_NPROCS parameter to improve the > performance?? Pls advise. And following on from Steven Hauser's list of 5 pieces of advice: Advice 6: consider an upgrade to the latest version of 7.31. There are several reasons to do so, but unless I'm misremembering horribly (which actually is possible in this case), there have been some improvements made to the performance of UPDATE STATISTICS since the version you have. Advice 7: look at Art Kagel's utility - it automates the generation of UPDATE STATISTICS pretty well. It's available in the IIUG SoftwareArchive. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/