Last week in part 1 we looked at four “outer join” reporting approaches. Two of them involved ExecuteSQL, and I ended that section with the plea: Of course it’s possible that you, dear reader, know some FileMaker SQL voodoo to speed things up, and would be willing to share? Well Dr. Osamu Noda of Japan was kind enough to not only respond, but has provided a pair of demos (Outer Join Demo 5 and Outer Join Demo 6) which are significantly faster and which I am sharing with his permission.
Both of the demos are based on my original files from last week, and as you may recall, the aim was to show a week’s worth of sales for all employees whether they had any sales or not.
You may also recall that 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 is produced by a single ExecuteSQL statement, and looks like this:
(This was covered in extensive detail in part 1, so I’m glossing over it now.)
The only significant difference in this week’s files is how that array is produced. The SQL statement in demo 2 was written thus:
…and I’m embarrassed to say, contains flawed logic which may account for its terrible performance.
But Dr. Noda’s approach uses SQL “Case” statements and produces results a) about eight times faster (!!!), and b) which happen to be correct.
Interestingly page 36 of the FileMaker 12 ODBC/JDBC Guide says that LEFT OUTER JOIN will not work, but Dr. Noda’s code appears to indicate otherwise.
He also pointed out that if I was willing to give up the requirement that employees with no sales during the week be shown, that a WHERE clause would further boost performance. You can see this in his second demo.
The bottom line: ExecuteSQL is not as fast as the native FileMaker relational approach used in the Outer Join Demo 3 from last week, but a properly formed left outer join query is sizzlingly fast compared to my previous malformed attempt. Thank you very much Dr. Noda for the impressive demos.