UI/Data Separation - all FMP Files

[stefwef]

I have a quite complex database and decided to separate the interface from the data because my director was requiring many changes and I wanted to avoid repeated imports. None of my books cover this topic -- it literally says to google "filemaker data separation" in the fmp book! I watched this video which made it seem peachy pie but of course does not discuss any of the specific things to watch for:

http://www.filemakermagazine.com/videos/data-interface-separation-discussion.html

So here is what I did: I duplicated my file. I relinked all table occurences to the external file and then deleted all parent file tables. Already just on my computer I noticed certain entries into portal fields were slower. I placed both files on the server. Oh my. DISASTER!

Here is a list of the new problems which were not the case with the original 2 in 1 SERVED file:

- Entering into certain portal fields takes FOREVER. I've noticed this is mostly the case for related records that have multiple occurences or that have calculations in parent that calls to child.

- Finds and sorts take much longer.

- Navigating between records in one same layout keeps saying "sorting x records" and takes FOREVER as a result. I'm guessing this means the child records are being sorted for EACH parent record... This is especially a problem for LIST view, as it sorts x child records for y parent records.

Does anyone know why these issues are happening with external file that were not happening when the tables were all in the same file? At this point I have to figure out whether to debug the system I now have or to revert to the old system which worked just fine but is a headache for upgrades.

I've noticed that deleting unnecessary table occurences in the EXTERNAL file speeds the portal entry issue. Unfortunately deleting them is very tricky as I have many calculations that require them and it's like finding needles in haystacks.

I also use a lot of sorted relationships - is it better to place the sort on the layout itself in this case?

My director and certain interfaces require the sorts so removing them altogether is not an option.

Thanks - any advice most appreciated on this as I think I've bitten off more than I can chew...

[stefwef]

Actually, another difference between the 2 models is that I re-imported data into the new external file. Could it be that the searches and sorts during testing were just that initial indexing - which means I could have hope that subsequent searches and sorts would be faster?

For those who are interested, I made a cheat-sheet of things to consider to optimize performance, as it appears I will have to go down the list now...

- Remove unnecessary tables and relationships (I had noticed this helped)

- Remove unnecessary related fields

- Change calls to files to be relative rather than absolute

- remove as many sorts in table relationships as possible

- remove as many sorts in layout portals as possible

- Index all fields that will be used for searching and sorting

- Index calculation fields wherever possible / avoid finds on unstored calculations

- Limit display of aggregate fields

- Limit use of aggregate functions in calculations

- Avoid scripts that create records that appear in portals

- Remove "flush cache" from refresh scripts wherever possible (this one puzzles me as I noticed portals from external tables / not same file / do not refresh if "flush cache" is unselected.)

- Limit use of conditional formatting

- Limit calculations that call on related fields, esp if relationship is sorted

- Minimize the use of bitmap graphics on layouts

- I read somewhere that globals that are not called by calcs should go in their own table, as all globals in a table have to be loaded when a global is called in a calc. (is this correct?)

Troubleshoot slow layouts further by creating duplicate layout, deleting one element at a time & testing after each change

[dansmith65]

I'm surprised to hear that you experienced such a difference in performance. Like you said, perhaps it was just the initial indexing. You could always try re-indexing fields if you think there is some sort of index issue (I think turning indexing off, saving changes, then turning it on again triggers a re-index).

What does your External Data Source file reference look like? I like to keep it super simple: file:DatabaseName.fp7

[stefwef]

My external data calls are relative (file:DatabaseName.fp7) and are not the issue.

I had been using some "cash join results" scripts which I have now removed so hopefully the "sorting" between all navigation actions are no longer an issue (I have yet to test on server).

I have now noticed that entry is slow (even on client computer) only on one particular layout, but INCONSISTENTLY. Sometimes I get the pinwheel for a few seconds after every entry, and sometimes entry is very repeatedly quick. I cannot for the life of me identify what is triggering the pattern.

