ExecuteSQL – Taming the Beast – Part 1

I’ve been getting to grips with the ExecuteSQL function.  To say that I’ve been enjoying it would be partly true – but it can be a little tricky, not without its frustrations, and disappointing in its performance.  BUT, I’m beginning to see just how powerful and useful it could be.  There are a couple of excellent resources that I’ve found very useful:  one is Filemaker Hacks – Kevin Frank’s articles about ExecuteSQL on his blog are excellent. Kevin writes so clearly, and presents the material so well, that even when he’s repeating stuff that I already know (which isn’t often!), it’s a delight to read his articles.  Of particular relevance here are:

Also, Beverley Voth’s “The Missing Filemaker 12 ExcecuteSQL Reference” – there’s a link to this on FilemakerHacks too – here.

Here are some examples of expressions using the function:

Basic List of Field Contents

ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE nameLast = ‘Smith’ ” ; “” ; “” )

This will give a list of the first names of people whose last name is Smith.  A couple of things that tripped me up, even at this basic stage:

  • “PPL” is a table occurrence, not a base table name.
  • ‘Smith’ is case sensitive.

To get around the case sensitivity issue, I’ve started changing all text to lower case within the expression, e.g.

ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE LOWER(nameLast) = ‘smith’ ” ; “” ; “” )

Using Arguments

The next step is to use the query term (“smith”) as an argument, rather than including it in the SELECT statement, e.g.

ExecuteSQL ( “SELECT count (*) FROM PPL WHERE LOWER(nameLast) = ? ” ; “” ; “” ; “smith”)

So now we start to see how ExecuteSQL can take the place of FM Finds.  Want to know how many Sue Smiths you’ve got?

ExecuteSQL ( “SELECT count (*) FROM PPL WHERE LOWER(nameLast) = ? and LOWER(nameFirst) = ? ” ; “” ; “” ; “smith” ; “sue”)

And, of course, the arguments can use fields instead of the literal values in the example.

Basic “Joinery”

There’s been a lot of excitement about the fact that ExecuteSQL can be used for querying related data, without the need for that relationship to be defined via Filemaker’s relationship graph.  This can mean simplification of the graph – often quite complex relationships have to be drawn on the graph just for the sake of a simple calculation of related data.

So, moving on to a basic JOIN, i.e. relating 2 tables and getting related data without (necessarily) having a relationship in Filemaker, say we want to know how many Sessions there are for a given Programme (where a Programme can have zero or many Sessions):

ExecuteSQL(“SELECT COUNT (*) FROM SSN AS s JOIN PGM AS p ON s.id_PGM = p.id WHERE p.id = ‘PGM00002866’ ” ; “” ; “” )

Note how we allocate an alias of “s” to use when we refer to the SSN table occurrence later in the expression, and “p” for the PGM table occurrence.

The “AS” in the previous example is optional, and the whole thing can be rearranged to be a bit more readable, e.g.

ExecuteSQL(“SELECT
COUNT (*)
FROM SSN s, PGM p
WHERE s.id_PGM = p.id
AND p.id = ‘PGM00002866’ ” ; “” ; “” )

And now for a 3 level “find”:

ExecuteSQL(“SELECT S.id FROM SSN s, PGM p, PTI t
WHERE s.id_PGM = p.id
AND  p.id_PTI = t.id
AND LOWER(t.title) = s.FILTERINPUT ” ; “” ; “” )

Performance

BUT (and it’s a big, disappointing BUT), ExecuteSQL can be very slow.  In the last example above, looking for matching text in the PTI table, the calc takes about 6-8 seconds to query a set of about 24,000 records.  That may not be a big deal when running a report, or an off-line process, but on an interactive screen (I was trying to use it for a “spotlight search” function), it’s clearly not good enough.  In this situation, it looks like you’re better off “denormalising” the data (i.e. repeating, in the parent table, the field you want to query).

Advertisements

Tips for Improving Performance Over WAN

I recently posted a cry for help on the Filemaker TechNet, and received a load of helpful responses about how to improve a solution’s performance over a WAN (wide area network, i.e. via the internet, rather than local).

The full thread is here (login required), but these are the main points offered by the ever-helpful TechNet people, especially Taylor Sharpe, Joshua Ormond, David Jondreau and Mike Mitchell:

Unstored calculation fields

This seems to be the biggest issue in some circumstances.

  • The bad news about unstored calcs.  If a calc has to reference other records (e.g. aggregate fields, such as sum, min, ave, etc.), then all the records that contribute to the calc have to be downloaded and cached.  Aggregating data or referencing a lot of related data inevitably impairs performance, and this becomes especially noticeable in a WAN setup, and even more so when the unstored calcs are used in a Find or a Sort.  And EVEN more so when unstored calcs are used to aggregate data from other unstored calcs in related records – e.g. Sum ( { insert unstored calc here } ).
  • The good news.  It would be unfair on the humble unstored calc to say simply that “unstored calcs are bad” – obviously they’re a brilliant, and essential, feature of FM.  And if an unstored calculation references a global variable, for example, the evaluation occurs instantly.  And unstored calcs only evaluate when they are accessed (displayed, referenced by another calc, sorted on, etc.).

Where unstored calcs are causing performance issues, Joshua Ormond came up with these alternatives:

  • When you need agrregates of related data (count, sum, etc.), use a static field, which can be indexed.  This field is updated when records are added, deleted, or amended.  Joshua calls this a “transaction model”.
  • Execute SQL can also help. (This really does seem to becoming the FM Developer’s new best friend…)

“Big data”

Where possible, split data into manageable chunks.  Because FM retrieves the whole record, rather than just the bits it needs, it makes sense to limit the size of those records.  I’ve found this to be true in my solution, where I once had huge “People” table, whereas I now have it split between “People basic” and “People auxiliary”, the latter containing various calcs etc.  So if I just need the basic People data, that’s all I get.  (The “basic” table is still too big, so I aim to split it further in due course, using a properly normalised attributes table.)

Too many relationships

Where a relationship exists purely to retrieve an occasional piece of data from the related table, consider using “Execute SQL” (FM12 +) instead.  Certainly in my solution, the relationship graph, whilst tightly organised along anchor-buoy lines, will be hugely simplified when I move to FM12 and can use this feature.

Layout efficiency

  • Use blank layouts on file open and file close.  It seems that the first thing FM does when it opens a file is “Show all records”, so specifying a blank layout based on an empty table will certainly help.  Likewise, ensure that this same blank layout is the last layout shown when the file is hosted and closed, as this becomes the default startup layout.
  • When switching to another layout to perform a find, go to Find Mode FIRST, then switch to the other layout, thus preventing the loading of an initial set of records to display.
  • Switching layouts is slow.  Again “Execute SQL” can be used to retrieve data which otherwise may require a layout switch.

Seeing what’s going on

To see how much network traffic FM is handling, analyse the packet data going via Port 5003.  Tools such as Base Elements enable detailed benchmark testing of script performance etc.

ALSO see this article from FileMaker itself – “Optimizing Network Performance for Shared Databases“.  Some useful stuff.