One of the new all-the-rage features in FileMaker 12 is the new ExecuteSQL function. When I heard about this new function and its potential to massively simplify the graph, I decided I did not want to wait to convert my customer’s School Management Solution to FMP12 and elected to use the free BaseElements plugin to access the FileMaker internal SQL API in FileMaker 11.
Some months and many hours of learning curve time later, I was able to successfully implement SQL queries in many areas of my customer’s solution. Since I am using a plugin in a FMP11 solution, I took advantage of the opportunity to use SQL Insert, Update, and Delete queries to create, modify and delete records without having to create any schema to support the actions. As a result, I created very few additional relationships and was able to write significantly shorter scripts as I implemented several new features for the customer.
After deploying the SQL-enabled solution, we encountered a nasty performance issue over the WAN. This solution has a person table with 20,000 records and a related Event table with 80,000 records. Every time the remote WAN user added a new event, FileMaker would freeze for as long as 5 minutes. This issue could not be replicated for LAN users.
Debugging revealed that after the event was added, the very next script step that performed a SQL Select on the Events table was the source of the long delay/freeze.
Here is the original offending SQL code, part of a script step whose intent was to capture the ID of an event for purposes of conditional formatting and data entry control. This query was part of a more complex calculation, however for our purposes the simplified SQL statement is sufficient:
SELECT id FROM Events WHERE id_Person = $personID AND status = 'No' AND method <> 'NonEvent'
In examining this code, I concluded that it could be simplified by adding a field to the Events table, Events::eventIsValid. This is a stored autoenter Boolean calculation that resolved to ‘1’ when the status = ‘No’ and method ≠ ‘NonEvent’. I then added that query to the SQL statement, thereby reducing the query by one condition:
SELECT id FROM Events WHERE id_Person = $personID AND eventIsValid = 1
Unfortunately, this change did not significantly affect WAN performance, as there was still a minutes-long delay when this calculation was evaluated. At this point, in the interest of getting this bug fix delivered to the client as quickly as possible, I elected to abandon SQL as a technique for this particular function in the database. To that end, I returned to a good, old-fashioned, multi-predicate FileMaker relationship that would serve the purpose of finding all of the related event records that were considered valid as per the business rules of the customer.
Lesson learned in this case is to consider all possible solutions when debugging and optimizing a solution. In this case, the new, fancy FileMaker feature would have required more time than was of value to the customer, and so I resorted to a more conventional approach rooted in the pure FileMaker techniques. Ultimately the priority was to serve the customer with a timely resolution of the bug – a simple case of the end result (delivering value to the customer) trumping a focus on the underlying technology.
Stay tuned for a followup to this blog post where I elaborate on the technical solution to the slow SQL query.
Excellent and valuable post, Darren! Reminds that techniques you expect to be faster by nature may turn out to be very slow in certain cases, and techniques you do not consider because you expect them to be slow can actually be the best way in your specific situation.
Thanks again for stopping by HOnza, and for reposting on LinkedIn. Stay tuned for the next post as the technical solution for optimizing the SQL query was quite surprising.
Nice post, Darren. Great reminder that there are many ways to approach a challenge. I am curious – did you test it against the new native ExecuteSQL function? I would expect the result to be the same (presuming the plugin and the native function both access the FMSQL). Yet, I’m often surprised by my wrong presumptions.
Thanks for stopping by Daniel. For this specific customer, they are still on FileMaker 11, and so I did not test within 12. I have made a few other comparisons related to 11 vs 12 SQL performance and did not find a difference. Certainly that is not to say there is no difference in performance, just no difference in the limited test I did (looping a simple select query and returning the time to iterate 1000 times).
Hello Darren,
thank you for sharing!
From what you describe, the reason should have been downloading the complete tables’ data for the SQL function to get to a result on the client (since you use a plug-in, everything must be done client side).
From what I tried to squeeze from FM in Miami, ExecuteSQL will execute on the server whenever possible. This should make a huge difference in your case! The long freeze on the client is most probably caused by the data download over slow connections. Any way to check this?
Regards
Volker
Volker, great hint. I would expect this client will be migrating to 12 in the next few months. That would be a good opportunity to do some comparative testing of plug-in SQL vs ExecuteSQL. Benchmark testing is fun to do, but we have to squeeze it into the cracks of our work load!
Like you, Darren, I also learned this the hard way. My solution utilizes ExecuteSQL() rather than a plugin, but either should be as fast as the other since they all use the same FQL engine.
Regardless of the FQL method, I have found similar slowness over a WAN, though not quite to the same degree as you found. You’d think it wouldn’t matter whether it is over the WAN or not if FMS is doing the SQL queries and only providing necessary data to the FM client.
I additionally have a SQL view that is not indexable. Queries against this are amazingly FAST from the FMP client, but are dreadfully, DREADFULLY slow from a server-side script. This would also seem to point to FMS not doing as much of the SQL processing as we’d think. To resolve this, I elected to NOT add the extra TO’s I was so looking forward to eliminating thanks to ExecuteSQL(); I instead did it the old-fashioned way — switching layouts and performing a find…which is still amazingly quick even against the unindexed SQL view.
Howard, thanks for stopping by and contributing to the conversation. I have continued to use ExecuteSQL in a FMP12 project, and found again that FileMaker techniques were substantially faster. In this case, I was using ExecuteSQL to to populate values into a repeating field for a cross tab report. All the fields used in the SQL were indexed. I elected again to use table occurrences to pull the data. In this project, performance time to load the report mattered to the customer, so speed won out over reducing schema with SQL.
I too experienced a similar issue with executeSQL over WAN, however I found the problem to be an unstored calc ( Which happened to also be executeSQL ) in the table I was pulling data from. As soon as I changed it to stored my speed dropped back to a couple seconds from 20 seconds. In general any unstored calc will drag down your WAN.
Thanks again Brooks for stopping by and commenting. I love that SQL can query unstored calculations. Bummer about WAN performance though. We have several customers for whom we host their databases, so this limits ExecuteSQL usage for us in some cases. In the case of this article, there were no unstored calcs, just allot of records. But really not that many – around 70,000. Follow me on twitter @DarrenBurgess