Re: Query Optimiser

> >bochner@das.harvard.edu (Harry Bochner) writes: > > This may be true in newer versions of the software, now that the optimizer > seems to be mostly improved, with a few areas of lost ground (trade-offs?). > In my example I had the inner DECLARE CURSOR within the outer FOREACH loop. > I did that for clarity. In reality I usually DECLARE CURSORs outside the > outer loop, and so avoid the overhead of repeated DECLAREs. As you say, > the inner cursor is still repeatedly opened and closed. > > >The difference between your experience and mine suggests to me that there > >are several trade-offs involved here, with the details of the case > >determining which trade-off is more important. Certainly your remarks make > >me feel better about searches that I meant to recode with a single cursor, > >but haven't gotten around to. > > Indeed, several trade-offs which are probably data dependent and thus will > also vary from run to run. An extrapolation from my earlier remarks is that, > given the same database, nested cursors perform better when relatively few > rows satisfy the selection conditions, while joined queries perform better > when more rows satisfy the selection conditions. This is certainly consistent > with your discussion of cursor optimization and opening overhead. From what I've, there is a definite crossover point when deciding between multi-table joins and nested cursors. For small-medium amounts of data, the multi-table join is much faster. The re-opening of cursors (even prepared cursors) destroys any performance benefit gained from outguessing the Optimizer. However, when the amount of data starts getting into the large-huge amount, then cursor penalty is well worth the cost. Also, with large amounts of data, you may start smacking into assorted walls based on free space and temp table sizes and such that wouldn't be a problem if you weren't trying to join 3 different million+ row tables. Of course, when you start dealing with data that big, I've found it wise to take huge whacks and cut off large chunks by loading data into temp tables, and then joining them (Joining 3 different 10,000 row tables is nothing compared to million row tables). Of course, the problem here is if you're working with a system that can be used in either environment. Those users with relatively small amounts of data suffer a performance penalty to run code designed for these large tables. The alternative is that the large users just don't run at all. In any case, when you notice that your expected row count and query cost both hit MAXINT, it's time to take a strong look at your process and your database (amazing what a missing index can do to you). Will (la4gen!villy@uunet.uu.net)