I am continuing to work on an ESS integration project and one of the biggest issues is speed when dealing with large record sets from MySQL.
So my big idea was to use a view or maybe a temporary table to reduce the amount of data moving from MySQL to FileMaker. MySQL is far quicker in running a select on a large number of records than a FileMaker find so getting MySQL to do the heavy lifting makes sense from both a speed of query and reducing the amount of data being moved around.
The first big choice is a view or a temporary table.
- Both temporary tables and views are specific to the user that creates them. While this sounds pretty inconsequential it is important from a FileMaker point of view. Remember that FileMaker server only creates one connection to the ESS datasource and routes all traffic through that. So while MySQL developers are keen to use views and temporary tables as a security device that is of less use in a FileMaker environment.
- Temporary tables can only be created through a query while views are stored in the schema. Effectively this means to use temporary tables you will need to find a way to execute the required CREATE TEMPORARY TABLE by the logged in user. Not easy when using ESS.
- Views allow the use of calculations, joins and unions in the creation of the table. Effectively this means it is possible to create sub summaries or other calculations and display the results.
Effectively this really pushes the above pushes us down the view path.
The simplified SQL for creating views is:
CREATE VIEW view_name [column list] AS select WITH CHECK OPTION
Generally you are always going to want the ‘WITH CHECK OPTION’ while working in FileMaker. The specific issues this addresses are discussed later in the gotchas.
The real power of the view becomes pretty obvious as soon as you see the ‘AS select‘ part of the SQL. Imagine we are a manufacturing company fulfilling the requirements of a number of customers. One of these wants us to take orders directly from their MySQL database. Pretty easy with ESS you say. And sure enough when you write the code it all works fine. But six months down the line when the table has 500,00 records it all grinds to a horrible halt.
The basic table structure looks like this:
Select * from Order
We are only interested in taking OrderID into FileMaker where the processed flag is 0 so the SQL for our view looks like:
CREATE VIEW order_unprocessed_view AS SELECT Order.__KP_ORDER, Order._kf_Contact, Order.d_FileMaker_Processed_f from Order WHERE Order.d_FileMaker_Processed_f = 0 WITH CHECK OPTION
So our view looks like:
Select * from order_unprocessed_view
So the big question is how much time does this save? In order to test the speeds 2 million records were inserted into a MySQL database. Five of these were flagged as 0, the remainder 1. The view loaded into a table in FileMaker instantly, using a script to go directy to the layout, perform a find and display the records was taking around 5 seconds.
- First, remember to check the views checkbox. This didn’t really confuse us for 15 minutes!
- Views do not always update the underlying tables. MySQL does a nice job of automatically allowing this where it can but there are limits. For example an aggregate (ie sub summary) view is not going to be able to update the underlying table as the aggregate function has merged a number of rows. There is no way for the MySQL server to know which actual record needs to be modified. Similarly if you define a field in the view as table.value1 + table.value2 then there is no way to modify the underlying fields. The rule of thumb here is if you can see a one to one relationship back to a real table and you have not applied any mathmatical functions then updates are possible.
- Inserts (ie creation of records as distinct from updates) are possible but again there are rules.
- There is an option within the creation of views that will not let you enter a row into the view that will not be updated or inserted into the underlying tables. This might be a two edged sword. There are circumstances where updating the underlying table is not required and you are happy to create records in the view that are lost when the session is dropped. If you us the WITH CHECK OPTION in the creation of the view then you will only be able to update and insert within the view data that will be written to the underlying tables.
- As soon as you set the record contrary to the view conditions it will disappear from the table. So in the above example setting the processed flag to 1 would be the last operation applied to the records.