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 Well, I'm not sure what bollocks is, but if it's anything like bulls#it, I think you've got it, old bean. I can't imagine how you would not greatly improve performance by having accurate stats. I'd do it, the app has no way to know or influence the optimizer for that matter. Heck, optimizer behavior can and will change from release to release, especially in the absence of stats. Generally, Informix' optimizer does a *great* job if it has good stats, and just awful otherwise (I doubt Informix puts much effort into optimizing in the face of poor to nonexistant stats...there's no good reason to). Sounds like a Oracle rule based optimizer mentality at work to me. Was this package first or primarily written for Oracle or some other rule based critter?? Sheesh, this is 1998, last I checked. Try to collect some "before" stats for your common tasks, reports, both fast and slow so you can compare after and arrive at a really quantifiable conclusion. -- Greg Moye The above are my opinions only.