Mac FM 8.0v2 Extremely Slow Using Shared Database

[Isis]

Have two macs (iMac G4 and G5 tower) both using FM 8.0v1. One was sharing and other was a client to the database over the net. Upgraded both to FM 8.0v2. Performance dropped to unuseable it was so slow in the following way.

With the client FM computer running a script that did a find on 4,669 records from the sharing computer, it finally completed in 5 minutes. Other times i gave up and cancelled.

I tried compressed clone and a repair on the database and the problem was still there.

I decided to go back to 8.0v1 on the sharing computer and the problem persisted. Only after also going back to 8.0v1 on the client did the find on 4,669 records go back to a reasonable 17 secs for first run after opening the database and 6 seconds for second run of same find. On the serving computer, the find takes 12 seconds first time after opening database and 5 seconds the second time. The field being used in the find is not indexable.

Dear Filemaker, please tell me what happened in FM 8.0v2 that makes it behave this way? Has anyone else experienced this type of behaviour?

[Søren Dyhr]

Concurrent filesharing are likely to slow things down. You should never turn on OS level filesharing on the mac running as server!

How come you're forced to make searches in a field that isn't indexable?

--sd

Edited January 4, 2006 by Guest

[Isis]

Concurrent filesharing are likely to slow things down. You should never turn on OS level filesharing on the mac running as server!

File sharing is not a problem. It was fixed by going back to 8.0v1. And in talking with Filemaker tech support today, they acknowledge that it is a known problem with 8.0v2!

How come you're forced to make searches in a field that isn't indexable?

--sd

The field is a calculation on a one to many relationship to another table. Each related record in the other table has a boolean. If the booleans are all true (1), then my calculation field results to a different value. I calculate that they are all true by comparing like so:

if ( sum(relation::boolean) = count(relation::record_id); "X"; "Y")

If you have a suggestion how i can do the same and store the results so they can be indexed i am eager to learn.

[comment]

Since you are talking about version 8, I wonder if the following workaround shouldn't be faster:

Go to Layout [ “Child” ]

Perform Find [ Omit Records; Criteria: Child::Boolean: “1” ]

Go to Related Record [ “Parent” ] [ Show only related records; Match found set ]

Show Omitted Only

Edited January 5, 2006 by Guest
Changed Find criteria to Omit (in case False means empty)

[Søren Dyhr]

Exactly, and before say with fm5, could it be done this way:

http://www.kevinfrank.com/download/multi-gtrr.zip

