Filemaker’s ability to connect to any of the major SQL databases without having to do any SQL is a real killer feature but performance can often not be what it might. Often this is caused by Filemaker having to hammer the SQL data source. There are a few tricks that can move a lot of the work onto the SQL side.
I am going to describe a few techniques and demonstrate examples on MySQL. Oracle and MSSQL have the same (and actually substantially better implementations in some cases) features and because SQL is generally pretty safe across the three these examples might even work on all three platforms.
Temporary tables exist as table only visible to the user that created them. When the connection between the user and server is dropped so are all the user’s temporary tables. In all other regards they function as a normal table.
Have a few useful features, mainly in that they intercept the requests if they share a name with a table. For example if we create a temporary table as:
CREATE TEMPORARY TABLE IF NOT EXISTS t LIKE t AS SELECT * FROM t
This will give us a temporary table, exactly the same as the original table t and from a users point of view they will never know they have been changed.
The power of this approach comes from manipulating the
SELECT * FROM t
code to get the MySQL to do some heavy lifting. For example we could narrow a table to just my records with:
SELECT * FROM t WHERE t.user = 'damian'
The biggest disadvantage of this approach is changes written to the temporary table are not written back into the original table. You can work around this using a trigger on insert or maybe consider an updatable view. Both approaches are detailed below.
The SQL purists maybe get a little animated but a view has a lot in common with a temporary table. They are specific to the user/mysql server connection, they are temporarily constructed (ie dropped at disconnection) and can be used to leverage the server to pre select records. The main differences for our purposes are that views are automatically created and can be used to update the underlying data. Views share the name space of tables, which is a complicated way of saying views can not have the same name as a table.
Now the complicated bit. As I said earlier views can update the base table but there are a few caveats. First the correct algorithm has to be selected in the create view statement. The nice thing is if you leave out the algorithm MySQL does its best to allow updating. Second the query has to produce a table with a one to one relationship back to underlying data. This effectively rules out summary functions and unions.
So why do we use views? Well the biggest saving here is getting a sub set of records into a table before Filemaker gets its grubby paws on the data. As you might have noticed Filemaker can be a little slow so moving the queries to the MySQL server is obviously a good thing. Second we can use the view as a method to apply security and safety to an existing MySQL database.
Triggers allow us to run SQL on the server based on events. The main difference between Filemaker and SQL triggers are SQL triggers are fired on events based on the database rather than the UI. Within MySQL there are three events (insert, update and delete) which can fire a trigger, the triggers can be fired before or after the event giving us six possibilities.
Triggers execute an SQL statement allowing you to perform updates or data validation but they can not prevent the event from ocurring.