Database Size Swelling Since Moved to 9

[Thirdwaver]

Ok, I've got a dented spot on my wall from hitting my head on this one.

I had a solution hosted on 8 with 8.5 clients. It was running fantastically. We really wanted to lose one of our plugins and take advantage of the external data source thing. So we upgraded to 9, I made a bunch of changes to the UI and schema to implement the external data sources into the solution. It's now being hosted on FMPS 9 and the clients are using 9.0v3.

The problem: My first sign that something was wrong was that it took almost 10 seconds to create a blank record in the main table. That time ballooned to nearly 2 minutes in some cases. The next thing I noticed was that the solution grew in size from 178 MB in FMP8 to over 3.5 GB (with a G) in 9 during the migration. My poor server was puffing and panting and things started imploding.

After messing with it all night, I was able to get the file size down again by saving a clone, then reimporting the main table's data first, then the supporting tables (if I tried to import the supporting data first, then add the main table, the file size would grow from 178 MB to 3.5 GB again). So I got the size back to normal, but here's the catch... every new record to the main table is adding ~500K(!) to the file size so it appears that I've only temporarily mitigated the ballooning problem.

My experimentation showed the following results:

If I opened the 8 version in 8, each new record added 4K.

If I opened the 8 version in 9, each new record added 8K.

If I opened the 9 version in 8, each new record added 536K.

If I opened the 9 version in 9, each new record added 516K.

Obviously there's something wrong with the new schema.

I've eliminated server from the problem because this happens with local copies of the database on my desktop being opened standalone.

I didn't know where to post this because I'm not sure if my schema is messed up or if the external data source tables in the schema are causing the problem or something else entirely. I'm ready to open a ticket with Filemaker, but I wanted to pique everyone's brains here to see if anyone has any thoughts.

I don't have container fields that used to be globals, now marked as non-globals.

In fact, if I export a single record from my main table into a single FMPro file in Filemaker format, and create a new record in that file, the size increases are almost non-existent. If just the main table data exists (23,000 records or so) with the supporting tables containing no records, each new main table record is nothing. It's only if the main table data exists with the supporting table data that each new record is 500K+

I'm stumped. Help! :hair:

[Søren Dyhr]

Although I can't rule out corruption here, are you here approaching the matter from an direction which I wouldn't ...the blackbox'ish! You can't really tell anything without knowing something about the fields and particular the indexes tied to these.

1) The slow rendering could point towards a use of summary fields in non reporting layouts, and in particular if these summaries build on unstored calc'fields.

2) Lookups, where live referencing actually fits the bill are very likely to cause exponential growth in filesize.

3) The spreadsheet metaphor sticks, and is actually tearing the database out of it's realm. Virtue here is to pick the right tool for the job at hand.

Have the system been planned ahead via ERDs and subject spotting in the mission statement etc.? ....we need to know how has the structure been refined. If the solution can flatter itself with a term like "suburban sprawl" in some spots, could it perhaps suggest that total rebuild might be the best option, instead of approaching one of bells and whistles gone out of tune!

It's perhaps not what you would like to hear, but take it as a learning experience to ditch the project, and start from scratch but this time with planning ahead normalizing the structure going easier on chained dependencies.

--sd

[Thirdwaver]

I appreciate your comments though I admit I didn't understand all of them, but I will say that if anything, my 9 version is even more streamlined than the 8 was. I made a serious effort to remove unneeded relationships and fields.

Back to the problem: Further research today revealed that if I changed the filename, the problem vanished. How's THAT for mysterious. I'm not sure whether my solution has some sort of circular reference that goes away when the filename is different, but the problem is gone. Now the only problem I have is that it seems to freeze every so often when trying to access an external data source field. That's the last one to tackle and I'll try to post results on the external data source forum if that turns out to be the cause. Are others having any issues with the Actual Oracle drivers?

Sean