Re: Advantages upgrading from Informix 4.xx to 6.xx

David Williams <djw@smooth1.demon.co.uk> wrote: :In article <NHR2BDATFhezEw1d@nickcol.demon.co.uk>, Nick Colthorpe :<nickc@nickcol.demon.co.uk> writes :>Can anyone tell me the advantages/disadvantages of upgrading from :>Informix version 4.00 to 6.00. :>We are using the Standard Engine running under SCO UNIX. If it's the Standard Engine you want to upgrade you should of course go to 7.x (not 6.x) and the appropriate tools whatever you are using. : A better optimizer which can use data distributions. A new UPDATE : STATISTICS samples the values in the column and stores how often : values occur. It splits the range of values in the column into : several ranges and stores how many rows are in each range of : values for the column. Although I have said it before I just wanted to mention to this poster as well that this is a two edged sward. It used to be that the SE engine (up to ver. 5.x) could be installed and run doing essentially no maintenance but backups and execute a single "update statistics" statement as often as you found necessary (say every night). With ver. 7.x we have found we need to do some fairly sofisticated update statistics (using medium and high) to just get back theperformance of the 5.x engine. Without these (using a simple update statistics only) the optimizer would go completely wild in its choices. By completely I mean that statements that executed in 1/2 hour in 5.x hadn't finished after 24 hours in 7.x. This was all fixed after a lot of update statistics. The advantage is that if you have statements that the 5.x (or earlier) optimiser allways chose the wrong join path on they can be fixed with the appropriate update statistics commands in 7.x. We where able to achieve perhaps a 100 fold performance increase on some statements in this way. Because of this there where statements you simply couldn't run in 5.x that runs well in 7.x. Sometimes you could program arround them, but that would mean splitting select statements that should have been a simple joins into several selects. Not a good solution. (Sometimes there where also super hacker type of solutions, but I dislike those so much I never used them.) I have seen no increase in raw performance from 5.x to 7.x of SE, and as far as I can remember there was non (or at least nothing significant) in earlier versions either. : International character support. : : These are the main two + it is the lateste version hence will be : supported in the future whereas support for SE 4 will be phased out : soon. : :-- :David Williams Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org