Slow Performance

[RPM765]

I have a FM relational database program I developed over the last year to summarize and report on company activity. We are a small railroad and I use this to update our records on a daily basis with car movement and revenue data.

The program has been working very well for over a year. However, all of a sudden the data import performance has markedly slowed during the daily data import. What used to take about 90 seconds now takes almost 5 minutes. During the import process I can see the record count "slider" on the left incrementing one by one as it moves through the records. It never used to do that.

It seemed to occur when the number of records in the "cars" table hit 60,000 or so. I'm now at 62,224 and new records are added at the rate of approximately 1,400 per month.

I can understand a small performance slowdown as the number of records increases, because there are more records to sort, check for dupes, etc. However, this was a sudden and major slowdown. No code was changed, nor were any new scripts added when this happened.

Does FM Pro 8.5 have issues when working with over 60,000 records? If so, can anyone recommend a work-around to fix this?

Edited December 6, 2007 by Guest
Change the Subject to Non-Shout

[IdealData]

Are ABSOLUTELY SURE that there were ABSOLUTELEY NO CHANGES? As you say the record slider never used to move.

Provided you don't need any fields on the layout then remove all fields and turn the status are to hidden.

[Søren Dyhr]

Does FM Pro 8.5 have issues when working with over 60,000 records? If so, can anyone recommend a work-around to fix this?

Splayed over how many tables, files ...what relational approach is pursued?

Five years from now, would be a thingy originaly fm5.5 or fm6 solution, what migration strategy followed, an entire rebuild or a drop upon the icon hope for the best. What measures have been done to clear up the file references?

This seems like something comming on to the solution exponentially, could it be the display of summary field elsewhere than specific subsummary reports?

Well, awful lot of questions, you in opinion have forgotten in your description of the problem ... without which the corresponding answers, the entire thing would near worthless all.

It's the same with all tools; garbage in garbage out!!!

--sd

[RPM765]

This program was originally built in FM 7 with a successful upgrade to FM 8 and then 8.5 a long time before this issue came up.

There are 4 tables in this file. 3 of them are related via a CustomerID field. The tables are:

Customers - 21 fields/71 records

Cars - 65 fields/62,224 records

Contacts - 13 fields/117 records

There is also an unrelated table for holding our company data in Global fields.

Company - 13 fields/1 record

There is a program logo (a jpg image) which appears at the top of the screen during the data import. Prior to this issue, that logo simply stayed on the screen in a stable fashion while the records were imported. When this slowdown issue started, the logo started flashing rapidly as the records are incremented.

I am absolutely sure that no code changes were made just prior to this issue beginning. The program has been fulfilling our needs without further development for almost a year and I have not done any programming work in this database for many months.

Edited December 6, 2007 by Guest

[Søren Dyhr]

Could you make a description of the whopping 65 fields in one single table, I would personally be hard stressed to think up so many attributes ... it seems like the table should have been broken up somehow?

I have no doubt that it have worked until now, but one of the reasons for normalizing a data structure is to make it scale endlessly instead of hitting a wall occurring exponentially!

--sd

[David Jondreau]

65 fields doesn't seem unusual to me. And I have absolutely no idea why a sudden slowdown might occur (is it being hosted? could there be network problems?)

If it's just a once a day thing, is a 3 1/2 minute difference significant to your workflow?

[Søren Dyhr]

65 fields doesn't seem unusual to me

No filemaker is actually quite forgiving here, but we're trying to trace down sources for this sudden change, aren't we??

Maintaining and Index of 65 fields can't be as fast as indexing just two(....3):

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

--sd

[comment]

I don't know the answer, but these are the first things I would check:

• Is there validation going on during the import?

• Are there summaries or aggregate calculations that need to performed on import?

It might also help to try and optimize the file. Without an Advanced version, I'd save a clone and import all records into it.