Update statistics question

Hi All, I got a user complaining that according to her, 2 months back, the report processing takes only 15-30 mins. Now, she's telling me that 2 hours, the report still not finished. I did check the program and it showed that the last update stats done was May 2010, I did perform update statistics medium on all the tables i found in the select statement. Here are some samples how the program is organized 1. create temp table (this is the storage after a big select statement is done). 4 or 5 columns are indexed. 2. select statement (10 tables with around 20 where clause). (big tables of around 100M records). 3. foreach, then there's still 2 foreach inside. 4. insert into temp table. The user complains it is still taking too long to process a report. Since all the tables inside the program were "update statistics mendim" was done. I did perform update statistics medium on all the sysxxxxx tables. Question is: 1. Does update statistics medium on sysxxxxx tables will show any improvement? 2. What is the best approach do you think? NOTE: I can't simulate the problem and see the session or SQL2 running.. onstat -g ses 0

JACK PAPA wrote: > Hi All, > > I got a user complaining that according to her, 2 months back, the report > processing takes only 15-30 mins. Now, she's telling me that 2 hours, the > report still not finished. > > I did check the program and it showed that the last update stats done was May > 2010, > > I did perform update statistics medium on all the tables i found in the select > statement. > > Here are some samples how the program is organized > > 1. create temp table (this is the storage after a big select statement is > done). > > 4 or 5 columns are indexed. > 2. select statement (10 tables with around 20 where clause). (big tables of > around 100M records). > 3. foreach, then there's still 2 foreach inside. > 4. insert into temp table. > > The user complains it is still taking too long to process a report. Since all > the tables inside the program were "update statistics mendim" was done. I did > perform update statistics medium on all the sysxxxxx tables. > > Question is: > 1. Does update statistics medium on sysxxxxx tables will show any improvement? It's quite likely that medium will cock things up, if you haven't done the necessary highs. > 2. What is the best approach do you think? Get dostats and run that. Or let OAT do a proper update stats for you. > NOTE: I can't simulate the problem and see the session or SQL2 running.. > > onstat -g ses 0 -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Thanks a lot..I will check what dostats says.

A couple of suggestions Jack. See below: Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 26, 2010 at 1:27 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > I got a user complaining that according to her, 2 months back, the report > processing takes only 15-30 mins. Now, she's telling me that 2 hours, the > report still not finished. > > I did check the program and it showed that the last update stats done was > May > 2010, > > I did perform update statistics medium on all the tables i found in the > select > statement. > Usually this is not good enough. In addition to the MEDIUM on all columns you need to do: 1. Update stats HIGH on each column that leads an index key. 2. If two indexes start with the same column(s) also do update stats HIGH on the first column in each index that is different. 3. Update stats LOW on the entire key for each index. > Here are some samples how the program is organized > > 1. create temp table (this is the storage after a big select statement is > done). > > 4 or 5 columns are indexed. > Don't create the indexes on the temp table until AFTER the load is complete. > 2. select statement (10 tables with around 20 where clause). (big tables of > around 100M records). > 3. foreach, then there's still 2 foreach inside. > 4. insert into temp table. > Change steps 2, 3, 4 into: INSERT INTO <temp table> SELECT ...... This is significantly faster. You can only come close to this if your application is using separate connections for the SELECT and the INSERT, the INSERTS are being done through a PUT cursor, the SELECT cursor is using the FETCH ARRAY feature of ESQL/C, and you have increased the fetch buffer size (FETBUFSIZE) to maximum (32767) from the default of 4K. Easier to just use INSERT INTO ... SELECT ... FROM ... Also, if the report that will read the temp table is doing any filtering at all, and given that you have created indexes on it I suspect that it does, at least running UPDATE STATISTICS HIGH on the leading index columns will help the optimizer make better decisions. > The user complains it is still taking too long to process a report. Since > all > the tables inside the program were "update statistics mendim" was done. I > did > perform update statistics medium on all the sysxxxxx tables. > > Question is: > 1. Does update statistics medium on sysxxxxx tables will show any > improvement? > It can't hurt, but unless you have thousands of tables and many thousands of columns, stats on the system catalog don't help much. For very large systems like BAAN, SAP, PeopleSoft, etc., yes, this can make a difference. On most systems it's trivial. That's why updating the catalog tables is an option that defaults to off in my dostats utility. > 2. What is the best approach do you think? > See my notes above about update stats and changing the application. > > NOTE: I can't simulate the problem and see the session or SQL2 running.. > You can, however, add code to the application to start off with a SET EXPLAIN ON if some environment variable is set. That will allow you to easily test this anytime you need to. > > onstat -g ses 0> ??? Version and platform info?? If you are on 11.xx you can use onmode -Y <sessid> 1 to turn on set explain externally. However, that only works if the application hasn't pre-prepared all of its statements at startup. It can only catch queries that are optimized after you issue the onmode. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f546e4ed2089048eb9ac5e

Thanks Art and Obnoxio for the usual help...