Range Definition (Magic xpa 3.x)

When browsing large tables in relational databases it is important to use ranges to reduce the number of records in the view. When a table is accessed without ranges, such as in the APG, some of the operations can take a long time, especially operations such as locate and page up. To improve performance the ranges should be on segments of an index.

Magic xpa lets you specify ranges from four places:

• Magic xpa SELECT statement – All ranges mentioned in the Magic xpa SELECT statement become part of the SELECT statement. The range will then be handled by the RDBMS with a WHERE clause, even during a sequential search. Magic xpa receives only the records that answer the query.

• Range expression at the task level – When using a range expression at task level, Magic xpa checks each record returned from the database against the expression, and decides if the record is part of the view. Magic xpa receives all the records and performs the filtering on its own.

• DB SQL Range – Free text that will be concatenated to the WHERE clause sent to the database as is. This is done for Physical transaction mode tasks only. There is a need to know the specific database syntax.

• Magic xpa SQL Range – a Magic xpa expression that Magic xpa translates to the appropriate syntax per database. This is added to the WHERE clause sent to the database. There is no need for the specific database syntax.

These four options perform very differently. It is better to put as much of the range information as possible in the range that can be sent to the database. Only enter ranges that cannot be expressed otherwise at the task Range level.