Large database

[madman411]

Hi all

 

I am about to start implementing some changes to a previous database system I wrote. It is being altered to suit the needs of a different company. One of the primary tables in my solution contains many, many inventory records. In the current system, it can take up to 30 seconds to load screens that reference the inventory table which contains up to 20,000 records (I know, I know...) 

 

I seem to remember reading that FM can run more efficiently if different individual FMP files are created solely to store tables containing large quantities of records, and then have a central file connected to the files containing the records via a relationship. Can someone confirm or deny this? How have you modeled your solution to contain and sort through large numbers of records?

 

In my previous database solutions I have not dealt with managing this many records. Large FMP databases are very new to me.

 

A little more info:

 

- The system is accessed by about 6 employees over a local network. The company has no intention of buying FMP Server until the user count grows.

- I am anticipating the new company to have about 10,000 records in their inventory. Not quite as much as the current system/company has, but I want this version to be more efficient.

 

Thanks to anyone who can offer a little guidance here.

 

EDIT: I realize I may have posted this in the wrong sub-forum. Please move if necessary. Sorry!

[comment]

In the current system, it can take up to 30 seconds to load screens that reference the inventory table which contains up to 20,000 records (I know, I know...)

 

Perhaps you know, but I don't. 20k records is not exactly a large database. So the more relevant question might be what exactly is on those slow-to-load layouts (and does it need to be there).

[James Gill]

Like comment said, 20k records is not a large database and unless you're viewing all 20k records at once for some reason, Filemaker should be able to handle that with ease.  Without knowing more about the solution, this is sort of like shooting in the dark.  To me, this sounds more like an issue with a layout displaying a large amount of unstored calculations rather than a relationship issue.

 

To your question about multiple files being used to speed up performance.  In my experience, I've used multiple files to cut down on ERD complexity, to segment UI tables & data tables, and for additional security.  About the only performance advantage I know of that creating multiple files will get you is allowing you to segment backups.  AFAIK there is no actual benefit in speed that results from storing data sets in separate files.