RE: Update Statistics high vs medium/low

We are running on our OLTP instances: MAX_PDQPRIORITY=0 OPTCOMPIND=0 PSORT_NPROD= cpu - 1 PSORT_DBTEMP="fast hard drive - EMC disk array in our case" OPTIMIZATION=low and a temp db space. So far no complaints for the most part. We do get a few slow queries, but that is an index construction issue. and we do update statistics high on all out tables, because of the index issue. Wayne E. Martin Informix Database Administrator Kmart Corp. -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: Monday, June 12, 2000 2:16 PM To: informix-list@iiug.org Subject: Re: Update Statistics high vs medium/low Tony Woods wrote: > > Hello gurus, > > Is it by any chance possible that executing update statistics high will slow > down the queries and executing update statistics medium/low will improve the > performance. It CAN happen, especially depending on how one is evaluating faster -vs- slower. It can certainly change the query path and the path under HIGH may involve sorting and unexpected indexes that can cause the return of the first rows more slowly than another query path might. If you want the engine to favor the return speed of the first rows rather than the return speed of the entire select set you can SET OPTIMIZATION FIRST_ROWS. Even beyond that, it is possible under specific circumstances for the optimizer to make a 'better' decision using MEDIUM stats than HIGH. However, this is rare and should be investigated further as setting the optimizer goal as above, using directives, or changing OPTCOMPIND may be a more consistent solution. Changing stats to MEDIUM from HIGH may improve some queries but hurt others. Art S. Kagel