I have also created a duplicate layout and taken things off. It appears that the sheer number of fields from related tables is causing the slowdown. If I remove the first set, it goes faster. If I remove the second set, if goes faster. There is no one field in particular that appears to be slowing everything down. There is only one field that has a sorted relationship and taking it off/putting it on does not seem to affect the pattern.

Most of my calculations calling on these fields are unstored or in related tables (hence unstored) except for:

3 valuelists call on 2 (indexed key child) fields from the child table.

One (parent key) field in child table is an indexed calc calling on 1 (indexed key child) field

I also have some counts that rely on unstored calcs.

HOWEVER... Again to reiterate: this issue is not happening in the file with integrated tables....

At this point I think there is some weird caching or indexing issue going on that I do not understand.

[stefwef]

I have now removed ALL calculations that call on fields from the particular table, all summary fields in the particular table and all table occurrences in data file (except for the main one). The entry issue is STILL occurring.

Can I safely assume it's the external data model at this point?

This particular table is the only one for which I have so many table occurences in the interface file (60 occurences).

All entries to any other table portals are going very smoothly.

I'm pretty ready to throw in the towel on data separation. It's a myth.

[David Jondreau]

Hmmm. Most people's experiences with data separation is different than your experience. Perhaps you're still making an error.

"Change calls to files to be relative rather than absolute"

How many calls are you making?

[dansmith65]

What TO's do you have in your data file? Do you have all the ones that used to be there (before separating the files)?

You should only have the TO's you need for calculated fields, validation, loopups, etc. , and value lists.

[stefwef]

I have removed all unnecessary TOs from my data file and only have those that are called on via calculation. I've deleted all scripts and valuelists from the data file, except for 2 VLs that are called in data file calcs.

I have a bunch of valuelists in the interface file that call on fields from data file - should I move those VLs back to the data file and delete form interface file? I was thinking they are part of the interface and as such should be in interface file, but perhaps not...

My file only calls on 2 related files, by file:filename.fp7.

I've removed all calls and relationships to one of the 2 files during troubleshooting, and no change to performance.

I've removed all calculations, summaries, sorts with no change to performance.

My only solution for the one super sticky table so far is totally changing the schema to rely on related record creation order and pre-creating all related records. I still keep all the same calcs, summaries, sorts and what not. Now entry is quick but I end up with 60,000 records in my related table instead of 2,000.

However, I still have slower performances throughout the database when it comes to simply navigating through records. One basic layout with very few portals is one of the slowest to click through records (using the little fmp index card icon). All I can think of is that it's one of the tables with the most records, but as there are absolutely no complex calcs or summary fields in that table I'm a bit stumped as to why that would be.

I also have some scripts that are popping dialogs/bugs that do not occur in the integrated file model.

Since none of these issues were happening in the integrated file, I am left with the conclusion that external data files significantly impact performance - at least when you did not develop with the model in mind. Perhaps if you develop with that model in mind you can adjust for performance along the way. As is, taking a complex file that works great and turning it into a separation model just does not seem to work!

[stefwef]

Well, as my husband likes to say, "now I'm cooking with fire".

So the slow navigation issue stemmed entirely from privileges to data records, which were set to check on whether a global (team) = record's field (team).

When I remove this, all of a sudden my layouts are super fast.

So - I am removing them!

My plan was to use different launchers to set each team / and use the same set of usernames for each. I guess that will not be possible - will have to set team specific usernames instead.

The table slowness was a different issue - and I maintain entirely caused by the external data file...until I am proven wrong!

;o)

[stefwef]

Well, I'm writing a book here, but hopefully this will save someone else a lot of pain.

I found this on the fmp website:

"Also, if the file is shared and the Boolean calculation that determines record access privileges contains one or more global fields, you may be able to improve network performance by moving some global fields into a separate table. "

Talk about slowing down performance! to a standstill!