Portal Filtering vs Relationship - Confusing Speed Difference

[Angus McKinnon]

The Filemaker solution that I (sporadically) work on is used to manage equipment rentals. 

One thing I've recently been asked to add is a way for someone to type a query word / phrase, and quickly see a list of current rentals that have a matching item on them. 

It's used for a "hire desk" scenario, where someone at the desk can see a customer coming in carrying a chainsaw, say, and they can type that into the search field, and have the customer details appear. 

 

The resulting layout has three portals on it. They are based on a Cartesian (X) relationship between the table with the rental items, and a "Dashboard" table.

Each portal is filtered using Position, so that in the example above, "chain" or "saw" would turn up the customer's order. 

(Position ( <item name>; <search field> ; 1 ; 1 ) >0)

 

Each rental order has a "Date Out" and "Date In", which can be the same in the case of a single-day hire.

My first portal adds a condition to the filtering of "Date Out" = today's date.

The second portal does the same thing, but for "Date In", so we can see any items that are due to be in or out at the time. 

The third portal broadens that a little:

"Date Out" < (Today's date +7)
  and 
 "Date Out" > (Today's date -7)
                   
or
                   
"Date In" < (Today's date +7)
  and 
 "Date In" > (Today's date -7)
                   
                 

So we catch anything that has been moving recently. 

 

So far so good - the results are being returned as I'd expect. However, the first two portals are deadly slow, taking almost a minute to churn before showing their results. Meantime, the third portal is almost instantaneous in comparison. This confuses me, as I would have anticipated that the third portal, which has more conditions in the filtering, would take longer. 

 

Since then, I've experimented by setting up a duplicate of the first portal using a different relationship. Rather than an X operator, I've based on "Date Out" = "Today's Date" (field in the Dashboard table) so that if the portal were left unfiltered, all that would appear are items which are due out today. The Position filtering is still applied to match results to the search field. 
This arrangement is certainly faster than the original filtered portal, but isn't noticeably quicker than the third portal, with its extra filtering. 

Speed is of the essence here, so I'm keen to pursue the method that's going to get me the quickest results. 

Part of the problem might be that the table with the hire items in it has almost 60,000 records, so working through it is always going to take time... I'm genuinely curious what could be making the third portal run so much faster. 

[Angus McKinnon]

I have done a bit more experimentation in the meantime. 

I've duplicated some of the portals, and done the filtering in a different order. 

Using the Position command first seems to make the filtering run much faster. Still slower than the portal based on a direct relationship, but a noticeable improvement. 

Meanwhile, I duplicated the portal that has the broader date filter. Putting the date filter before the Position command slows it to a crawl...

 

It looks to me that filtering via the relationship is going to be the quickest option. But I'm not sure if there's a way of doing the broader date filter that way. I know I can use operators in relationships, but probably not in the way I'd need to get the ±7 days that I'm looking for...

[comment]

There is a fundamental difference between a relationship predicate and a portal filter. A relationship must be based - at least on the "fetched" side - on an indexed field, and the index is then used to determine which records are related. A portal filter, OTOH, must evaluate the filtering expression for all related records every time it is refreshed.

If you have more than a few records, you should always try to reduce the number of records that the portal filter must go through by making the underlying relationship do most of the work. An inexact match such as your filter using Position() cannot be implemented by a relationship predicate, but the date matching can - and should.

5 hours ago, Angus McKinnon said:

But I'm not sure if there's a way of doing the broader date filter that way.

It is a bit more tricky if you want to catch both incoming and outgoing items, but it can be done. For example, you could add a (stored) calculation field in the Items table that lists both DateIn and DateOut*, and on the other side an unstored calculation that lists the 15 dates surrounding today. Then match them using the = operator.

__
(*) Caveat: you must normalize user-entered dates to the format being used by the file.

 

[Angus McKinnon]

Four months on, and I'm back at this again. 

The portals that are an exact date match are working very well. (The relationship returns records to the portal that match the exact date, then the portal filter using Position() handles the text search.)

I'd left off doing anything with the third portal on the layout, which has the broader date filter, but since the layout is getting more use I'm beginning to get complaints about the speed. 

On 11/1/2023 at 4:42 AM, comment said:

It is a bit more tricky if you want to catch both incoming and outgoing items, but it can be done. For example, you could add a (stored) calculation field in the Items table that lists both DateIn and DateOut*, and on the other side an unstored calculation that lists the 15 dates surrounding today. Then match them using the = operator.

This is perhaps a stupid question, but how do I produce a calculation field that has multiple values? Should I use a repeating field? Or is it a list in text format that I'm matching on? 

On 11/1/2023 at 4:42 AM, comment said:

(*) Caveat: you must normalize user-entered dates to the format being used by the file.

Should hopefully be OK on this, because all the dates are chosen via a picker rather than entered by the users. I'm guessing it's safe to assume that FM will be consistent internally, or is there anything I need to pay attention to with the calculations themselves?

 

[Søren Dyhr]

1 hour ago, Angus McKinnon said:

This is perhaps a stupid question, but how do I produce a calculation field that has multiple values? Should I use a repeating field? Or is it a list in text format that I'm matching on? 

Both things work, but the predefined finite number of repeating fields would endanger something not quite included. So custom functions like:

https://www.briandunning.com/cf/892

are more likely candidates ...and which I have used for decades.

But I should mean that a join table, is where each items booking should be stored and where any of the searches should be made. It's so that from FM16 and onwards, did a new summary function, the list of - appear. and it's possible to make this summary field a relational predicate, by first defining the summary field an ordinary text field, draw the relation in the TOG and then switch the field into a summary later. The summary should be made over the foreign keys.

In my view of the matters it's much much faster to locate desired items via the data stored in the join table, and simply issue a GTRR when the found set is established, and from there go to the table with each of the items.

----------

I see that you're on FM15, that would not provide the new summary ... but then would a scripted replace into a global field, probably facilitate this but with a slight drawback in execution ... but portalfilters are notorious slow ... and can probably be beaten by this method, especially if each join table record is tagged with a indexed keyword for the type of tool in question.

--sd

Edited February 2 by Søren Dyhr

[comment]

1 hour ago, Angus McKinnon said:

how do I produce a calculation field that has multiple values? Should I use a repeating field? Or is it a list in text format that I'm matching on? 

When I wrote my suggestion, I was thinking of a return-delimited list of dates (as Text). But a repeating field would work just as well - actually, even better because the result type could be Date and then you wouldn't need to worry about users entering dates in a different format. The formula in the Items table could be:

Extend ( Choose ( Get ( CalculationRepetitionNumber ) - 1 ; DateIn ; DateOut ) )

with 2 repetitions, and for the Dashhboard:

Get ( CurrentDate )  + Get ( CalculationRepetitionNumber ) - 7

with 13 repetitions - and this calculation needs to be unstored.

 

1 hour ago, Angus McKinnon said:

all the dates are chosen via a picker rather than entered by the users. I'm guessing it's safe to assume that FM will be consistent internally

I am not sure about that. I suspect that if the file is set to use the current system's settings, then the date will be entered using the user's OS format even if they use a drop-down calendar to enter it. But I have no way to test this now. In any case, if you choose the repeating fields option, you don't need to worry about it.

 

Edited February 2 by comment

[comment]

Hey, Søren, welcome back!

I am afraid I lost you at this:

49 minutes ago, Søren Dyhr said:

the predefined finite number of repeating fields would endanger something not quite included.

But that's just like the good old days...😀

 

Edited February 2 by comment

[Søren Dyhr]

2 hours ago, comment said:

Hey, Søren, welcome back!

I am afraid I lost you at this:

Thanks - Not your two repeaters there but, it's my view that a lot of these spreadsheet'ish exercises to substitute genuine join tables, has no real meaning with todays ExecuteSQL and script triggers as well as PSOS'ing long scripts executions ... but if you're without Filemaker Server to your disposal is it about time you turn to a FDA licence, where it's included in a crippled 3 user version. It's not just for the ability to PSOS'e but also if you make a mistake in your development, could the backups be restored swiftly, with the loss of perhaps only a few records in a working system.

The issue at hand seems here, to be singling out items, which haven't seen any use in a specific span of time, this is in my humble opinion best done with a request in the join table for getting the records in a specific span of time, then gathering a list of the foreign keys (here makes the new summary sense), followed by a GTRR into the the items table and finally constraining the found set with the final search criteria .... simply because all calc-field shenanigans would make the impatient user frown. 

If the ExecuteSQL feels challenging, then turn to https://www.briandunning.com/cf/1692 and then chatesian relationships between the tables in question, and you could make a solution with hardly any unstored calc's utilized.

 

2 hours ago, comment said:

I am not sure about that. I suspect that if the file is set to use the current system's settings, then the date will be entered using the user's OS format even if they use a drop-down calendar to enter it. But I have no way to test this now. In any case, if you choose the repeating fields option, you don't need to worry about it.

Int( would turn a date of whatever format into 7 digit number, which then is universally understood.

--sd

Edited February 2 by Søren Dyhr

[Angus McKinnon]

I have been meaning to report back on this. 

Using the repeating field in the relationship worked perfectly. It also helped me tidy up another aspect of the solution, combining two portals into one which is a much more streamlined look for the users. 

I've not run any empirical tests, but from a users POV all the portals are loading within milliseconds of each other and seem to be almost instantaneous. 

So thanks again to al. And also hello to Søren - I remember seeing your name crop up when Riley C was leading efforts with Filemaker for rental companies over on the old PSW forums. 

[Søren Dyhr]

9 minutes ago, Angus McKinnon said:

Using the repeating field in the relationship worked perfectly. It also helped me tidy up another aspect of the solution, combining two portals into one which is a much more streamlined look for the users. 

On the combining portals, is this perhaps an even more mind-blowing aspect: 

 

--sd

 

Edited March 15 by Søren Dyhr