Best practices for good Recon Rule design

I have been focusing on recon rules and how they perform for the last few months. Some great dev work has been done to improve the auto matching, but it is also possible to get significant performance gains by implementing good rule design.

To give you an idea of how impressive the gains can be:
The 'worst' performing Domain in our testing only showed an improvement of 14% between the old and new code. After applying some of the tips below to the rules, there was a 50% improvement in the time taken for the rules to execute. These rules were run on large data sets, but the gains are equally noticeable on recon counts of 1000 and less.

New Code, original rules (hh:mm:ss) m1.Large Instance	New code, tweaked rules (hh:mm:ss) m1.Large Instance	% Improvement
4:00:30	2:01:16	49.5%

General Guidelines (in no particular order):

• Specify exact matches in the Command SortOrder for both LHS and RHS rather than in the match Section of the rule.
• Try include at least one exact match criteria - even just the AccountId match
• Sort your SQL queries in the same order you specify the matches.
• Always sort the SQL queries and always sort LHS and RHS in the same way.
• Use (noLock) in the SQL.
• Only return the columns you need, not everything.
• Filter your data sets to exclude blank values, zero amounts and other records which won't produce a match for that rule.
• Use @CurrentDomainID (case-sensitive).
• Limit the data sets to recent transactions. If a record hasn't auto-matched in the last 90 days (or less) it is unlikely to recon now.
• When working with grouped data sets exclude 'groups' of 1 transaction. Grouped data sets make use of the GetKeys command whether the group is one or many and this takes more time. Rather do a separate rule for 1-1 matches.
• Set the priority of your rules - this helps ensure the best matches are made first.
• If values have changed fields ( eg from Details to Reference1), after an initial phase in period, remove the old matching criteria. 'Or' command in the regex will slow down the rule.
• Try get the matching value in SQL and use SortOrder to make the match rather than use Regex in a match definition.
• Limit the number of Recon Rules.
• Review the rule performance periodically. I can provide scripts to do this.
• Make use of Helper Rules.
• Make use of the TemporaryTableCommand.

Not all of the above can be applied to all rules and sometimes a change may make things worse. Use your discretion and always test a tweak first to make sure that you are still matching the correct stuff and that there is actually a performance gain. There are those rules where very specific matching criteria may mean a trade off with performance but try limit them!

If you have other tips please let me know about them. I am always trying to grow the Bank Recon knowledge base.