2 Replies Latest reply on Jan 14, 2010 12:05 PM by philmodjunk

    Oversized Database



      Oversized Database




      I work for a company that uses a FileMaker database to store all of their job information.  We have been using the system since 2007, and have about 26,000 jobs in our database.  In certain parts of the database this large number of records has brought our system to a crawl.


      I have searched over the interent for ways to speed up a filemaker server, and none of them seem to be making a significant change.  I'm afraid the solution may be that I need to remove old jobs and place them in a backup file.


      My issue is: I would like to have a button on the layout that runs a script to change the layout from showing fields from the main database's table to showing fields from the backup database's table. Is there a way to do this without creating a duplicate layout for the backup?


      Just a little information about how the system is set up : 


      I am using FileMaker Pro 10, on Windows XP, and the databases are stored on a FileMaker Server 9.


      All of our layouts and scripts are stored in one file, and then the data is stored in a seperate file.  The data file is broken into about 5 different tables (The 26,000 records is just one of the tables), for different types of jobs. I am unsure if I should create a seperate backup file, or just create a different table within the data file for backups.


      Let me know if you can think of a way to do this, or if you have any great ideas on how to speed up my system.


      Thank you so much,

      Joe Wegner

        • 1. Re: Oversized Database



          Thank you for your post.


          At what point do you see the system slowing down?  Are clients printing reports of a table where other clients are editing records?  Is there any exporting of records while others are editing?  That is, is there any activity occurring that affects the entire database file while others are adding/editing records?  I need more information.


          A layout is just a way to display information.  If the active and archive data are in separate tables, then you will need to duplicate the layout, and then change the Layout Setup and change "Show records from" to the appropriate table.  Is there any reason why you didn't want to duplicate the layout?



          FileMaker, Inc. 

          • 2. Re: Oversized Database

            I don't know of a way to make a layout switch table references "on the fly", but....


            26,000 jobs doesn't sound like all that large a number of records. Just for comparison, one of my databases currently stores nearly 185,000 invoice records with 344,000 related line item records.


            This file is hosted over the LAN by filemaker server 10, though a year ago, it was hosted by filemaker server 5.


            You might want to take a look at your database design for possible bottle necks that could be removed to improve performance.


            Typical issues that slow a Filemaker system down are:


            Searching and/or sorting on an unstored or unindexed field.

            Summary or calculation fields with aggregate functions that summarize large numbers of records.


            There are ways that denormalize some of your data in ways that reduce the total number of cacluations needed to compute certain summaries. Sometimes that compromise is needed in order to avoid unacceptable delays though such changes need to be looked at very, very carefully first.