Jump to content
Sign in to follow this  
Daniel Wood

Portal level filtering performance question

Recommended Posts

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....)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.