OPTIMISATION of SELECT statement

The best route will depend on how many rows you expect the query to retrieve and the ORDERing thats required rather than the immediate size of the tables. If you know in advance that the number of rows that are going to be retrieved from some of your tables is likely to be small AND/OR you have specific columns that you are ORDERing by, then preselect data from them into one temp tables, or make the 4GL slightly more complicated and process each of these tables in a FOREACH loop with the remaining queries being PREPARED in advance. Note, for ORDERing, if you are able to select the columns to be ORDERed by into 1 temp table, and use the resulting table to join with the rest of the tables (I am assuming that your ordering requirements are limited to a few tables, not all of them), you will not need to apply an ORDER BY to the remaining query(s) since the data in the temp table is pre-ordered. Also, take note of any existing indexes that you can take advantage of. You can sometimes force the optimiser to choose an index to scan (as opposed to a sequential one) by adding dummy criteria to the WHERE clause. No doubt the route you choose will be affected by the version of the engine that you are using. Version 7 being capable of parallel queries may perform much better on a few larger queries than version 5. since I have not worked with anything newer than 5, this information is based on reading manuals rather than experience, perhaps one of the people with this knowledge will fill in any gaps here. In older versions of Informix the order of the tables DID make a difference, but in newer on 5+ I think they have done a good job on optimiser because I have run tests on the engines with SET EXPLAIN and found that for most queries it chooses the same route, regardless of the order of the tables. (I have not tested it with more than 6 tables). I suggest you do some advance experiemntation using SET EXPLAIN, set yourself up with a test database containing a subset of the data and only the tables you are interested in (if you can). Mark Denham BBC London, UK Mark.Denham@bbc.co.uk