5 Replies Latest reply on Oct 20, 2009 2:38 PM by anode505

    Too many records?



      Too many records?


      Is there a point when too many records will slow down FM?

      I am logging data and getting 100+ records a day in one table and about 50 in  4 others. 


      I know CPU, RAM, and other running apps will have an effect, but just looking for something general. 

        • 1. Re: Too many records?

          You could make a copy of the database, reimport the records in the original (double the records)

          Copy that db version, reimport the records in the copy (4x the records)

          This would give a reliable indication for your database and your computer.


          • 2. Re: Too many records?

            Every record you add to a database will make certain operations such as searching or sorting take longer as there are more records to search and/or sort. The amount of change is so small that individual record additions do not slow the process enough for us slow ponderous humans to be able to notice the difference. Eventually though, you will start seeing progress bars and other indications that sorting and searching your database is taking more time.


            I have a FMS 10 LAN hosted database of over 800,000 invoice records with more than two million line item records in a related table in the same file. While it does take a few seconds more to search, sort or sum up all the records in the table, the response times are still pretty reasonable as long as I don't search/sort on an unindexed or unstored field. There are several tricks you can use to improve response times in database systems with lots of records:


            Use a script to store a value that could not otherwise be stored. In my case, the invoice total, Sum (lineitems::lineitemAmt) is an unstored field. I added a number field to the invoice record and scripted a step to store the computed invoice total into this indexed number field the same time the invoice is printed. This works, because users are not permitted to edit an invoice once it is printed so the data in the record(s) is static from that point forward.


            If your database collects large amounts of static data that you use to view aggregate calculations such as totals and or averages, you may be able to create a secondary table that condenses the data into a much smaller number of records. In my case, we have a 5 year comparsion report that reports monthly totals and averages for a user selected line item over a 5 year time span. If I set up this report to access the data in my line items table, it would have to process data from approximately 3.6 million records. To speed up this report, I created a "summary" table and use an end of the day script that adds one record to this table for each individual type of item listed on one of the day's invoices with the totals for that one item type. Using this table as the source for the report reduces the total number of records referenced down to about 18,000 records and thus pops up considerably quicker. Note: this method is only practical if the summarized data comes from records that will not henceforth be changed by user edits.


            If you have to do a search that specifies criteria in a mix of stored and unstored fields, do it in two steps. Step 1: enter criteria in the stored fields and perform the find. Step 2: enter criteria in the unstored fields and use "constrain found set" to reduce the found set from step 1 to the desired set of records that meet your criteria.

            • 3. Re: Too many records?

              Thanks guys!


              Dave, I like your idea.  Kinda shotgun troubleshooting, *just my style*  :)


              Phil, going by what you've said, I'm not sweating it much  Wil probably ony keep 2 years worth 'on file'.  Also dig the summary table idea.  Gotta look into it.  Boy there's a bunch to learn.  ut its been fun so far and the great help here is quite a bonus!


              (how come after all these years FM doesn't do any graphing? - just a small rant  I'm thinking not to step on the people that make the plug-ins for it?) 


              (Phil, just curious, that server a mac or win based?)

              • 4. Re: Too many records?

                Windows Server 2008 is what we use to host that particular file.


                "(how come after all these years FM doesn't do any graphing? - just a small rant  I'm thinking not to step on the people that make the plug-ins for it?) "

                I can't speak for FMI, but I've seen a number of cases where features of popular plug-ins were co-opted into filemaker (script triggers were possible for years via plug-in to name one example).


                You can do simple horizontal bar charts in filemaker.


                I've exported data into Excel simply to chart it. It's not too awful a method, but built in would be nicer.

                • 5. Re: Too many records?

                  I love Excel.  Use it for just about everything.  At least until now.  FM can do just about everything (though, give a kid a hammer and everything looks like a nail syndrome  here)


                  Thogh exporting the data to Excel si almos counterproductive in this case.  I'm using applescript to put the data into FM.  Could just go staright into Excel. (though putting data in FM via AS is SO much easier then into Excel)