From 4.523 DP4 SQL will support the use of a certain number of compilation hints to allow the SQL Engine to execute queries more quickly. By default it will also use a different and usually better algorithm to select the indexed to be used for reading tables.
By default when executing a query that accesses more than one table the DP4 SQL engine sometimes rearranges the order in which tables are accessed. The rearrnagment is intended to improve the way in which the query will be executed, but in fact it sometimes results in worse execution.
You can stop the SQL engine reordering tables in a particular query by adding this comment to the SQL statement: /* ADESQL O0*/. If you do this you may like to experiment with listing tables in the FROM clause in various orders to see which executes fastest. To disable reordering of table accesses generally add this line to the [SQLLIB] section of the DP4 configuration file:
table_sort=0
Then to get table ordering back in queries that benefited from it add this comment to queries: /* DP4SQL O2*/
If you know a query used to execute much faster with 4.519 or earlier versions of the DP4 SQL engine try this: /* DP4SQL O3 */
The SQL Engine now also picks indexes differently, and the new new rule can execute some queuries thousands of times faster. Nevertheless in rare cases it may execute worse. To get the old rule by default add this line to the [SQLLIB] section of the DP4 configuration file:
table_index=1
The new rule (equivalent to table_index=2 will be better in most cases. It will only be worse where there is a multi key index that can be used to optimise a query but the index is actually more poorly discriminating than a single key index. e.g. selecting a credit card on name and expiry year/month is quicker by name than year/month (assuming no index on all three).
/* DP4SQL I2*/ selects the new rule in a particular statement.
/* DP4SQL I1*/ selects the old rule in a particular statement.
/* DP4SQL I0*/ forces the prime index to be used, and may speed up simple selects for which the main index is going to be the best choice.
You can set both compilation options in a single comment like this /* DP4SQL In On*/ (or vice versa)
For further details consult the updated Guide to DP4 Configuration and the updated SQL Products Guide available from page A4000015.
Beta software is supplied without warranty and should not be used in live sites without the agreement of Itim Technology Solutions. Compatibility with an eventual final release cannot be guaranteed.
Please post SQLERR.PRN to your system database with the 4.5xx version of MAPPOST.