TurboDB Engine Documentation

TurboSQL Statements

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

The condition

A.a like 'USA'

can be optimized, while

Upper(A.a) = 'USA'

cannot.

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

instead of

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
where ...

might be much faster than

select * from C, B, A
where ...

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.