Re: Query Optimiser

->bochner@das.harvard.edu (Harry Bochner) writes: -> ->Unfortunately, Alan, my experience doesn't agree with this. I have reports ->that sped up considerably when I recoded them from using nested cursors to ->using one cursor with a join. Thank you, Harry, for your gracious contradiction. One thing I like about this forum is that we have so few flamers. Differences are seen as an opportunity to discuss and learn, rather than a reason to attack. ->My impression was that the performance improvement I saw came from the fact that ->when you use nested cursors, the inner cursor gets reoptimized and reopened once ->for every row, while single cursor with a join avoids this. But this is just a ->guess. Based on this experience, my recent practice has been to use a single ->cursor whenever it's easy to do so. 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. ->Does anyone know how to tease apart the trade-offs in more detail? -> ->BTW, my remarks are based on experience with RDS & SE, versions 4.00 & 4.10 ... ->-- ->Harry Bochner ->bochner@das.harvard.edu Mine are based on C4GL & SE, mostly version 2.10 with some 4.10. So the version differences may explain some of the variance in results. We know from prior discussions that the optimizer differs greatly between SE and OnLine, due in part to the larger number of statistics available in OnLine. It seems to me that cursor declaration and optimization is an area that might be particularly sensitive to the differences between compiled 4GL and RDS. Would some of our friends at Informix care to comment on this? Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, Tech Ops | / \\ alan@den.mmc.com | P.O. Box 179, M/S 5422 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\