Performance tips for LIKE operator?

[jbante]

Can anyone suggest any tips for improving the performance of FileMaker 12's ExecuteSQL function for queries using the LIKE operator? I was hoping to re-do a technique in FileMaker that included an off-screen find, but when I compare the performance of ExecuteSQL vs. performing a find in a new window, ExecuteSQL takes about 5 seconds and Perform Find takes about 0.1 seconds. Any thoughts? (Before anyone asks the most obvious question, the field being searched on is fully indexed.)

[David Wikström]

I have a solution with some pretty complex selects with multiple WHERE criteria including LIKEs with wildcards, multiple joins, orders by on related tables... and 5 seconds sounds slow based on my experience.

This solution is still in FM11 with a plugin, but that shouldn't really be faster than native FM12, though you never know...

Maybe you can just do a test with one of the plugins (I'm using a custom plugin built with ScriptMaster Advanced for this one, but they all perform equally in my experience, though DoScript an one other seemed to be a fraction faster)?

I'm just now working on a report in FM12 where I do have concerns with speed, in particular with nested selects. I haven't had the time to explore that further yet, but comparing plugin with native (and 11 with 12) would be interesting.

More fundamental questions would be:

How many records are you working with?

What are you selecting: *, an ID, specific fields, a count...?

[fabriceN]

This solution is still in FM11 with a plugin, but that shouldn't really be faster than native FM12, though you never know...

Indeed David, this assumption in erroneous. FMI managed to deliver a native function incredibly slower than what optimized plugins. To my knowledge, the best SQL plug-in is DoSQL. It's incredibly fast compared to some others. If I understand well, the query itself takes the same time for it's the same engine, but the difference is in the result parsing.

Just a tip : it seems that sorted queries are much faster than unsorted ones, which I explain (my own assumption) by the fact that a so called unsorted query has in fact to be re-sorted by FileMaker record ID.

[jbante]

I tested what I was working on with the suggested plugin and sorted SQL variations, and I found that neither makes a substantial improvement in query performance. All 4 variations of SQL query (native vs. plugin X sorted vs. not) were in the neighborhood of 5 seconds ±.05 seconds, and the traditional procedure (new window, find, loop through records to collect data, close window), took 0.1-0.2 seconds.

To answer David's diagnostic questions, I'm selecting data from a table with ~350k records, and I'm selecting an ID field and two name (short text) fields.

[beverly]

Speaking from SQL, even the "big guns" are going to take a hit with the "LIKE" over the "=". fields/columns must be indexed, of course. Are there any fmp functions (and/or SQL functions) that could be used to make the search not have to use LIKE?

Beverly

[jbante]

My goal was to support a type-ahead-type search of records in an unrelated table. The goal of a query is to return an ID and name field where the beginning of the name field matches what the user has typed in a search field; a script can then replace what the user typed in the search field with the returned name, which the user can confirm to make a selection. So if the user types "ab", I need to match "ab", "abc", "abz", "abcdefg", etc. Is there a SQL way to accomplish that without LIKE?

[Vaughan]

Exploded keys have been used for type-ahead interfaces in FMP since FM3 days. The keys are stored calculations so add to the file size, but they make the relationships VERY simple and fast.

[jbante]

Exploded key relationships have occurred to me, and I've used them many times; I was hoping ExecuteSQL might be a reasonable alternative. Does anyone know why a SQL query with the LIKE operator is so much slower than the equivalent find?

[David Jondreau]

My guess is that a Filemaker find without a wildcard uses the index while ExecuteSQL() with a LIKE does not.

[David Wikström]

FMI managed to deliver a native function incredibly slower than what optimized plugins.

Fabrice - I would unfortunately not be surprised if that was true. Have you done any benchmarking, or seen results from tests done by others?

As for the typeahead functionality, I would definitely go old-school on this one. The redundant TOs are certainly ugly, but I doubt if they do any actual harm in most cases.

[Daniel Wood]

Glad I found this topic, though was hoping for a solution!

I have this exact same issue right now, rather than build a laborioius exploded key for this purpose, I want to use executeSQL to basically allow a user to type a wildcard query to search on someones name, eg:

Dan*Wo

would find "Daniel Wood" where * is any abritrary length of string

While executeSQL using the LIKE condition and sql wildcards does work, it is painfully slow. Upon inspecting what is actually happening - EVERY SINGLE RECORD in the table is transferred to me from server in order to carry out this query - just like if I was sorting every record, that is why it is so goddam slow. Server is not bothering to perform the query in this instance, it is shoving off the work to client. Whereas other types of non-like queries are a bit faster (tho still not anywhere near the same as a native find or relationship)

It is my assumption that when LIKE is used, FileMaker Server says "ugh, thats going to require me to do lots of processing, f-that, I'm going to just send you every record instead and you can do it yourself, have a nice day" - exactly the same as if you try to do a portal-filter referencing anything other than hard-coded text.

While an exploded key can be done, the penalty is that keys tend to be big and that can drastically increase record size, meaning when you actually get the results back (eg for sorting, or listing the ID to put into a field) the record size and thus amount of data transferred is much larger, which can offset any actual performance gain in the first place.

I'd like to know if anyone has had any advances in performance at this point with using SQL - native *shudder* or otherwise.

At this point what I think I am resorted to doing is when the user wants to search by name, show them a given/family name field and once they type it in actually go away and do a filemaker find off-screen and spit out the results, far from ideal but them's the cards FM gives me.

[Daniel Wood]

In fact, turns out any use of the "LIKE" clause causes this transfer of all the record data, not just with wildcards. It's like FMS doesn't like like!

[dansmith65]

What happens if you add an unstored calculation field in your SQL query? Apparently performing a FileMaker find on an unstored calc field will cause it to be performed on the server - I wonder if that also works for an SQL query?

You could add a simple calc field that just equals 1, in order to keep the overhead of doing this as low as possible.

[Daniel Wood]

I'll try that thanks Dan - in my experience given a find on unstored calc is brutally slow on large data sets - and reduces server to a crawl and cripples other users (and the cancel button disappears when u click it!) I would bet also that the calc is being carried out on server

I'll see if it works and let you know.