Tips to improve your queries

[Old answer centre doc. Revised]


A dozen Tips to enhance query performance

1. Use CheckPlus Queries when possible. Much faster!

In a CheckPlus Query the answer table is not sorted, and duplicates are allowed. While intuitively it may seem that eliminating duplicate records would be faster because it would reduce the number of records needing to be processed, actually sorting and checking for duplicates takes longer than simply reading through the table and placing all matches in the answer table in unsorted order is quicker.

2. Check Only the fields needed.

Checking more fields than needed requires processing more raw data - hence more overhead

If you need all fields in the answer (to run a prewritten report) but in your query logic do not need to check all fields, it may be faster to run two queries.

The first query only checks those fields required to generate the correct records in the answer table.

The second query to link the first answer table back to the main table, checking all fields in the main table to get an answer with all fields

3. Use Tools | Queryspeedup. Query Speedup Generates secondary indices which will help subsequent executions of the query. You may want to have the Custom configuration setting of Indexes Maintained set to yes.

4. Use Normalized table structures, which will enable you to perform queries against primary index fields as much as possible.

5. Give Paradox as much information as possible to perform the query. The more information Paradox is provided with,
the better Paradox can optimize the query by restricting the scope of records it needs to manipulate in temporary files.

6. Use EXACT match searches whenever possible. - This will allow better use of the indexes. Wildcard searches can force a sequential search of the table data, instead of a binary search of the index. - Avoid using wild card searches like: "..smi.." - this will disable any use
of an index and force a sequential search of the data.

7. When possible, use range searches rather than pattern searches. eg: to find all dates in 1994 use: >= 1/1/94, <=12/31/94 rather than: ../../94

8. Sometimes extremely complex queries will return faster results if the queries are broken into a series of discrete steps. - instead of doing a single massive query, perform two or three smaller ones. In many cases this will result in a net gain in performance when the queries are chained together.

9. Defragmented hard disks allowing cleaner reads and writes to and from the disk.

10. When applicable use the 'or' operator rather than multiple query lines.

11. Select 'Fast queries' under Preferences. For changeto, insert and delete queries - this option stops Paradox from generating temporary (auxilliary) tables associated with these type of tables : CHANGED, INSERTED, DELETED.

Note: Adding this operator will make these queries irreversable.

12. When running queries against SQL tables, write the queries in native SQL. Also, do not use SCAN or LOCATE with Tcursors against SQL tables as this will greatly affect performance.