Helping your app search the data is important for application performance. Adding indexes can help keep searching the data as fast as possible. Apex Designer makes it easy to define indexes and constraints for a Business Object without being a database administrator.
Adding a Supplier Name Index
Open the Supplier business object that you created in Create a Supplier Management App. The index section is in the middle of the page and you can start the index by specifying the name and pressing <Enter> . A good pattern to follow for naming indexes is to identify the column(s) included in the index.
We can add the name property to the index by typing (or selecting) name in the 'Add a property' field. Just like, that you have created your first index.
Preventing Duplicate Supplier Names
If you wanted to avoid duplicate Supplier names we can select the Unique index checkbox. This stops the database from allowing the exact same supplier name more than once. You can also update the index name to signify that it is a unique index. This helps us identify the properties of the index without having to look at the index definition.
Building a Supplier Location Unique Index
It is always a good idea to build an index on your relationships. Since our Supplier has a relationship to many Locations, we should build an index on the Location object for the supplier. We also want to make sure that the supplier and Location.name is a unique combination.
We can build the following index on the Location Object to satisfy all of those requirements.
This unique index has multiple properties (supplierId and Location.name). With the Unique index checkbox check, the uniqueness is verified across all of the included properties.
Sorting an Index Property
Sorting an index property can help performance if you frequently look at data in the same order. Expanding our sample application, we can add an Invoice Business Object containing Invoice Date as a property. If we frequently want to look at the invoices for a supplier where the invoices are sorted with the most current invoice first, we can build an index to help with that.
Specifying that invoiceDate is not Ascending will eliminate the extra sort step by the database.
Two Specialized Features
There are two specialized features we would be glad to help you with if you feel you need to use them (but that is pretty rare). They are:
- Deferred checkbox: Used for Postgres and only applicable when introducing multi-statement transactions
- Conditional Expression: Used to create a filtered index containing a specific set of data.
If you need help with an index definition, just ask us a question using the chat feature in the lower right corner.