Query Construction Guidelines

A thorough understanding of the guidelines provided here will assist you in formulating queries that can be optimized efficiently by RDM SQL.

  • Formulate WHERE clauses in conjunctive normal form. Avoid using OR.
  • Formulate conditional expressions according to the forms listed in Table 10. Use literal constants as often as possible. The compile-time for most queries is insignificant compared to their execution time. Thus, dynamically constructing and compiling queries containing literal constants (as opposed to parameter markers or stored procedures) will allow the optimizer to make more intelligent access choices.
  • Make sure that the only columns that have the same name in tables that are related through foreign and primary keys are the foreign and primary key columns themselves. Then use the NATURAL JOIN clause when formulating queries that join the two tables.
  • Include more (not fewer) conditional expressions in the WHERE clause, and include redundant expressions. For example, foreign and primary keys exist between tables A and B, B and C, and A and C. Even though it is not strictly necessary (mathematically) to include a join predicate between A and C, doing so provides the optimizer with additional access path choices. Also, assuming that join predicates exist and a simple conditional is specified for the primary key, you can include the same conditional on the foreign key as well. Look at the following query:
select ... from A,B where A.pkey = B.fkey and A.pkey = 1000

You can improve this query by adding the conditional shown in an equivalent version below.

select ... from A,B where A.pkey = B.fkey and A.pkey = 1000 and B.fkey = 1000
  • Avoid sorting queries with large result sets in which no index is available to produce the desired ordering. If you have heavy report writing requirements, consider using the mirroring feature to maintain a redundant, read-only copy of the database on a separate TFS and run your reports from there. This will allow the primary system to provide the best response to update requests without blocking or being blocked by a high level of query activity.
  • In defining your DDL, explicitly declare the foreign and primary key relationships. You can still do joins between tables even when the relationships are not declared but optimum join performance is guaranteed when you declare those relationships in your CREATE TABLE DDL statements.
  • Do not include conditional expressions in the HAVING clause that belong in the WHERE clause. Conditional expressions contained in the HAVING clause should always include an aggregate function reference. Note that expressions in the HAVING clause are not taken into consideration by the optimizer.
  • Execute an UPDATE STATS statement on your database once you have a representative amount of data in your database. You can include the AUTORUN clause of your UPDATE STATS statement to ensure that UPDATE STATS will automatically run after the specified amount of database changes have occurred.
  • In lieu of UPDATE STATS (e.g., you don't have the memory available to store the generated statistics), use the DISTINCT VALUES and RANGE clauses in either the CREATE TABLE or the SET COLUMN STATS statements to provide more statistical information to the optimizer. The DISTINCT values clause is particularly important for equality conditions. Do not declare a key on a column that has only a few distinct values. For example, never declare a key on a column that contains a person's gender. If no DISTINCT VALUES clause is specified, the optimizer will use the current number of rows in the table. The RANGE clause is used with inequality conditions. Again, these are only used when you are running without UPDATE STATS.
  • Only declare keys that you actually need to get the needed performance in your embedded application. More keys increases the time to insert new rows in a table besides consuming more storage.