Relationship-Based Filters Slow???

[T-Square]

Does anyone know why it is that when I build a layout on a Table Occurrence that filters and sorts my data, the layout loads much slower than if I script the same filter and sort on the base table?

I have a clients/services database where I start with a client, click on a button and show the client's services.

In the old way, I set a global to my client ID, went to the services file, searched for services with the stored ID, and sorted by date. Clunky in code, but the response was instantaneous.

I just tried the new way, using a relationship to filter on the Client ID, and applying the date sort to the relationship. My system has ground to a halt while it sorts through the entire 100K records, 7 records every second. I'm not up to the math right now, but that'll take some 15000 seconds to finish up. I think monkeys sitting at typewriters could sort these records faster...

It is not clear to me why this should happen, although it is frustrating!

David

[Sanjai]

I have had a similar question wherein a forum member had suggested to me that sorting is network intensive.

The reason which I see in your case is probably sorting is much faster if you have the foundset and is slower if you are trying to build the foundset and simultaneously sorting the records. If you find it is true then you should remove the sorting mechanism from the relationship and instead perform a sort script step after the go to related record script step when you are ready with the foundset.

[T-Square]

Further examination of the situation first showed me that my assumption about what records I would view on opening a layout were flawed.

However, when I remedy that situation and use a GTRR to open the new window showing related records only, I *still* find that the relationship-based query is slower. In fact, the sort--while orders of magnitude faster--still takes about 7 seconds to run, and it seems to build this sort *before* it filters the records.

For example, if I have a related recordset of 104 items (out of 100,000), the GTRR using a sorted relationship sorts through at least 70,000 of the 100,000 before correctly loading the 104 records.

I do not understand why FM would sort first and filter last--especially given Sanjai's note about sorting being system-intensive. As a consequence of this unfortunate behavior, I will continue to use my old method.

Thanks,

David

[Søren Dyhr]

Are you using non equijoins to establish the found set??? FM7 have a bug ...which is solved with FM8. So if what you use a multi criteria relationship including some <'s or >'s, do you have to rewrite it to a equijoin using a hashing algorithm.

http://www.clevelandconsulting.com/support/viewtopic.php?t=299

Just be shure to use the last set of CF's...

--sd

[T-Square]

Søren--

Thanks for the info. I'm not certain what is meant by "equijoin", but I assume it means a simple join on two fields that match. The relationship is a simple match:

Clients::ClientID = Delivs::ClientID

The Sort option is checked, and the sort is descending by DelivDate, which is a simple date field. There are a number of calc fields in the underlying table (vestiges of the old ways).

I assumed, however, that a GTRR on this relationship would function essentially the same as a find/sort script. It does not.

David

[Søren Dyhr]

So there is nothing fancy except the sorting??? You need to make a found set another way....Remove the sorting in the relationship def and make data into information this way:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

You might have a view on databases, that stems more from evangelization than a pragmatic approach. Since you expect sorting a full set of data is of no significance to the speed compared to establish a found set and THEN sort this smaller chunk.

Then follows further filtering say by the fastsummaries algorithm or similar. Your problem - I guess??? Is that you have a politicians craving thirst to make data and information synonymous things while databases as such prefere you make the distinction, since processing speed isn't an endless punchbowl to drink from.

No matter how fast your computer is going to be, will chances be that you perform silly things, due to lack of method.

--sd

[T-Square]

Søren--

First, as I said before, there's nothing fancy in the sorting. And as I said before, I already have another way--it's a script that finds first, then sorts. It works, and it is what I will continue to do, since the relationship-based approach doesn't perform as I expected.

Second, the point of my thread is not evangelizing, but determining the pragmatic approach (as you suggest). If it sounds like evangelizing, well, maybe that's because it doesn't seem logical to me.

To elaborate: the issue of the intensity of finding vs. sorting is a well-known one that is not limited to Filemaker databases, and examination of most any discussion about RDBMS use and theory will turn up lengthy discussions about how to optimize your searches by limiting a found set *before* sorting it. I assumed that if I made use of a sorted relationship, Filemaker would filter the result first, and THEN sort it. That's not "silly" as you put it, it's just an assumption I made based on common sense. When I *script* this process, I search first and then sort because it's faster. It's not rocket science. What's evangelical or silly about expecting Filemaker to follow the same (in my opinion logical) approach?

Moreover, it's interesting that if I use this same relationship for a portal within my Client window, the display is instantaneous, but the same relationship takes 7 seconds to load in a new window using GTRR. Why's that? Maybe there's some optimization that occurs in one circumstance that doesn't occur in the other.

Third--the fundamental point of an RDBMS application is not merely to capture data--it's to provide information. My use of a sorted relationship is precisely to turn data into information. I'm not conflating the two, nor do have any "thirst" (politician's or others') to make them synonymous. I am looking at the various techniques available to me, trying them out, and noting when one of these doesn't seem to function particularly well.

My purpose in ducking down this rabbit hole in the first place was to try and eliminate the need for scripted navigation, and take best advantage of FM7's relational capabilities. Forgive me if my attempts to build a better system expose an inconsistency in how FM works. If anyone's evangelizing, it seems like it's you.

David

[Søren Dyhr]

Hi David

First off - I have obviously misread your post here and there, which I appologize. Yes these inconsistencies are a little counter intuitive!

I came to think of Debi Fuchs Whitepaper on Packet watching, which also explains the algorithm portals sorting procedes ...Last summer did I remember that we had some optimization issues with first opening of a layout compared to the following returning to the same again, layouts including scrollbars etc.

What we tried was to make palusible explanations based on Debi's paper, allthough we all have upgraded to FM7 which is different.

Try to read what she found, and then put it on your discoveries ...although you have a newer version as well might some of the core issues remain.

http://www.aptworks.com/downloads/presentations/PacketWatchingPres_v2.PDF

--sd

[T-Square]

Søren--

Thanks for the information and apology. Likewise, I apologize for getting testy.

I'm looking at the GTRR slide, and pondering whether there's something about the "Create Empty Found Set" that might apply, but for the life of me can't figure out what it might be...

I guess that I will have to accept that for some reason the GTRR(New Window) command should be used sparingly, and to script this navigation myself as I always have.

David