A wish of mine is that ScriptMaker™ will get an overhaul and include syntax colouring, amongst many other improvements. That’s a pretty common wish, I think, amongst the FileMaker developer base.

For the last few days, I’ve been working on a few complex scripts that get quite long. Whether juggling long rambling scripts or lots of shorter scripts and maintaining parameters or global variables (some still say globals are the work of the devil), syntax colouring would definitely help with these beasts, hopefully making it easier to identify exit points, variables, etc..

I was cleaning up some comments (I do that sort of thing!) and my mind wandered about how to make them stand out in a different way, particularly parts of the code that I want to come back and decode. I’ve never liked the way comments are bold black and script steps are plain black. Most development environments seem to have comments in grey text as a default setting.

Here’s a FileMaker script with some comments embedded.

The comments don’t really stand out, or, they stand out the wrong way (I want to look at the code, not always the comments).

To improve that slightly, we can put blank comment lines in and indent the comment text with tabs or spaces to move them away from that cluttered left margin (which is my modus operandi):

Obviously this takes up more vertical space, but it’s a lot clearer, I think.

You can also stuff comments in to all sorts of places in script step. Anything that has a specify calculation component can handle a comment wrapped in /* */ or at the end of a line following a //. Try it, there are heaps of places.

But still, no colour syntax.

I did stumble onto something a tiny bit special. Emoji! Mac OS X’s Emoji font is in colour. And guess what, it works!

Even though the effect is limited, you can obviously go too far with your symbols.

Methinks judicious use of the bug and flag symbols and maybe the construction symbol might be useful.

Alas, the feature doesn’t work on Windows, which is a shame. Never mind!

Optimising Expectations

Recently, I rewrote an old script that processes large files to gain speed. At least, that was my intent.

This was a very old script written in the pre-FileMaker 6 days and it had chugged along nicely for all of those years.

One would think that the obvious way to speed this up would be heavy use of variables (which didn’t exist back then) and chomp the data in memory and avoid writing data to fields opening and closing records and the like.

I had a great test file with just shy of 1 million lines of text to process. Given that computers have bucket loads of memory these days, the plan was to load the entire file into a variable (using any of the numerous plugins that do this kind of thing…or in FileMaker 12, of course you can use the Insert URL using the “file:/” protocol) and mash away.

I wrote my script, tested on a small file with 50,000 lines and the script zipped along very nicely indeed. I was very happy with the speed increase and so I loaded up the big file and waited.

Then I waited a bit more.

Then a lot more. I let it run for about an hour or so but I was getting impatient. Based on the first 50,000 line which took less than two minutes, I expected that a file 20 times larger should not take more than 40 minutes. Especially on this quad-core, maxed out with RAM, SSD based firestorming computer that I use (OK, it’s just quick).

Pausing the script with Script Debugger showed that it was barely a quarter of the way through the file.

Time to examine the script for the bottleneck.

Here is the basis of the looping portion of the script.

