I am currently in the process of putting together a matching algorithm. The matching process is as follows:

  • Query data is used to perform a "lookup" on a set of reference data in order to determine an applicable id which is then later used in a sales order
  • When the lookup data is queried, quite often more than one result is returned because the query data typically yields a broad number of matches (meaning there is more than one id to choose from to use in the sales order). In order to identify only one unique row, a series of rules are applied (using query data as the source) in order to narrow the results set, for example in the absence of value x then use value y, and so on
  • Once the lookup query result has narrowed sufficiently so as to only identify a single unique row, the id is extracted and a sales order is created

I am interested in hearing of different/preferred approaches to this problem. Currently, I approach this as follows:

  • Call a simple data query to extract data from the "lookup" table on a "like for like" basis comparing values that are guaranteed to match to give an initial reduced results set
  • With the resulting (disconnected) data set, I then apply a custom method to refine the result set further, continually narrowing a query until I find only one row
  • If at any point I narrow in so much as to create a zero count result set, I undo the last applied query and "roll back" to the previous query

In principal I am happy with this approach, but I am also aware that there may be potential performance impacts with large volumes of data. In an ideal world, I would have the up-front data query dependable enough that it returned only a single result, but I am also concerned that I don't want to over-bloat that layer with too much logic surrounding how the various "rules" should be applied.

Can anyone suggest a different approach I should consider?

Thanks in advance

  • 1
    You can do this in a Single SQL Call using Subqueries and SQL functions like Coalesce. – Morons Oct 14 '16 at 17:34
  • @Morons Can you give me any kind of working example please? Taking into account the kind of rules that I've mentioned. Thanks. – Ian Oct 14 '16 at 23:17
  1. Call a simple stored procedure to extract data from the "lookup" table on a "like for like" basis comparing values that are guaranteed to match to give an initial reduced results set. Store these results in a "working" table.

  2. The working table should include an additional column called "Rank" which can default to a high number-- say, 100.

  3. With the resulting working table, apply a custom method to refine the result set further. For each row which passes the more stringent criteria, decrement the rank.

  4. Continually narrow the results, decrementing Rank each time a row passes the filter.

  5. Retrieve the working table, sorting by Rank. Use the first row. Optionally, if the second row has a rank that is very close to the first row, you can display both rows to the user and have him make the final decision.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.