Wednesday, 25 June 2014

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.




9 comments:

  1. The code sections in the post seem to be missing.

    ReplyDelete
    Replies
    1. They are hosted on Github gist and embedded as inline javascripts. They appear to be working for me. Perhaps it was a temporary outage on github.

      Delete
  2. The code is now showing for me too.

    ReplyDelete
  3. I am having trouble getting the dynamic table to code to run. I set it up as described in my ApplicationDelegate.php file using the function beforeLoadResultSet(). Note that I had to add this line, otherwise I get a class not found fatal error:

    import('xf/db/DynamicTable.php');

    I took that from the sample.php code above, but it was not listed under the Application Delegate example.

    I can tell that the beforeLoadResultSet() function is running and parses the arrays properly by adding a print_r statement at the end of the function. But the table doesn't get created or updated. There are no errors in the server log. Am I missing something?

    ReplyDelete
  4. Make sure you are using the full path to DynamicTable. e.g.

    new \xf\db\DynamicTable(...)

    not

    new DynamicTable(...)

    ReplyDelete
  5. Yes, I have the namespace the same as in the example. I am sure it is hitting the DynamicTable.php file, but it never seems to call the update() function. Do I need to explicitly call update() from the Application Delegate?

    ReplyDelete
  6. That was it! I just didn't know the proper syntax. This line needs to be added to the end of the sample code:

    $contact_call_info->update();

    ReplyDelete
  7. Supports multi level category listing (i.e parent-category > child-1-category > child-2-category > child-n-category, etc.).Ajax Dynamic Category Listing

    ReplyDelete
  8. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you need to watch this video
    right away...

    (VIDEO) Get your ex back with TEXT messages?

    ReplyDelete