5 Replies Latest reply on Feb 1, 2015 1:53 AM by Vincent_L

    Is there a more efficient way to search than with eSQL and "LIKE" ?

      Hi,

       

      as a SQL search with the operator "LIKE" is not very efficient (full table scan necessary) are there better approaches if you want to offer a "multiple table general search" like: you type in a phrase like "Luna" and you want to get all companies, employees, products etc. with the string "Luna" somewhere in it.

        • 1. Re: Is there a more efficient way to search than with eSQL and "LIKE" ?
          electon

          LIKE in SQL still can only find records in only one specified table ( except for explicit joins maybe, but in this case the data is not related ), per search.

          This can be a massive undertaking but can be done.

          Searching is easy, displaying the data in Filemaker can be challenging at times. Probably best via a Virtual List technique.

          Web apps that do that "global search" often prefetch the data and do it via javascript filtering or thanks to super fast servers with load sharing, disks in fancy configurations for data fragmenting, special "flat" tables just for searching, etc. This is what enables those techniques to be a relatively fast experience for the user.

          • 2. Re: Is there a more efficient way to search than with eSQL and "LIKE" ?

            Does it mean - "like" can be a burden and there is no better (faster) way to acieve the goal - with a given "normal" server setting?

            • 3. Re: Is there a more efficient way to search than with eSQL and "LIKE" ?
              Mike_Mitchell

              Yes, LIKE is a significant burden - made more so by the fact that SQL statements have to be translated into FileMaker's native binary query language before they can be executed.

               

              electon's suggestion of prefetching or using a flattened table is likely the best solution. Even enterprise-level SQL databases have to resort to tricks like these to produce the sorts of results you're asking about. You can use a batch job that runs on the server at night to perform the flattening, or you can run a script on some frequency to update the warehouse of data to be used in this type of search.

              • 4. Re: Is there a more efficient way to search than with eSQL and "LIKE" ?
                Vincent_L

                There's a commercial FMP module that does this very effectively

                 

                http://www.seedcode.com/fm-search-results/

                 

                I don't know how they do it, but it work great

                • 5. Re: Is there a more efficient way to search than with eSQL and "LIKE" ?
                  Vincent_L

                  Hi Luna,

                   

                  Just a thought. It seems you insist a lot on SQL, but are you aware that you can script regular filemaker finds ?

                  You create a variable $search containing your criteria "Luna"

                  Then you either :

                  (1) - put a perform find script step and you specify the field in which to find and as the criteria you put  $search. But this will only work for the field set up.

                  (2) - put an set error capture ON, then Enter find mode script step, then a set field by name to which you pass the full qualified field name like table::title, and in you put the $search variable in the criteria. Then you do a perform find (without specify).

                   

                  N.B : normaly $search will only contain Luna, but you may miss ThisIsLunaBombShell or 67Luna6798, so if that's important put *Luna*  this will be equivalent to Like % %

                   

                   

                  The 2 approach might better work for your case, which could be solve by doing something like this

                   

                  If you've a multi file solution, you create a file in which in relationship graph, you add all the TO of the tables you want to search.

                  Then using SQL you query Filemaker to get all the names of the field and the table names see here :

                  http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/

                   

                  you can even only get the text fields if you want to (which is more in line with your post)

                   

                  On ce you have that, you create  2 nested loops, for each table TO, in which you loop on all the fields

                  and so, for each field you do the search :

                   

                  either with SQL Like, or if it seems slow to you (I doesn't to me, and I think it uses the indexes contrary to what you say, but it's just my feeling, but of course native filemaker search are always faster).

                   

                  or like in (2), but that will require you create a blank layout in your file for each of the table you want to search, and that each time you go to the correct table with got to layout by name. Be sure to use the freeze window script step.

                   

                  You'll have the results of the search in that field in that table. You need to store the RecordIDs and the TO name, and also the field name, and perhaps the field contents.

                   

                  To store them, first you need to get all the RecordIDs of the foundset in a value list. Using ListOf ig you have FMP13, or using that FileMaker Custom Function:GetNthRecordSet ( fieldName ; recordNumStart ; recordNumEnd ) (if your foundset is limited to 10000).

                   

                  once you have that RecordIDs in a $RecordIDs variable list you create your result row

                   

                  set variable $result  = Substitute($RecordIDs&"¶";"¶";& char(9) & $TOname &  char(9) &  $fieldname & char(9) & $fieldcontents & "¶") // assumimg that the filed contents doesn't contain returns (otherwise protect them substitute($fieldcontents;"§{}§"), and you'll do the opposite when you want to display it

                   

                  set variable $result  = Left($result;Length($result)-1)  // you do this to strip extra ressult

                   

                  and you add that result to the $Results variable which would contains all of them.

                   

                  set variable $Results = Case(

                  not isempty($RecordIDs) and not isempty($Results);$Results"¶"&$result;

                  not isempty($RecordIDs) and isempty($Results);$result;

                  $Results)

                  // you could do List($Results;$result) but it may be slower.

                   

                  You will end up with a list lock like that

                   

                   

                  45676     TableMovie     Titte                    The adventures of Luna

                  67899     TableMovie     Description         Luna, a beautifull  james bond girl, is getting in trouble because criminals want…

                  8945       TableCast       character            Luna Bombshell

                   

                  Etc.

                   

                  One you get this list, you could do whatever you want with it. Most simple is to display it in a table view.

                   

                  The most difficult part is, that if you use the (2) approach, you'll need to create all the blank layouts.

                  So at first i will go will sql like, which seems fine to me

                   

                  but be sure to make it correctly

                   

                  "SELECT RecordID FROM movies WHERE UPPER(title) LIKE '%" & UPPER($search) & "'"

                   

                  this will get rid of the case sensitivity problem.