5 Replies Latest reply on Dec 12, 2016 11:14 AM by philmodjunk

    Multiple Tables vs. multiple files performance/reliability

    Wicktor

      I have seen many solutions developed with multiple Tables inside the same file, which is very practical, especially if the number of Tables is limited.

       

      On the other hand, I have seen some very sophisticated solutions made of 3-5 files linked together.

       

      Probably because I started using FM when it was 4, using separated files is still a component of my instinct.

      Updates are easier and faster. And in case of corrupted data, recovery is faster and easier since in most cases do not affect the all solution but only a specific file.

       

      AT the end, the real matter is performance/reliability, especially with > 500k records.

       

      Has anyone tested performance comparing one-file-multiple-tables vs. separated files ?

      By instinct I would say it should be faster with one-file-multiple-tables, but is there a “significant” difference ?

       

      The reason of my question is because, if I have a solution with some 50 Tables, the relationship graph is terrible so I tend to split the solution into 3-4 files, grouping Tables accordingly with their logical use/relationships.

      Victor

        • 1. Re: Multiple Tables vs. multiple files performance/reliability
          RickWhitelaw

          FileMaker doesn't really care if there are one or several files As long as the appropriate tables are on the relationship graph. My current business solution uses 7 files. The Relationship graph does get to ne complicated . . .

          1 of 1 people found this helpful
          • 2. Re: Multiple Tables vs. multiple files performance/reliability
            wimdecorte

            Wicktor wrote:

             

            AT the end, the real matter is performance/reliability, especially with > 500k records.

             

            Has anyone tested performance comparing one-file-multiple-tables vs. separated files ?

            By instinct I would say it should be faster with one-file-multiple-tables, but is there a “significant” difference ?

             

            Reliability is not an issue.  A solution and deployment is going to be reliable because of many factors but not because it hosts one or more files.

             

            Performance can be affected but probably in some unexpected ways:

            - split off the static tables into their own files.  The data that does not change often that is.  That allows FMS to take full advantage of its hard-link backup mechanism

            - you can do also do faster backup restores depending on what exactly you are restoring by replacing just the file that you need instead of having to replace the whole single-file solution

            - if load (# records, # of users, design,...) becomes a big issue you could host some of the files on a second FMS and take advantage of the extra processing power of the 2nd machine

             

            If you are using the Remote Container feature, consider using a separate file with tables that have 1-to-1 relationships with the tables where you'd normally have the container field.  That extra 'container' file would store the container fields.   This is sort of a variant on the 'data separation' model and it allows you to easily update your solution and migrate data without having to touch the container data.

            3 of 3 people found this helpful
            • 3. Re: Multiple Tables vs. multiple files performance/reliability
              richardsrussell

              As Wim says, there are often compelling reasons to have separate files. One that I've found is portability. I've got a single file ("Comms") that processes the living daylights out of street addresses and what I call "electros" (electronic means of communication: phone numbers, eddresses, and website links). It has 2 main tables — "Addresses" and "Electros" — and 2 link (join, merge, etc.) tables — "ALinx" and "ELinx" — that represent combinations of "Addresses" and "Electros", respectively, with whatever "Beings" (people or organizations) table a particular client uses. I can customize the "Beings" table to a fare-thee-well for each client but simply plug in the "Comms" table unaltered at each.

               

              I've also got another file, akin to this, that I call "Universal". It has 3 tables:

              • "Agents" allows the owner to identify its employees, officers, and board members who are authorized to sign letters or other communications and provides a few standard ways of displaying their names, titles, and contact info.

              • "Identities" contains full, familiar, and abbreviated organization names; shipping and mailing addresses; a variety of shapes and sizes for logos; a field for the organization's slogan or motto; basic contact info (phone, fax, eddress, website); some standardly formatted letterheads; sales-tax rate; and miscellaneous other things that apply to the owner's organization as a whole.

              • "PreSuf" has prefixes and suffixes that go with the names of people ("Dr.", "Rev.", "Jr.", "PhD", etc.) or organizations ("The", "Der", "Inc.", "Pty.", etc.) that can be used for drop-down value lists.

               

              Each of these files can just be dropped into a new client's installation without modification and be instantly available for use, without having to create the database-support infrastructure anew each time.

               

              Incidentally, I'd be more than happy to provide copies of these generic files to anyone who'd like a look at them. Just contact me directly at RichardSRussell@tds.net.

              • 4. Re: Multiple Tables vs. multiple files performance/reliability
                Wicktor

                Many thanks to everyone,

                I am glad that I am going in the right way: easier solutions into one file, complex solutions splitted into a few files accordingly with their logical relationships, i.e. cleaner relationship graphs and easier to be periodically updated.

                Good day to everyone.

                Victor

                • 5. Re: Multiple Tables vs. multiple files performance/reliability
                  philmodjunk

                  Allow me to point out a negative to multiple files: It makes managing accounts in Manage | Security much more complex and tedious--both to create, maintain and to debug them when something goes wrong.