Product Indexes
From EnablerWiki
Created: 26 October 2009. Last Reviewed: 26 October 2009
Enabler uses indexes to assist in rapidly retrieving data from the database. These indexes work just like an index in a book – the search value is located in the index, and the index tells Enabler where in the database to look for the full data record. Indexes are essential to allow data to be retrieved rapidly, however they require work to maintain. Every time new information is added to the database, the index must be updated to tell Enabler how to retrieve that data.
Tables in Enabler may be indexed on many different fields or combinations of fields. For example, the product table is indexed by product code, barcode, description, supplier product code and many more fields. Thus for each product that is added to Enabler, all these indices must also be maintained.
The product table is perhaps the most important table in Enabler, in so far as products are central to most POS operations. As a result, the product table, and to a degree the style table, tend to have a large number of indexes. It is also common, for the product table in particular, to have quite a large number of rows in these tables – product tables with > 600,000 rows are not uncommon.
Because of the large number of rows and the large number of indexes, it follows that if some of these indexes can be disabled, substantial performance gains can be enjoyed when importing new/additional products into the Enabler product database.
A tab exists in the Product Database Options called “Index Options”.
On this tab, you can turn off various indexes on the posprod & posstyle tables. The more products you have, the slower it becomes due to the number of indexes that need to be updated, even if you are not using a lot of them. Turning off indexes that you do not use will have a significant impact on the time taken importing new products.
The functional impact of turning off the indexes differs depending on the index in question. A summary is below. Note that in all cases where it says cannot search from PLU prompt, this also applies to other areas of Enabler as well where we search for a PLU, not just the sales screen (i.e product field on stock adjustments or product labels screens). This is because the underlying product lookup logic has been changed to ignore those indexes that have been turned off.
Product Table (local\posprod.dbf):
Turn off supplier stock code 1 index
-When supplier product code 1 is added as a custom field to the product browse, you will no longer be able to sort the browse results via supplier product code 1.
-Cannot search from the PLU prompt in the sales screen using “.” notation to search for a supplier stock code.
-Centralised Purchase Orders. Cannot validate/resolve a PLU entered using “.” Notation to search for a supplier stock code.
Turn off supplier stock code 2 index
-Cannot search from the PLU prompt in the sales screen using “.” notation to search for a supplier stock code.
-Centralised Purchase Orders. Cannot validate/resolve a PLU entered using “.” Notation to search for a supplier stock code.
Turn off supplier stock code 3 index
-Cannot search from the PLU prompt in the sales screen using “.” notation to search for a supplier stock code.
-Centralised Purchase Orders. Cannot validate/resolve a PLU entered using “.” Notation to search for a supplier stock code.
Turn off supplier code 1 index
-Cannot search via supplier code 1 only in the advanced search screen on the product browse.
Turn off division index
-Cannot search via division code only in the advanced search screen on the product browse.
Turn off class index
-Cannot search via class code only in the advanced search screen on the product browse.
Turn off barcode index
-Cannot search from the PLU prompt in the sales screen using the product barcode contained in the product table. Searching via barcodes contained in local\posbarc is still supported. Note: When using barcodes sent form EEE, they are stored in the local\posbarc table, not the local\posprod.pd_barc field.
-When importing SOH (SC records, ISL-INV.DAT), cannot search for product via posprod.pd_barc when barcode included but no PLU included in the import record. The number of attempts that were skipped is logged to the event log, i.e “Product Quantities (no PD_BARC index) = 113”.
-When generating new products from the style maintenance, and the option to generate a barcode is turned on, we do not generate a new barcode
Style table (local\posstyle.dbf):
Turn off supplier stock code 1 index
-Cannot search from the PLU prompt in the sales screen using “.” notation to search for a supplier stock code of the style.
-No longer an index that can be selected on the style browse.
Turn off supplier stock code 2 index
-Cannot search from the PLU prompt in the sales screen using “.” notation to search for a supplier stock code of the style.
Turn off supplier stock code 3 index
-Cannot search from the PLU prompt in the sales screen using “.” notation to search for a supplier stock code of the style.
Turn off barcode index
-Cannot search from the PLU prompt in the sales screen using the style barcode contained in the style table.
For example, when “division” index is turned off in the example above, performing an advanced search will not allow you to search on division without entering a class, supplier or department to narrow down the search via index first:
The local\posprod.dbf and local\posstyle.dbf files are always conditionally reindexed during the data purge as part of the end of day. This will force indexes turned on/off to be regenerated/removed. You can off course reindex the files manually, do a conditionalindexall or a migrate to update the indexes to what you have selected.
