Preferred approach to a matching process

Question

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

Answer 1

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.

Answer 2

Your approach seems sound. Moving data between the persistence/application boundary tends to be the biggest time consumer. By limiting that data with queries that are guaranteed to match, you reduce the amount of data you're transferring while still keeping as much business logic out of your data access layer as possible. And I'm a big believer in keeping business logic in the business layer and not in the data layer.

If you find that you're still pulling back lots of data and it does actually cause a performance issue, you can look into parallel processing and asynchronous tasks... take advantage of the hardware you're running on. You do have the benefit, though, that each rule you impose reduces the amount of data that each subsequent rule has to process. If you have a way to identify rules that eliminate the largest amount of data, you can run those rules first.

And if you're pulling back a lot of data and really, really have to squeeze performance, you might use a temp table instead. Do your initial pull into a temp table, then push set-based commands at it to reduce the volume of data. (Do NOT iterate over a temp table. Use set-based operations. That's the use that databases are designed for; databases aren't procedural tools.) I don't like this because it puts business concerns in the data layer. So I would only recommend this if 1) you're having performance issues and 2) you've identified bandwidth as your bottleneck.