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.
FMT Staff Note: IF you would like any of Kevin's tip or techniques incorprated into a solution or project contact him at his website below.
Hello and welcome. I’m Kevin Frank, and I’ve been using FileMaker Pro since the late ’80s… professionally since 1995. The expression “hack” has both positive and negative connotations. Here it is defined as “a tip, trick or technique that helps you solve a problem,” and the best hacks are the ones that can be re-used and modified to meet a variety of challenges. Or, put another way, as I read recently in Street Fighting Mathematics, “A tool is a trick I use twice.”
Kevin Frank and Associates provides custom FileMaker database solutions for business, government, education and non-profit clients. With over 20 years experience, we are certified FileMaker Developers with a solid reputation for high quality results. We invite you to call us today for a no-charge, no-obligation phone consultation.
To find many more in depth articles from Kevin and some must have tools and services visit his site at the link below:Website: www.filemakerhacks.com