Which uses Copy All Records - however is there with present version, yet another way to handle this with a CF and GetNthRecord( ...tear this appart:

http://www.nightwing.com.au/FileMaker/demos8/demo804.html

...although this template shows how to make portalized searches, could Rays CF substitute Kevin Franks "Copy All Records" But...but is it worth it? No! the new GTRR(so,mfs) is far more elegant.

--sd

[Isis]

Thanks for your attack on the problem but it doesn't appear to do what i need, at least from what i can make of it. Let me be clearer about the field and the find.

The field's definition is:

if ( sum(relation::boolean) = count(relation::record_id); "X"; "Y")

Then i do a find for "X" in all the records of the parent table. To note, relation::record_id is a unique id for the related records and relation::boolean is 1 or 0 for true for false. So if the count of related records is say 10, and all 10 records have relation::boolean = 1, then sum(relation::boolean) = 10 and so does count(relation::record_id) -- and the value of the field is "X".

Perhaps the recent hot topic "Triggered Stored Auto-Enter Calcs Technique" could be used in some manner to store this calculation into the field? Yes?

On the other front, here is Filemaker's tech Answer in their Tips database updated just yesterday that explains what is going on in 8.0v2:

TI Answer 5678 Knowledge Base

Since you are talking about version 8, I wonder if the following workaround shouldn't be faster:

Go to Layout [ “Child” ]

Perform Find [ Omit Records; Criteria: Child::Boolean: “1” ]

Go to Related Record [ “Parent” ] [ Show only related records; Match found set ]

Show Omitted Only

[comment]

it doesn't appear to do what i need

I don't know about appearances, but it should do exactly what you describe.

[Ender]

Thanks for noting that tech info answer. This is an significant change in the way finds are processed:

ISSUE:

Performing finds on a hosted file that contains unstored calculation fields can result in a larger local temp file.

EXPLANATION:

In FileMaker Pro 7 and FileMaker Pro 8.0v1, all finds on unstored calculation fields were sent to the host.

This was an issue with certain unstored calculations such as calculations that use a Get(StatusAreaState) or something else that must be done locally. Unfortunately, in some more complex cases, the calculation could not run correctly on the host, and the field became unsearchable.

To resolve this, In FileMaker Pro 8.0v2 all unstored finds are now performed locally, which will result in more records being cached in the local temp file.

This applies to FileMaker Pro 8.0v2 with either FileMaker Server 7 or FileMaker Server 8.

This seems to be a big step backwards. Not that I care about the size of the temp files, but running such a search over a slow network will result in very poor performance (just like in FM6 and below.)

It seems these methods for optimizing finds with unstored criteria will be even more essential in this (and future?) versions.

Edited January 5, 2006 by Guest

[Søren Dyhr]

Your scripting doesn't fall under these:

http://www.filemaker.com/help/05-Create%20a%20database44.html

You simply solve your problem inconvenient, when better algorithms exists! You're applying a defaitist view on what filemaker is capable of without considering the workaround.

Earlier on when filemaker weren't as matured as today, were joking remarks suggesting that 20% of filemaker developement is pure coding and 80% is workarounds.

I would guess that any abitrary pluckings in the documentation could prove what ever point you might have, as long as you deliberatly ignore the context it's ment for a.k.a the devils advocate.

I don't know about appearances, but it should do exactly what you describe.

I'm with him, your scenario is that you wish to single out records with portals not having all boolean values set true. Could be uncompeted orders where some of the items in the orderlines already are shipped.

--sd

[Ender]

You simply solve your problem inconvenient, when better algorithms exists! You're applying a defaitist view on what filemaker is capable of without considering the workaround.

Soren, I don't know what you're being all snooty about. This type of find performed fine prior to this update, so using it seems a reasonable thing to do. And I don't think Isis was being defeatist, just looking for a solution.

At least now we know why the behavior has changed, and these suggested optimization techniques (work-arounds) can be explored.

[xochi]

ISSUE:

To resolve this, In FileMaker Pro 8.0v2 all unstored finds are now performed locally, which will result in more records being cached in the local temp file.

Seems like FM should have fixed the problem, rather than using a work-around.

In this case, it would suggest that the size of the FM Client's cache would be critical, but only if you repeat the search a 2nd time. The first time is going to be network-speed-limited.

As for whether using a stored calc is a good idea : It would probably a fantastic idea (showing 10x or 100x speedup), but would only be worthwhile if you can live with the fact that the stored calc will only be accurate immediately after you've update it. For example, if you get new data once a month, you can just update the stored calc monthly, and your Finds will be lightning fast. If the data changes more frequently, you might not be able to deal with it.

Another idea: what about doing the "Find" in the Related table, and then use Go To Related Records to get the matching set in the first table? In some cases that might be faster (for example, if the majority of the boolean data is usually False)

[Ender]

As for whether using a stored calc is a good idea : It would probably a fantastic idea (showing 10x or 100x speedup), but would only be worthwhile if you can live with the fact that the stored calc will only be accurate immediately after you've update it. For example, if you get new data once a month, you can just update the stored calc monthly, and your Finds will be lightning fast. If the data changes more frequently, you might not be able to deal with it.

Given the choice, I would want any field that's involved in a search to be indexed. I think what we're talking about here is fields that are necessarily unstored, like in Isis' example. Another example is an Age calc, that relies on Get(currentdate) to determine its result, and is therefore unstored. I can easily image wanting to search for people in a specific age range.

Another idea: what about doing the "Find" in the Related table, and then use Go To Related Records to get the matching set in the first table? In some cases that might be faster (for example, if the majority of the boolean data is usually False)

I believe this was the gist of comment and Soren's suggestions. And while these methods work in some cases, other workarounds will be needed for things like that Age calc.

[comment]

On second thought, it was probably a mistake to call it a workaround. Quite simply, Filemaker is not good at searching unstored fields (differences between various versions notwithstanding). So perhaps it would be better to call it "recommended practice": switch your search to the source data (which is bound to be stored).

Case in point: your Age calculation relies on a DateOfBirth field. Instead of searching for Age >= 18, search for:

DateOfBirth <= Date ( Month (Get (CurrentDate)) ; Day (Get (CurrentDate)) ; Year (Get (CurrentDate)) - 18 )

Although it might look like a completely different method compared to the script I suggested above, the underlying principle is exactly the same.

[Ender]

Oh--I understand how to handle it, but this is not intuitive, and I can see many developers getting thrown off by this change of behavior. That age calc is a good example, as the process for performing such a find is different from the process usually used for a find: Usually, you have the fields up on the Find screen that you wish to search, go into Find Mode, enter some criteria, hit Continue and perform the find. To not allow searching on this unstored calc would require your find script to preprocess the entry and calculate the Date of Birth range to stick into the find criteria.

[comment]

Filemaker is not intuitive. If it were, developers wouldn't be needed.

To not allow searching on this unstored calc would require your find script to preprocess the entry

In general, I don't believe in allowing users into Find mode anyway, so that's not a big deal. I hasten to add that this doesn't need to apply in all circumstances.

[Søren Dyhr]

Given the choice, I would want any field that's involved in a search to be indexed.

What a relief, I almost came to think that you'd changed practice resently given the oppertunity getting casual, for a while :

--sd