Re: update statistics not recommended!

Neil Truby wrote: > > My new site has a third-party financial package (Lawsons), running on > v7.13. The underlying DBMS is Informix. Another satisfied Lawson customer? I'm not alone . . . 8-) We run a mix of Lawson apps and Informix 4gl apps. Much of our 4gl only hits 'our' databases, but we also interface with Lawson and run reports built from Lawson tables. As a result, we run update stats against our Lawson database. > The Lawsons help desk has told > me that they recommend that we never run UPDATE STATISTICS, as it will > degrade performance. We were never told that in the 'old' days, only recently. Their SQL is always expected to run against an index. The only problem is that they are afraid that Informix will choose the wrong index. We had a problem with that on one table (gltrans) in one instance; Informix ran on the wrong index. We traced that to a bug in 7.13 where update stats low could really hose the index information in sysindexes. I've pared my update stats back to high and medium with no ill effects. That way, Informix 4gl is happy and Lawson still seems to be happy. > Their reasoning, allegedly based on empirical > evidence of their users, is that all their SQL is written with where > clauses, and the indexes that exist will be used, which is what is > required. If we run an UPDATE STATS, we might risk unexpected and > undesirable access paths. No problems here, except for the bug as stated above . . . bug number availiable upon request (I think I still have that documentation). My guess is that the bug is fixed in current version (7.23), but upd stats low takes such a long time to run on our system due to the size of the tables. We'll keep it running with high and medium until we upgrade to Informix 7.30. Besides, with onstat -g ses / sql, it's easy to monitor Lawson SQL. That's how I've been able to check and make sure that Lawson uses the correct index. (Besides, it shows how Lawson puts its SQL together, which is real scary. Not to mention program processes . . . . but I digress.) > A caveat is added that, under v7.23, some > customers have experienced benefits from an UPDATE STATISTICS LOW, and > that we might run it, but prepared to remove the statistics so derived > if performance nose-dives. Again, we use HIGH and MEDIUM with no problems. Give me a holler and we can trade some war stories. John Carlson Informix DBA WH Smith, Inc. #include std_disclaimer.h #include lawson_wish_list.h