Improve Performance of Calculated Fields with Dynamic Tables

The next version (2.1) of Xataface includes a new simple, but powerful, feature that can drastically improve performance on tables that include grafted fields - especially when those grafted fields include expensive calculations.   The feature is called "dynamic tables".

A dynamic table is a MySQL table that is generated with the results of a query.  It is dependent on the tables included in the query, so that if any of them are modified, the dynamic table will be deleted and rebuilt.  They are very similar to MySQL views, except that they can perform much better in environments where updates are rare relative to reads.

Let's motivate dynamic tables by way of an example.  Consider a database with a table of contacts, called "current_call_list".  There is also a "calls" table to record all phone calls made to each contact.
The current_call_list itself only contains data pertaining to the contact (e.g. name, phone, email, etc...), but we would like the list view to also include some calculated information such as how many calls have been made to the contact, when the last call was made, what some recent call comments were, and what some of their responses were to questions asked during the calls.

Xataface makes it relatively easy to add these fields as grafted fields using the __sql__ directive of the fields.ini file (for the current_call_list table).  Our first attempt is as follows:



This works.  But it has brought with it some hefty performance baggage that we feel in a big way as the database grows beyond a few thousand records.

The problem is that all of the calculated fields require subqueries and aggregate calculations to be performed on the calls table.

Using a MySQL View

 One solution is to create a view that produces this information, and then use that view instead to obtain our grafted fields.  We could define the view as follows:



Then we could change the __sql__ directive in our fields.ini file to:



Notice that our SQL query has become much more efficient.  Rather than performing aggregate calculations on the calls table, it does a simple left join on our new view.  Generally joins on key fields in MySQL are *very* fast.  In this case, we're cheating a bit by using a view, so we will likely lose a little performance there.

Playing around with the database a little bit more, shows that the performance is marginally better, but still sluggish on larger databases.  It appears that the view still has to perform some intense calculations each time.

Using a Dynamic Table

In our application, the calls table is updated relatively infrequently compared to the number of read requests.   We should be able to take advantage of this fact to cache our calculations whenever the calls table is changed.  This is precisely what a Dynamic table is for.  The Dynamic table generates itself as necessary with the results of an SQL query.  In each HTTP request, it checks the modification of all dependent tables to see if they have changed since it was last generated.  If they have changed, then the dynamic table is cleared out and repopulated.

Let's modify our example to use a dynamic table instead of a mysql view:



In this example, we make use of the xf\db\DynamicTable class.  The constructor takes 3 parameters:

1. The name of the table to create.
2. An array of SQL statements that should be executed to create the table and populate it.  The first statement is usually a CREATE TABLE statement.  Subsequent statements are the queries that populate the table.
3. An array of table names that this table depends on.  If any of these tables are changed, the dynamic table should be deleted and regenerated.

The update() method is where the actual work is performed.

Where do I place this code?

The best place to for this code is somewhere that will be executed in every request, and before the query is sent to the database.  Xataface 2.1 has supports the beforeLoadResultSet() exactly for this purpose.  It is executed just before the result set is loaded, so it gives us a chance to generate the table before it is used.

Since we have named this table the same as the View in our last example (note: we need to drop the view before doing this),  we don't need to make any changes to the __sql__ directive in our fields.ini file.

Since the SQL query that Xataface has to perform is just a simple join between two *real* tables, the application is much more responsive.  Actually an order of magnitude faster.