Re: Informix upgrade slows Delphi

Art S. Kagel wrote: > > root@candle.pha.pa.us wrote: > > > > I had a client who just upgraded from Informix 7.1 to 7.2, and all of a > > sudden, all the queries are slow. Moving from field to field which does > > a single SQL table lookup takes 30-40 seconds. Seems like it is doing a > > sequential scan or something. > > Have you checked OPTCOMPIND in the ONCONFIG file? If I remember correctly, Informix defaults this value to 2 instead of 0. A setting of 2 means that the optimizer will prefer a cost-based solution over index joins. That may mean using sequential scans. > > Nothing has changed on the client end, including the Informix I-Net > > 5.10. He has many visual basic applications that also use the same > > I-Net on the same computer, and they work fine. > > > > Any ideas? How could a minor Informix upgrade cause this. > > Yes the optimizer got an intelligence injection in 7.2x so it may be > interpreting your table structure differently. If you have not done so > update statistics according to the instructions in the release notes> file SERVERS_7.2 (if those instructions are not there or are not > different from those in SERVERS_7.1 let me know I'll mail you the > updated recommendations or get my dostats.ec utility from my submission > to the IIUG software repository which implements these recs). If you > are doing multiple table joins you should also update statistics HIGH > on any join columns, in all joined tables, which are not already at > HIGH. > > In any event you should run the queries with SET EXPLAIN ON to > determine exactly what is happening. You may need to add indexes. > > If you still cannot improve things post a schema and representative > queries and someone will pitch in. > > Art S. Kagel John Carlson Informix DBA WH Smith, Inc.