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. The Lawsons help desk has told > me that they recommend that we never run UPDATE STATISTICS, as it will > degrade performance. 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. 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. > > Well, sounds like bollocks to me, but does anyone else have any views > or, better still, experience of this software packeage running with > Informix? > > Thanks > > Neil I would ask them to take a look at their code a second time. Update statistics is good. I've always seen performance improve afterrunning it. As far as apps. taking the wrong access path (i'm assuming query plans here), you can influence it by OPTCOMPIND. Most of the ERP's were written for older backend's and may get confused by Online's optimizer. A good example for this is BAAN. Playing with OPTCOMPIND, I found I got much, much better performance by setting it to 0 vs. 2 for BAAN. Since then, as a starter, I've been using OPTCOMPIND = 0 for OLTP systems and 2 for DSS and then do my analysis. So far, it's been looking good. But, I always run update stats. Arun