Re: Re : Query Optimiser

Richard Ridley writes: ->We had other instances where using a program variable value wherever ->possible (available) rather than cross-column matches in a query also [1] ->prevented the optimiser from getting misled into sequential searches. -> ->I would also like to know if there is some documentation somewhere on ->the best way to structure queries, or any tips on when to use one ->complicated cursor as opposed to a number of simple, embedded cursors ->- I have had experiences in the past where using a number of simple [2] ->cursors cut a query down from 3 minutes to a few seconds. I have tried to write this a couple of times, and it is hard to state briefly. Please bear with the following pseudo-code which is sort of a blend of 4GL syntax and ESQL/C syntax. Anyhow, based on my experience: case [1] SELECT ... FROM tab1, tab2 WHERE tab1.col = $var AND tab2.col = $var { no join } **always** is no worse than and usually outperforms SELECT ... FROM tab1, tab2 WHERE tab1.col = $var AND tab2.col = tab1.col { join } case [2] DECLARE select_from_tab1 CURSOR FOR { nested cursors } SELECT ..., col, ... FROM tab1 WHERE key = $keyvarFOREACH 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 = $keyvarFOREACH select_from_both INTO ... ... END FOREACH In each case, the first version avoids the join that is done in the second version. Joins suffer from the "Cartesian product problem", which can be stated (VERY informally) as follows: In joining two tables, each of which has 1,000 rows, the engine must do work equivalent to forming a pseudo-table of 1,000,000 rows in which each row of the first table is matched with each row of the second table, and then the engine discards the rows that do not satisfy the join criterion, returning only those rows that qualify. The amount of work done increases multiplicatively as the number of tables in the join increases. Using indexes reduces each row search within each table from N/2, on average, to log(N), so getting the engine to use an index is your major optimization, since it works in both SQL and in procedural cases. 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. As an extreme case, consider your example of a four-table join. Assume that NO rows qualify because of criteria on one table. It might take the engine between 4*log(N) and (log(N))^4 amount of work, depending on how smart the optimizer is, to do the indexed joins and then discard all rows. If the "failing" table were in the outer cursor loop, then the procedural approach will finish after log(N) amount of work, since the inner loops will never be entered. 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!) \\ /________________________) (____________________________\\