4 Replies Latest reply on Nov 2, 2016 11:21 AM by fmpdude

    Data Storage


      Hello everyone, i am new to filemaker. i would like to ask a question about the data storage.


      For a database, it will store much data and the number of records will increase annually or several months.


      So my question is, for the normal practice, do the database administrator make a copy for example annually and delete all the records in the database so that the database will not accumulate too much data and run much faster?

        • 1. Re: Data Storage

          In general, no. FileMaker is capable of handling significant data loads when configured properly, and the number of records will typically affect only some operations (such as summary / aggregate processing). Without knowing more details of exactly what you're planning, it's hard to make any specific recommendations.


          But in general, making a new copy of the database every year is harmful for reporting and searching purposes; it's hard to get a report of multiple years or look back through the archives. (Not to mention you have to maintain multiple files and consume more server capacity.)

          1 of 1 people found this helpful
          • 2. Re: Data Storage

            We have multiple tables with tens of millions of records. Sometimes we run into problems where we have done something wrong with the code or don't have things indexed properly, but our solution runs quick most of the time.


            Every now and again we'll find a script that hasn't been properly configured and it'll cause a slowdown. In particular one of the previous designers loved to use loops, but was really bad about checking for the number of records first or for errors. Looping through every record on a 34,000,000+ record table can slow the system down for everyone.


            Just be sure to include things like if (get (lasterror) does not equal 0) and do some sort of error capture, and script escape.

            1 of 1 people found this helpful
            • 3. Re: Data Storage

              Some solutions have a table for current/active records and an archive table. Records from the "current" table are periodically imported into the archive. This can produce better performance and allow you to do things with the current table that you would never do with a table that stores massive numbers of records while still allowing you to produce reports that include older data by doing them from the basis of the archive table.


              I will be the first to admit that this often is a way to "get away with less than optimum design choices" and is not my first choice for how to design a solution, but there are cases where this approach has proved useful.

              1 of 1 people found this helpful
              • 4. Re: Data Storage

                If you have huge data amounts in a reporting table and your reports are too slow, consider a denormalzied data reporting structure (one or more denormalized tables) that creates itself automatically as you use FMP.  You really don't want to have "manual steps" by a DBA when they can be avoided.


                HOPE THIS HELPS.

                1 of 1 people found this helpful