Hi,

Lehman's is a multi-channel retailer using Microsoft Dynamics AX R2.  Our customer records number in the millions, and when we went live with AX a few years ago our entire customer database was synced to the retail store (EPOS) database.  I was recently asked to improve the performance of the customer search function in EPOS.  It was taking 30 seconds or more for search results to be returned when searching for customers by name, and this would cause EPOS to time out, effectively making search by name unusable.  As I learned more about how search works in EPOS, I built a document that I thought the AXUG community might find useful if anyone is using EPOS in your brick-and-mortar retail locations.  If you have questions or need more details about any of the below, please reply in this thread and I'll see what I can do for you.

About Customer Search in Retail POS (EPOS)

Background

• The POS application uses a set of table-value functions on the retail store database. These functions are created when the database is created using the database utility.  POS calls a function called CUSTOMERSEARCH. This function calls three other functions that search customer, address, and contact information. (GETPARTYBYCUSTOMER, etc.).  These sub-functions return a table of party IDs and CUSTOMERSEARCH unions them together into one result set, which is then used to query the customer, address, and contact tables to create the final results table that is returned to POS, sorted by AccountNum, OrgId, or Name.
• Lehman's determined that the GETPARTYBYCUSTOMER function was the bottleneck when searching millions of customers. Lehman's put in place a full-text index on the DirPartyTable.Name field, which improved the search results performance by an order of magnitude.  (The full-text index is only refreshed at night, so new customers added that day will not be found when searching by name on the same day. They would be found when searching by name the following day.)
• Search is not case sensitive.

Search Schema

The search string is compared to the following customer data.  If any record is found that meets these criteria, it is included in the search results.

• Beginning of account number or org ID
  • For example, if account number is 12345, and the operator searches for 1, 12, 123, 1234, or 12345, it will be found.
• First, last, or middle name/initial, or a combination (in order)
  • For example, Daniel Zook will return results, but not Zook Daniel
  • Note: adding an asterisk to the end of the search term will cause more results to be returned (e.g., Dan Zook will only return customers named Dan Zook, while Dan Zook* will return additional customers, including Daniel Zook)
• Beginning of any currently valid address linked to the customer
• Beginning of any currently valid phone number
  • If the phone number is stored with dashes in AX, the search term must include dashes, and vice versa
• Beginning of any currently valid email address

 

Caveats

• In general, the shorter the search term the more results are returned, and the longer it takes for the search to complete.
• If the search takes too long and times out, POS may show some errors about the off-line database being inaccessible.
  • Note: Attempting the same search again a few seconds later may return results if the first search attempt completed in the background and the cached data is still in memory.
• Due to the cache needing reloaded, the first searches after the database is restarted may take longer and/or timeout.
• In general, it is best to start your search with a longer search string and then shorten it as needed.
• Combining strings (such as a name and a zip code) will return no results.
  • However, you can search for the beginning of the address, e.g., searching for 123 main would include results for customers with an address that starts with this
• Some results may show up that don't appear to include the search string. This is because the results list only shows the *primary* address, phone, and email for the customer, while the search evaluates *all* current (date/time valid) addresses, phone numbers, and email addresses.
• If an N-1010 job is run, the DIRPARTYTABLE will get wiped out and the steps to enable the Full-Text Index will need to be configured again.

 

------------------------------
Daniel Zook
Director of IT
Lehman's
Dalton OH
------------------------------