TurboDB Engine Documentation
|Previous Top Next|
Some rules for fast TurboSQL statement execution:
Start the where and the having clause with simple and-ed conditions
If logically applicable, order your search-conditions like this:
A.a = B.a and C.b > X and (...)
i.e. start with simple comparisons, which are necessary for the whole search-condition to be satisfied. These simple comparisons are most suited for optimization. The optimizer will try to create this structure of the search-condition automatically but may in some cases not be smart enough to do so.
Separate the column-reference from the value in comparisons
If you write
A.a > 2 * :ParamValue,
this will be optimized more likely than
A.a/2 > :ParamValue.
The important point here is that the column reference A.a stands alone the left side of the comparison.
Prefer like over Upper
A.a like 'USA'
can be optimized, while
Upper(A.a) = 'USA'
Prefer left outer joins over right outer joins
The implementation of joins largely favors left outer joins. Whenever it is suitable in your application, write
B left outer join A on B.a = A.a
A right outer join B on A.a = B.a.
This can speed up your statement considerably. The optimizer does not do this conversion by itself, because it would deprive you of the possibility to hand-optimize your statement.
Modify the Sequence of Tables in the From Clause
This sequence can have a severe impact on the query performance. If you think, your query is not as fast as it should be, just check out different orderings in the table-reference list.
select * from A, B, C
might be much faster than
select * from C, B, A
Normally the optimizer will try to order table-references not part of a join in the best way, however sometimes assistance from the programmer is needed.