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.