Now that you are ready to implement the functions in the IP, you need to understand optimization. For an IP, the processing of a query involves building rows from the data source and evaluating each of the rows against the expression in the WHERE clause to determine whether the row is part of the result set or not. Optimization mainly consists of reducing the search space as soon as possible by using constraints provided in the query.
The OpenAccess SDK SQL engine provides functions to help you implement optimized access to the database when the following types of conditions exist in a query:
1. Equality and like conditions on a specific column—for example, a condition of the type:
WHERE NAME='T1' OR NAME='T2' OR NAME LIKE 'T%'
with any number of other AND constraints can be executed by retrieving the possible values for the column NAME, and then processing just the rows matching {T1, T2, T%}.
2. Value range on a specific column – for example, a condition of the type:
WHERE intval > 5 AND intval < 20
with any number of other AND constraints, can be executed by retrieving the range for the column index and then processing just the rows with an index in the range of 5< intval <20.
3. Combination of 1 and 2 above – for example, the condition:
WHERE (NAME='T1' OR NAME='T2' OR NAME LIKE 'T%' ) AND (intval > 5 AND intval < 20)
can be executed by retrieving the possible values for the column NAME and then processing just the rows matching {T1, T2, T%} with the additional limit of INTVAL between 5 and 20. This type of optimization makes sense in accessing data where a given column value can appear in many rows by itself but in fewer rows in combination with another column value range. An example is time-based data for a given tag. A name T1 may have data for many years but only a few points between any given time range.
