5 Replies Latest reply on Jul 31, 2014 7:37 AM by philmodjunk

    Enhanced Search

    MichaelVoccola

      Title

      Enhanced Search

      Post

           Utilizing the "perform find" and "constrain found set" steps in a search script. The target is a calculation field with various other fields separated by carriage returns. I am currently using a match phrase from anywhere criteria to find the records.

            

           The issue is presented when the search query is multiple words that do not necessarily have a particular order. For example, I might want to find a record based on values in several fields based on several words in the query:

           Example query: audio yamaha 32ch

           a) Is it possible to break the query down into essentially a list of values, with each new value beginning at word start in the text string?

           b) Once the query is broken down into values and defined as a variable in the script, is the find criteria still essentially the same, with records being found when:  TargetRecord::cFilterTarget = *"$SearchQuery"

           c) I may elect to take a particular field in the target records, like the description, and break that text string down into a similar value list to include in the FilterTarget field, does this change anything in regards to the structure of the find request?

           d) Would it faster to use PerformScriptOnServer()  containing an ExecuteSQL() script step to return TargetRecords::RecordID to the client in a variable as a list, set a global field as that variable and navigate to the related records, or are ExecuteSQL() steps performed on the server if the target table is not the current table on the client?

           Note: This is being done on a relatively large database remotely hosted. Speed is as much of a priority as relevant search results and the current speed of the search (performed on the client) without this additional functionality is already on the border of being considered too slow.

        • 1. Re: Enhanced Search
          philmodjunk
               

                    Example query: audio yamaha 32ch

               What result should that produce? A found set of all records where this calculation field has the word

               and not all records that have: "audio" OR "yamaha" OR "32ch"?

               Should partial text matching be permitted such as "yam" matching to "yamaha"?

               Assuming that this is an "And" search you might use the following outline for a search script:

               perform a find for "audio".

               return to find mode, specify "Yamaha" and constrain the found set.

               Return to find mode, specify 32ch and constrain the found set again.

               A looping script can loop through such a list of values in a variable or global field to achieve this result.

               You may find some of the examples in this thread of interest: Scripted Find Examples

                

          • 2. Re: Enhanced Search
            MichaelVoccola

                 Phil,

                 TargetRecords::cFilterTarget, which contains data from fields of the same table like category, model, make and description where cFilterTarget is an I stored calculation with each of those fields on a new line - in a list - would need to contain all of the separate words in the text string "$SearchQuery".

                 In such an example where  $SearchQuery "audio  yamaha 32ch" each of those words would need to be present, partially or completely, in  TargetRecords::cFilterTarget.

                 This would return a record such as follows:

            Make: Yamaha Electronics

            Model: PM-5D

            Category: Audio

            Description: digital audio console with 32ch in, 64ch outbound etc...

                 I will be reading through the link you shared for inspiration.  My concern, however, with the looping constraint of found record a is speed, which beings up the subject of performing a  SQL query on the server and returning a list of  TargetRecords::RecordID for the matching records in a local variable, setting a global field on the client and so on. Does this seem like a more logical approach or am I missing something with both speed and query structure regarding traditional client-side finds?

            • 3. Re: Enhanced Search
              philmodjunk

                   I strongly recommend that you perform some tests and see what results you get as there are a lot of variables that can affect the end result. I would welcome any results that you report back here after performing such tests.

                   Constrains are usually very fast as the criteria specified is only applied against the current found set. So your first find with the first listed value will search the entire table for matching values, but each constrain that then takes place will be performed only against smaller and smaller found sets.

                   I use a variation of this when searching large tables (over a million records). If I have criteria to use for fields in both indexed and unindexed fields, I will first perform a find using only criteria that I can specify in indexed fields. Then I perform a constrain specifying criteria in the unindexed fields. Because the constrain will be applied (in most cases) against a found set of less than a hundred records when I do that, it is many times faster than performing a single find specifying search criteria in indexed and unindexed fields at the same time.

                   So I would expect the looping script with successive constrains to be pretty fast in most cases. Performance results on ExecuteSQL can vary quite a bit with the way that the query is structured and what is used in the WHERE clause and it is actually an Interpreted process slower in most cases than the same query run on other database systems that are SQL based, so I have my doubts that the execute SQL method will be any faster.

              • 4. Re: Enhanced Search
                MichaelVoccola

                     I have created a demo file isolating a solution utilizing Phil's suggestion of looping a ConstrainFoundSet script step. The file is available for download at the following link:

                Sample File

                • 5. Re: Enhanced Search
                  philmodjunk

                       More thoughts on your search. Searching your calculation field may well be slower than putting the criteria into individual fields in your record.

                       The calculation field will have a much larger and more complex index to search (because the index holds unique values from all fields referenced in the calculation for all records in your table) than the indexes associated with the individual fields.

                       Thus a find that puts "yamaha" in a Make field, "audio" into a Category field and "32ch" into a description field may find records significantly faster than specifying this criteria in the "all in one" calculation field.