Re: ISQL Performace Optimization

In article <1992Apr27.045011.1397@beaver.cs.washington.edu>, jdirks@cs.washington.edu (Jeffrey Dirks) writes: > I have a rather general question about query optimization and > performance benchmarking with Informix ISQL/4GL 4.1 running > with the online engine. > > We've used the SET EXPLAIN option to study the query plan > for most of the processor intensive joins butr have found it > a bit vague in determining from where some extra time > savings could be found. We've found 'set explain' to be useful only in the most obvious of cases, to confirm what indices are being used for simple queries, etc. What we would really like is the capability to INSTRUCT the optimizer of when we 'know' better. Beyond that, a good knowledge of the schema, a knowledge of what is being done [after all, if you make a million reads, it just HAS to take a while ] and a desire to experiment with real data seem to be the most useful. In addition, the INFORMIX Guide to SQL Reference, Version 4.1.0, has a VERY useful chapter on OPTIMIZATION, although this is NOT a magic bullet. An important observation is that multiple table joins frequently do NOT operate as one might expect, and that by a simple redesign of the query, some dramatic optimization might be achieved [see above reference for an example]. Standard disclaimers - and I'm still learning ... Ihor Kinal att!cbnewsh!ijk