Field conditions and performance

Date:Archived
Product/Release:LANSA Client
Abstract:Whether field conditions are applied to fields from primary or secondary files will affect the performance of queries
Submitted By:LANSA Technical Support

Field conditions can be attached to any of the fields in a query. These may be fields from the primary file or fields from any of the secondary files. In general a query will run faster if conditions are specified for the fields of the primary file and slower if conditions are specified for the fields of the secondary file.

Filed Conditions

For example, imagine that print sales values need to be printed from the Sales file and a product descriptions from the Product file.

Condition on the sales value in the sales file

For a condition on the sales value, (for example to print only the exceptionally low sales and the exceptionally high sales values), it is more efficient to make the Sales file the primary file and the Product file the secondary file. LANSA Client reads records from the Sales file to find out whether the criteria are met. The Product file is only read to find the product description for the records that meet the selection.

Condition on the product description in the product file

For a condition on the product description, for example, display only those products that contain the word 'print' somewhere in their description, it is more efficient to make the Product file the primary file and the Sales file the secondary file. Ideally a logical file would be used, indexed by the product description. Conditions on key fields are very fast. The database administrator can help here.

Mixture of conditions

To specify conditions on a mixture of fields from the Sales and Product files, it might be more efficient to make the smallest file the primary file. In this example probably the Product file would be smaller than the Sales file.

How to boost performance and user friendliness

The database administrator can make queries easier to design and faster to run by defining "predetermined join fields". For example, the administrator can define the product description as a predetermined join field to the sales file. When this is done, the description will appear as a field in the sales file. The LANSA component on the AS/400 will only "read" that description when the other criteria are met and will do so in a very efficient manner. This results in faster queries.

The above tips are only a matter of efficiency. If they aren't applied a query will still run but it might run slower. Ask the database administrator for advice on the corporate database if needed.