Portal level filtering performance question

[Daniel Wood]

Hi all,

I'm working on a solution in FM12 which requires me to filter a rather large record set (100,000+). Typically for performance reasons I would filter at the relationship level so that only a very small subset of records is returned to me over the network.

However I've heard talk in FM12 that portal filtering is meant to behave much faster and - dear I say it - be carried out server-side?

So in my testing the only way I've managed to get the portal filtering to actually behave the same as relationship filtering in terms of performance is to only reference hard-coded text strings in the filter, eg:

Portal::Field = "Blah"

Now, this is fine for some, but the whole idea here is that the user can define what they filter by. They enter it into a global field currently, however when I change the calc to:

Portal::Field = GlobalField

Performance goes waaay down, and it takes way longer - activity monitor reveals every single record in the relationship is being transferred to me over the network - essentially client-side filtering instead of server-side as in the first example.

The same thing happens if I abstract and put the global into a global variable instead and use that as the filter. It seems hard-coded text is all I have?

Does anyone know of a way to modify a portal filter calc so I can use a user-entered value for filtering, but is being performed server side? I heard some people say that this was talked about at devcon this year and there is a fast way to do this, but I missed that talk, any ideas?

(I've also tried executeSQL, lets not go there....)

[Agnes Riley]

I wonder who has an idea here, cause it's usually you come up with cool tricks. :-)

[bruceR]

Does the server know anything about a user-specified global variable? Guess we will have to await test results.

[mr_vodka]

Hi Daniel.

I am surprised that the global variable didn't work. The theory behind it was that the variable would translate to text and therefore be treated as such in the filtering.

[dansmith65]

Have you tried an unstored calculation field? Let that field reference your global field that contains the user-defined criteria.

I was just reading how an unstored calculation field is the trigger needed to have a find be performed on the server: http://jaketraynham....g-ins-or-can-it

Maybe that will also cause a portal filter to be performed on the server.

[Daniel Wood]

Hi guys, thanks for the replies, unfortunately still having no luck. Tried the global variable & unstored calc, neither worked, also tried making my global field into a non-global, and indexed, no luck.

Here's the odd thing, even referencing the field (global or non global) in the filter calc causes huge performance issues and transfer of all records, eg:

if ( isEmpty ( Global ) ; False ; Portal::Field = "Daniel" )

So even here the global is referenced, but even if it isn't referenced in the actual comparison check it's still slow. I figured Server would see the first condition and that the field is empty and know False for every record - but no.

Also the following fails:

Let ( Var = GlobalField ; Portal::Field = "Daniel")

Global isn't even used in the test, anyways... I guess as far as FileMaker is concerned the entire portal filter calc IS the test.

So I'm kinda stuck here now, Vodka you are right I really would have thought the global variable would be treated differently to a global or non global field, but that is not what I'm seeing - every record is being downloaded from server to client.

The other thing I've tried is executeSQL to grab rowIDs which I know to be obtainable without having to get record contents, but even the executeSQL query is slower than the relationship by a huge amount.

So at this point its looking like it will have to be relationship filtering, which kinda sucks in a way beause I'm having to generate some keys on the child-side to achieve the type of filtering I want, but at least its fast - still would have been nice to use portal filtering, it's just not quite there yet...

[bruceR]

AFAIK for portal filtering, the filter calc alway looks at every record that is a match for the underlying relationship, and looks at them in strictly creation order. Only afterwards does any sorting take place if the relation uses sorting.

[Daniel Wood]

bugger So in other words portal filtering is still useless for wide area network (and in lots of cases local area network) solutions *

*when working with large data sets

[mr_vodka]

On a positive note Daniel, Jon Thatcher did mention that they were looking into getting it working even referencing global fields so FMI is well aware of this shortcoming.

[Daniel Wood]

Excellent, that would be awesome if they could, I would prefer to do the filtering using that as opposed to relationships if speed was comparable, especially if things like Patterncount function were performed quickly server-side, thanks for that heads up.