Re: Query Optimiser

In article <2a44cmINNh08@emory.mathcs.emory.edu>, alan@po.den.mmc.com (Alan Popiel) writes: |> case [2] |> DECLARE select_from_tab1 CURSOR FOR { nested cursors } |> SELECT ..., col, ... FROM tab1 |> WHERE key = $keyvar |> FOREACH select_from_tab1 INTO .., $var, ... |> DECLARE select_from_tab2 CURSOR FOR |> SELECT ... FROM tab2 |> WHERE col = $var |> FOREACH select_from_tab2 INTO ... |> ... |> END FOREACH |> END FOREACH |> |> **always** is no worse than and usually outperforms |> |> DECLARE select_from_both CURSOR FOR { one cursor with join } |> SELECT ... FROM tab1, tab2 |> WHERE tab1.col = tab2.col |> AND tab1.key = $keyvar |> FOREACH select_from_both INTO ... |> ... |> END FOREACH ... |> Using the procedural nature of your program to forcibly break the join apart |> does even more to optimize the query. At worst, it will be no worse than |> the optimized join, and usually it will be much better, especially if you |> put the more selective criteria in the outer cursor loops. 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. 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. 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. 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