#                     load the file into a variable
Set Variable [ $file; Value:idma_File_Read( $path ; 0 ; $dataLength; "Raw Data" )  // ]
Set Variable [ $linecount; Value:ValueCount ( $file ) ]
Set Variable [ $counter; Value:1 ]
Loop
    Exit Loop If [ $counter > $linecount                 /* exit condition */ ]
    #                  load the appropriate line into a variable
    Set Variable [ $currentline; Value:GetValue ( $file ; $counter ) ]
    #                  do stuff here
    Set Variable [ $counter; Value:$counter + 1 ]
End Loop

Timings

The file load only took a second – despite the large number of lines, it was only 4.5MB. So I compared how long it took to load chunks of 1,000 lines for the first 10,000 lines versus each 1,000 lines over the last 10,000 lines.

(I initially timed many more chunks over the whole file so I could plot them to work out whether the change in performance was linear, logarithmic or just plain weird).

First 10,000 lines

A simple modification of the scripts to include timings which were written to a table and charted looked like this:

Set Variable [ $linecount; Value:ValueCount ( $file ) ]
Set Variable [ $oldtime; Value:GetAsNumber ( Get ( CurrentTime ) ) ]
Set Variable [ $counter; Value:1 ]
Set Variable [ $lastTag; Value:"" ]
Loop
    Exit Loop If [ $counter > $linecount                 /* exit condition */ ]
    If [ not Mod ( $counter ; $increment ) ]
        Set Variable [ $time; Value:GetAsNumber ( Get ( CurrentTime ) ) ]
        Set Variable [ $timing; Value:$timing & Right ( "      " & $counter ; 6 ) & Char ( 9 ) & $time - $oldtime & ¶ // very basic formatting ] 
        Set Variable [ $oldtime; Value:$time ]
    End If
    #                                 
    #                                 is this step the culprit?
    Set Variable [ $currentline; Value:GetValue ( $file ; $counter ) ]
    Set Variable [ $counter; Value:$counter + 1 ]
End Loop
#                                 
#                                 write results
#                                 
Set Variable [ $counter; Value:1 ]
Set Variable [ $maxloops; Value:ValueCount ( $timing ) ]
Loop
    Exit Loop If [ $counter > $maxloops                  /* exit condition */ ]
    New Record/Request
    Set Field [ data::counter; LeftWords ( GetValue ( $timing ; $counter ) ; 1 ) / $increment ]
    Set Field [ data::time taken; RightWords ( GetValue ( $timing ; $counter ) ; 1 ) ]
    Set Variable [ $counter; Value:$counter + 1 ]
End Loop

and the results are shown in this graphic:

You can see that each 1000 lines of the memory based variable take roughly between 2 and 4 seconds (FileMaker’s timing facility is only accurate to the second).

Last 10,000 lines

Repeating the process for the last 10,000 lines of our sample, by modifying the counters results in a chart like this (sorry for not resetting the y-axis to the same scales on each chart):

Woah! What a difference. Each 1,000 line chunk took roughly 3-5 times longer.

Why

The time is taken up with this step:

    Set Variable [ $currentline; Value:GetValue ( $file ; $counter ) ]

or more specifically, with the GetValue function.

Now GetValue is fast in most situations. Have a look at an article on Daniel’s Weetbix (Thinking outside the loop) which shows GetValue to be part of the fastest solution on that web page.

Without seeing the C++ code behind the function, one is left to postulate that each time you call the function, FileMaker starts at the beginning of the string variable and counts the number of ¶ characters until it finds the one before the line you want and then extracts the line.

This makes sense and also would explain why it takes longer, the more data there is to process. My problem was that I was dealing with very large numbers of lines. (In this “mega/terra/peta” age, a million is still a large number to me). If this is linear, then a top of the head calculation results in the last 10,000 lines of a million line file requiring nearly 1,000 seconds… wow!

Back to importing

Abandoning the large variable and simply importing the data into a work table (this does require a bit of jiggery pokery for random files like I was using) and testing the speeds again is very revealing.

It only took 5 seconds to import the 50000 line file into an unindexed table. Modifying the looping test so that this line:

    Set Variable [ $currentline; Value:GetValue ( $file ; $counter ) ]

became

    Go to Record/Request [No dialog; $counter]
    Set Variable [ $currentline; Value:Table:f1 ]

and then re-running the tests on (say) the last 10,000 records showed a chart like this:

Each step took less than half a second (more or less, FileMaker’s time function granularity means this isn’t the way to measure if you want accurate figures). Allowing for the time to import the data, even with pre-processing the file before the import (so each line goes into a single field without tabs and commas) and post- processing each line as it is copied into the variable (putting back the tabs and commas).

As has been said in many other fora, optimisation is hard work. You have to analyse and try lots of things. You do get a gut feel for what works and what doesn’t, but it’s a wise developer who just doesn’t always assume!