Improving performance with indexes

Selecting data from a large table with millions of records can be a very slow process when records are scanned directly. A good index can drive search queries many times faster. We will see how to control table indexed from the NAV development environment.

How to do it...

This recipe shows how to create indexes in a table to improve the performance of queries from the previous recipe.

1. In the list of keys, move to the empty line below the primary key No. and click the assist button on the right.

2. Open table 50010 Item Certificate in object designer and click View | Keys.

3. In the Fields List window, add two fields to the index:

   • Item No.

   • CA Code

4. Click OK, then close key's list and save the table:

   

5. Open table 5802 Value Entry in table designer, go to key's definition.

6. Add a key containing two fields: Item No. and Certificate No..

   Certificate No. is a custom field added in the recipe Creating Custom Tables.

7. Click the assist button in the SumIndexFields field and add two...