3 Replies Latest reply on Jun 7, 2013 9:46 AM by disabled_ScottKoontz

    Keeping Year-old Data: Your thoughts


      Hey everyone.

      I'm finishing up today, my 2 full time jobs: Teacher and database developer for 3 schools, 100 users a day. I'm excited to be able to do the database work full time next year within my schools.

      Wanted to ask you about keeping year-old data. I have three tables that have kept attendance, merits and demerits, and behavior consequneces data for the past year. The merit table alone contains oaver 177K records.

      WHat does a developer do? Does he/she (in this case he) keep the data in the table? Does he summarize the data various ways, throw that info in a table and erase all the data?
      I'm just curious what to do: it seems that if in one year over 177,000 records were created, within 3 years, this one table could be close to 600,000 records. Is it wise to keep that many records in a database just sitting there?
      I'm sure your answer will have to do with the need to go back and look at the data from previous years; i have my thoughts on that, i'd like to know yours.



        • 1. Re: Keeping Year-old Data: Your thoughts

          It depends. : )


          If the performance isn't an issue with that many records...just keep it as is. If things start slowing down due to to many records you need to look at optimizing in one of the ways you've suggested. Archive the records or summarize and store that.



          • 2. Re: Keeping Year-old Data: Your thoughts


            Does the number of records slow down the performance when a person logs into the system, or is the slow down only when looking up information through finds, portals, ExecuteSQLs, stuff like that?

            The normal teacher users simply write a new record to that table and see the most current ones (from this school year, this week or last week) through a portal. There are some ExecuteSQL script steps that do finds, but none of those are on the client; all of them are through the server.


            Where does the performance hit come into play: using the system generally, or when a user tries to create a new record , or when records are viewed in a portal?



            • 3. Re: Keeping Year-old Data: Your thoughts

              I'm working with 50 Million records at the moment. FMS 12 on an older Mac Mini, standard cable-internet connection with clients in several states. Here's a summary that may help:


              • Searches on any indexed field with just about any number of resulting set (1 - 1M) are very fast. I have crafted the searches and am using very few portals, but even for remote users most searches are fast.
              • Sorts on the resulting found set seem to be the same speed as the table increases
              • I used portals on the previous version of this DB, and they did not seem to slow with table increases
              • Imports are becoming a little bit slower, but mostly because I'm adding 100K+ records at a time and FMP index management during import is probably the culprit
              • Adding a single record is immediate, and does not appear to slow with larger table sets
              • Logging into the system is very fast, although all speed precautions for the startup script were taken, i.e., single record, form view start-up, no stopping on lists, no ESS, etc.
              • If your portals are fast now, they *should* be almost as fast with 2x the number of records, but this may depend upon how you are filtering your portals. Your users may not even notice a difference.
              • Backups will take longer, but with FMS 12 and faster drives (SSD +++) this should not be an issue.


              It has been more than a decade since I last separated old data into an archive table, mostly because of the advances in computers and FM. In my opinion it is not worth the time to separate, and simply makes it harder for you, the developer, to maintain and allow people to view the archive. Also, if you add new fields to the live portion, you'd have to remember to add to the archive.


              600K records is not a concern for FM 11 or 12 on today's hardware. This would have been a different discussion a decade or more ago.