Getting Started

Documentation

Glish

Learn More

Programming

Contact Us

Version 1.9 Build 1556

News	FAQ
Search	Home

Subsections

• Time/position considerations
• Optimization

Some further remarks

Time/position considerations

A position constant (e.g. 3h4m) is converted to radians, so it can be used easily in functions like SIN. Since a time constant has exactly the same format, it is also converted to radians, while the user may expect it to be expressed in seconds. The user has to take this into account if times are used in a comparison. For example, timecol > 3h4m is only correct if timecol has unit radians.
To make life easier the function TIME results in a value in radians, so it can be used directly in a comparison. For example, TIME(datecolumn) > 3h4m.
In the future this may change if units are handled in their full glory.

Optimization

A lot of development work can be done to improve the query optimization. At this stage a few optimizations are done.

• Constant subexpressions are calculated only once. E.g.
  in COL*sin(180/pi) the part sin(180/pi) is evaluated once.
• If a subquery generates intervals of doubles or dates, overlapping intervals are combined and eliminated. E.g.

  select from GER.MS where TIME in [select from ::POINTING where
  sumsqr(DIRECTION[1])>0 giving [TIME-INTERVAL/2=:=TIME+INTERVAL/2]]
  

  can generate many identical or overlapping intervals. They are sorted and combined where possible to make the set as small as possible.

The user can optimize a query by specifying the expression carefully. When using operator | | or &&, attention should be paid to the contents of the left and right branches. Both operators evaluate the right branch only if needed, so if possible the left branch should be the shortest one, i.e., the fastest to evaluate.

The user should also use functions, operators, and subqueries in a careful way.

• SQUARE(COL) is (much) faster than COL^2 or POW(COL,2), because SQUARE is faster. It is also faster than COL*COL, because it accesses column COL only once.
  Similarly SQRT(COL) is faster than COL^0.5 or POW(COL,0.5)

• SQUARE(U) + SQUARE(V) < 1000^2 is considerably faster than
  SQRT(SQUARE(U) + SQUARE(V)) < 1000, because the SQRT function does not need to be evaluated for each row.

• TIME IN [0 < : < 4] is faster than TIME>0 && TIME<4, because in the first way the column is accessed only once.

• Returning a column from a subquery can be done directly or as a set. E.g.

    SELECT FROM maintable WHERE time IN
        [SELECT time FROM othertable WHERE windspeed < 5]
  

  could also be expressed as

    SELECT FROM maintable WHERE time IN
        [SELECT FROM othertable WHERE windspeed < 5 GIVING [time]]
  

  The latter (as a set) is slower. So, if possible, the column should be returned directly. This is also easier to write.
  An even more important optimization for this query is writing it as:

    SELECT FROM maintable WHERE time IN
        [SELECT DISTINCT time FROM othertable WHERE windspeed < 5]
  

  Using the DISTINCT qualifier has the effect that duplicates are removed which often results in a much smaller set.

• Testing if a subquery contains at least N elements can be done in two ways:

    count([select column from table where expression]) >= N
  and
    exists (select from table where expression limit N)
  

  The second form is by far the best, because in that case the subquery will stop the matching process as soon as N matching rows are found. The first form will do the subquery for the entire table.
  Furthermore in the first form a column has to be selected, which is not needed in the second form.

• Sometimes operator IN and function ANY can be used to test if an element in an array matches a value. E.g.

    WHERE any(arraycolumn == value)
  and
    WHERE value IN arraycolumn
  

  give the same result. Operator IN is faster because it stops when it finds a match. If using ANY all elements are compared first and thereafter ANY tests the resulting bool array.

• It was already shown in the indexing section that indexing arrays should be done with care.