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.
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?
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.