Outer Joins in FileMaker 12, part 3

I’ve said before, and no doubt will say again, that one of my favorite things about this blog is how much I learn from your feedback and the demo files you send me.

Recently I received a file from Otmar Kramnis of the Hochschule Luzern demonstrating the fastest SQL-based method I have yet seen to solve the challenge we looked at in part 1 and in part 2, and with a few minor modifications, this is the demo we’re going to focus on today:  Outer Join Demo 7

As you may recall, the aim is to show a week’s worth of daily sales totals for all employees whether they had any sales or not.

Or, to restate the problem in more generic terms: we need to show all values from table A, whether or not there are any matching values in table B. This is known as an “outer join”, or more precisely a “left outer join”, since we want to see all values in the “left” table (Employees), whether or not they have corresponding matches in the “right” table (Sales).

You may also recall from the earlier postings in this series that the Week View is a special viewer layout using Bruce Robertson’s Virtual List technique, with eight columns (calculated fields) parsing a 2-dimensional array which looks like this…

…and that this array is produced via a single ExecuteSQL statement. What’s different today, is the way the SQL statement is constructed. Here it uses “sub-selects” (a.k.a. sub-queries) rather than outer joins to work its magic. And given how blazingly fast it is, I don’t think “magic” is too strong a word.

To keep the file size manageable, today’s demo initially contains 20,000 Sales entries, spread across the years 2011 – 2015 and (approximately) evenly distributed between the 20 employees in the Employees table.

When the demo first opens, the week view will be blank, but you can click the Refresh button or change the date via the calendar drop down or one of the little buttons below the calendar to see how quickly the week view populates.

Really, the only method I’ve seen that is faster is the “pure” FileMaker method in this demo from part 1. But with only 20,000 sales records to evaluate, the difference is undetectable (at least locally — I have not yet tested on a LAN or WAN).

How would today’s demo, or any of the demos in this series, perform with, say, 1 million sales records instead of a paltry 20K? Good news: you can find out for yourself if you are so inclined, and to make it easy, you can run this script to add as many more sales records as you wish… in the screen shot I’ve chosen to add 980,000 to bring it up to an even million (obviously the larger the number of records, the longer it will take;  I think it took about ten minutes to create this many records on my system, but I confess I wasn’t really paying attention).

And then if you’re further inclined you can delete all the sales records in any of the earlier demos, import them from today’s demo, and do an apples-to-apples comparison between the various methods. Or I can just tell you that with a million sales records, and testing locally on a fast 10 month old machine… the pure FileMaker method alluded to above takes about 1/3rd of a second to refresh the viewer, and the SQL method takes about 2/3rds of a second to do it.

Update 19 Apr 2013: I have now done some WAN-based performance comparisons between the “pure” FileMaker relational approach from demo 3 and today’s demo 7, both refreshing the week view with a million sales records. Demo 3 takes 11 seconds; Demo 7 takes 16 seconds.