I spent some time this weekend doing some old fashioned FileMaker hacking that I’ve always enjoyed (viva La WorkAround Pro). Definitely inspired by Todd Geist’s very cool hyper-list stuff, but he had also reminded me of the problem of ExecuteSQL on the found set, and I’ve heard this as a common feature request. My big take away from Todd’s comments was that the found set is a uniquely powerful thing, so if it could somehow be incorporated into FMSQL, then that would certainly be a potentially powerful tool. Let me say right now that I did get this to work, but the results are such that it’s really not a practical solution except in maybe some edge cases. I can proudly say that it is neither as fast (edge exception) or as well-abstracted as hyper-list, however…
When I was in Woodworking school we were given a demo on creating an initial flat edge using a table saw. It was an elaborate jig set up, safety was a bit questionable and took a little practice to get right. Some of us were scratching our heads as the joiner is a dedicated machine for doing just this task. We would all have access to joiners and they work about 10 times better that this elaborate table saw “hack.” (more…)
18 Comments
Copy All Records script step -> into a variable (will need a plugin for that) then use list in IN clause.
Hi Ramon,
Thanks Ramon,
Right, this is very fast! and you can paste into a global field and reference that without a plug-in, although it’s not very portable. Unfortunately, getting the ids for the WHERE clause is not the problem it’s the execution of the SQL itself that is so slow once that list of IDs gets long. Maybe chunking the list of ids into smaller groups and making multiple ExecuteSQL calls would be a way to make this faster? I’ll have to try that some day!
Cheers,
-Jason
Yep that might be a good idea 🙂 In some solutions I just “replicate” the SQL to match the Filemaker found set, but that is high maintenance. Must say I’m getting the urge to click on that loop step and give a try.
Hi,
Did you experiment with FM13 ListOF(RecordID) where RecordID is a calc filed that does Get(RecordID) ?
Hi Vincent,
Thanks!
Right, that might help a little, but getting the list of IDs for the WHERE clause is not the slow part. It’s the execution of the SQL itself that’s so slow.
FWIW, FileMaker Pro 12 seems to have a hard limit on the number of OR operators that can be used in the ExecuteSQL function. I get a consistent crash if I use more than 4,629. There’s also a limit for AND operators, but it seems to be somewhere above 22,000. That’s plenty for typical usage, but be careful when working with larger snapshot files.
Hi Greg,
I didn’t run into any of those limits with these tests. With 25,000 records it was basically one Big IN clause with a few thousand OR BETWEENS. When I ran the straight Single IN clause with 25,000 here: http://www.seedcode.com/sql-and-the-found-set-part-2-recordid-list-and-hyperlist/, it was slow but did complete. Do you know what the limit of literals in an IN clause is? Been meaning to test someday, but seems like something you might know =)
-Jason
The limit on literals with an IN operator seems to be somewhere around 500,000, at least with integer values. I’ve seen both ? results and crashes beyond that. The results are inconsistent, so there may be other factors, such as the length of the values or perhaps other things competing for FileMaker Pro’s memory.
I’ve been thinking we could use a similar technique to allow FMRPC to work with the user’s found set (and not just the current record or all records), based on some previous tinkering I’d done with Snapshot links. Thanks for fleshing out the non-trivial mechanical details. I’d use a similar approach, but not to drive a WHERE statement for ExecuteSQL, but to drive a subscript that performs a native FileMaker queries. That may or may not scale so well, but it’s worth some additional exploration. Great post!
So what does the ” Insert the contents of this file into the global immediately after exporting it.” entail? What does the URL look like? Does it refer to the path the txt document was saved, or something more simple than that?
Hi Bill,
The export path for the snapshot looks like:
“file:” & Get ( TemporaryPath ) & “snapshot.txt”
and the InsertFromURL path looks like:
“file://” & Get ( TemporaryPath ) & “snapshot.txt”
On Mac I’m substituting out the “Macintosh HD/” so the actual calc looks like:
Substitute ( “file://” & Get ( TemporaryPath ) & “snapshot.txt” ; “Macintosh HD/” ; “” )
There’s going to be a few more posts on this theme and I should have a sample file link on one of the upcoming ones as well.
Right, Temp Path is a great tool as it cleans itself up when you close FileMaker!
Thanks Jason.
I was playing around with it on my FMPro 12 desktop version and finally got it to work when I exported and imported to the Web folder within the FMP application folder.
Now I know about “TemporaryPath” too!
Thanks again.
[…] have one thing (at least) to follow up with on my ExecuteSQL using the Found Set post from a little bit back. This was to see if I could get the RecordID parsing optimized (or at […]
Jason,
This is a fantastic line of inquiry, and kudos for blazing a trail.
I’d guess that this might evaluate much quicker if you could use a simple expression like.
…WHERE id IN (x, y, z)…
Assuming I have this right, then I’d wonder about creating a light native FileMaker structure that would allow us to use List() to harvest a list of either record id’s, or maybe a list of ID’s (whether we’re using UUID’s, serial ID’s, or some other method of generating our primary keys).
There are, of course, lots of ways this can be done, and with some tinkering, we could probably find a few painless ways to do this quickly create a list of unique found set ID’s on demand, ready to be fed to our SQL query. I’d suspect this would have the advantage of creating a more readable query, avoiding the need to invoke InsertFromURL(), and potentially evaluating much more quickly.
Thanks again for starting this discussion. Looking forward to hearing others’ ideas and seeing where this leads.
PS – I love your woodworking example. I have a very similar memory involving a router table and a plank to be leveled. I wasn’t successful, but I still have both my hands, so I’ll call it a win 😉
Thanks Brian, I will have a follow up post along those lines. Basically using a Hyperlist type method to build a single lN clause rather than the combination with the BETWEENS. That does seem to be generally faster, although it still seems to bog down on large sets even with the single IN clause.
It would be nice if FileMaker had an InsertIntoVariableFromURL (base 64 encoding as needed to boot) so we didn’t need the global. For that reason, if you really wanted to have an SQL Query based on the found set, I think using HyperList to build your id list and using that rather than the snapshot method would be a better overall method than this, both for portability and predictability of results…but in that case, why not just use Hyperlist to build the ultimate results that you’d get from the SQL. My guess is that doing that will almost always be faster, maybe having a really large number of columns being the exception.
Hey Jason,
Yes, I agree. I’ve been lamenting the lack of the missing InsertFromURLIntoVariable function for the past few months. And as you say, I keep wishing we didn’t need that annoying global field. And agreed also that something like Hyperlist to assemble these id’s will probably be a better route to take than using InsertFromURL.
I think even in this case, there are likely cases when SQL can do things that are either more powerful or require less overhead than doing them natively. We may want to grab columns from unrelated TO’s, concatenate fields on the fly, do aggregations, or other things SQL excels at.
I still think you’re onto something interesting here. One way or another, linking the current found set directly to SQL feels like a powerful concept. Maybe we’ll just need some time to get a handle on what we can do with it…
I like the idea of testing what it will do as well what it doesn’t do ( or do well)! Keep up the good work.