FileMaker's niche

I have used other databases such as Oracle, Informix, SQLServer, MySQL, Access, etc. I am considering a contract for writing input templates and cleaning up several FileMaker databases. I understand there are 250K records in one of their databases, and I was surprised that they had that large a database without considering another database alternative.

What is the typical FileMaker application? Can FileMaker handle databases of this size without performance issues?

Thanks,

Dorothy
LVL 6
dorothy2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
The typical application is not a file that big, but things have changed recently, so which version are they using?
Also, only sorting and search operations are slow, so if they only enter data to log stuff, they won't suffer from it.
And they may want to keep an excellent user interface which users are familiar with. As far as reliability is concerned, there is no problem as long as they do a regular backup as for anything else. I have bases bigger than that (> 300000rec.)
0
billmercerCommented:
FileMaker is not a SQL-based system, so it's hard to make direct comparisons, but I think of FileMaker as being a middle ground between a single-user/small workgroup database like Access and an "enterprise" database like SQL Server or Oracle.

In my experience, FileMaker handles large tables quite well. I've got a large networked database hosted on FM Server 5. The biggest tables have about 2.5 million records, and I consider tables of ~100-200k records to be medium-sized. The only performance issues with large files are when doing things that affect the entire file, such as validating the file, sorting the entire file, summarizing fields for the whole table, etc. Simple finds are very fast, regardless of the size of the database. Sorting the whole table is very slow, but we almost never need to do that, so it's not an issue. Sorting a found set of X number of records is not noticeably slower for a big file than for a small file.

Version is important. FileMaker 6 and earlier has a physical limit of 2 gigabytes per table. V7 basically eliminates this limit. There are also significant differences between version 7 of FileMaker and earlier versions with respect to how client/server databases are handled, V7 does more on the server side.

As for reliability, I find FileMaker to do surprisingly well. I've actually had more corrupted files that required restoring from a backup using SQL Server than I have with FileMaker.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dorothy2Author Commented:
One last question, and then I'll split the points between the two of you. Thanks for your opinions.

Is record access based on sequential file access or random access? Do the tables have indexes for the fields that you assume the lookup will be based on, like Customer ID? If so, what happens to the search when you choose a field which is not indexed, like customer's countrycode="US". Full table scan?

Thanks again,

Dorothy

0
billmercerCommented:
Record access is random.

FileMaker indexing is pretty similar to indexing in other databases, though simplified. Each field can be indexed or unindexed as you prefer, except of course for fields containing binary data (container fields) and fields that are unstored, calculated on the fly.

Primary keys must be indexed, and anything you plan to search or sort on should be indexed.
Indexing increases file size, so it's usually best not to index fields that won't ever be searched or sorted.

If the field is unindexed, then a linear search through the whole table is required, and this is dramatically slower, as it is as with most databases.

If you don't specify indexing, the default index setting for FileMaker is that each field starts out unindexed, but the first time an index is needed, it will be automatically generated. (I hate this option, so I always change it. I tend to put indexes on just about everything.)

In FM7, indexing is slightly more sophisticated, with two different levels of indexing.

FileMaker's a lot of fun to work with, but if you're coming from a SQL background, it takes some getting used to. FileMaker is a streamlined approach to data, and the user interface is tightly integrated with the database.
0
dorothy2Author Commented:
Thanks to both of you.

Dorothy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.