Previously we looked at how you can improve script performance with some simple (yet often overlooked) commands. In case you missed the earlier posts on Optimizing Script Performance, read Part 1 and Part 2.
Greetings scripters!
Today, I would like to touch on a slightly more advanced topic and look at keyword searches using ACL Analytics. Like most things in ACL, there are usually more than one way to attack a problem—the same can be said when searching a table a field for a list of keywords. Here are some tips to help you achieve faster results:
1. Use the ALLTRIM function
You might be asking “Why would I need ALLTRIM() in keyword searches?” The answer is simple. Large free-form text fields can contain a LOT of empty space and using ALLTRIM will limit how many bytes of data you need to search, thus saving you valuable processing time. Nesting ALLTRIM within the FIND function will eliminate leading and trailing spaces so that you are only searching the visible data. Let’s say that we want to search a comment field for the keyword “Gift”. Here is an example of what this might look like in ACL:
Here is more info on the FIND function
2. Use the AT Function instead of FIND
Someone recently showed me a great trick – using AT instead of FIND can reduce your processing time by more than 50%! This was surprising to me at first until I realized that FIND is case-insensitive, which means it has to check for every combination of upper and lower case. Since AT is case sensitive, you can nest the UPPER function with it and get the same results. Here is what the previous example might look like if we use AT instead of FIND:
Here is more info on the AT function
3. Avoid the use of conditional computed fields
The above example works nicely if you want to search a field for one word. But what if you have a list of 200 hundred words? The very first way I tried this (and I see many others make the same mistake), I created conditional computed fields using the FIND function and a different keyword in each condition. There are some issues with this approach:
- It takes a very long time to complete
- The keyword list is hard to manage
- You won’t know if multiple words are in the same field as only the first match will be captured
In my opinion the easiest and most efficient way to do a keyword search is to:
- Create an Excel (or Text) file with a list of your keywords
- Import the table into ACL
- Perform a ‘fuzzy’ JOIN between your data and keyword tables. If you don’t know to do a fuzzy join, check out our knowledge base.
- Use AT or FIND in the IF condition of the JOIN to filter for keyword matches
Let’s say we have a table called “My_Keywords” with keyword field called “Keywords” and a data table called “My_Data” with a field “Comments” we want to search. Our code would look something like this:
The resulting table will contain all records where it found a keyword match and the word it matched on. Note that PRESORT and SECSORT are not required during a fuzzy JOIN since we are essentially matching everything in one table to everything in the other table.
Hope you found this helpful and stay tuned for more tips. Happy Scripting!
