3 Replies Latest reply on Jul 29, 2011 11:43 AM by philmodjunk

    DATABASE SLOW!!!!

    KAmsinger

      Title

      DATABASE SLOW!!!!

      Your post

      Hi All. 
      I am on FMP Server Advanced 11. I am on a Virtual Dedicated Server that has Windows 2008r2 operating system, Core Duo Processor, 1500 GB Bandwidth, 45 GB Disk Space, 3 GB Ram, xenon dual core processors. The database file is around 1.46GB, with around 12,804 records. Each record contains around 200 fields, and on avg 1-4 of those fields contains picture images, or images of documents. And at any given time around 2 users on the database but no more than 4 users.
      In the database I have merged together several different features that I pulled from templates that came with the software. 
      I have a email feature where I can email distribution lists of records or individual records. Also in the email table I have created a Group (folder) system. That has a main group, sub group, that leads to draft emails which all the Group hierarchy has a portal leading to the next portal. Then in the same table I created a note section on "How To" do certain things and processes with the company, that also has this Group (folder) system just like the emails I described above..
      Group Member feature (Created from the Meeting template but not used for meetings used for it's grouping feature)- which this has been duplicated over several times to create several different types of groups. 
      Meaning there is groups that a person (recordID) can be a leader of and people (records on the same table) are a member of their group, this setup joins 2 tables together. Meaning the Group list is in a Group table but it is pulling it's list of members from a contacts table. 
      Then there are value lists that are leaders of groups that people are a member of. 
      Each person's record contains around 8 portals that leads to these different types of groups. And at any given time a person can be apart of anywhere from 3 groups to quite a few groups (10, 15, even 30 groups for 1 record)
      So here is the deal. I have created this very intricate database system all on 1 file that does everything we need it too and keeps everyone organized and all their pertinent info all in one place. 
      However, our biggest challenge has not been resolved is the speed of the the database on a server that is shared over a network. We have been using one service (which I gave the spec's on above), but my boss complains that the database is too slow that she doesn't even use it. So she asked me to research some new companies to host the database file for us. 
      Here is the thing. I have sent out emails to a hand full of companies and the response I have gotten back is that the server we are on should be fast enough, but filemaker is notoriously difficult to optimize using hardware upgrades, however, changes in the database schema design generally yield much much higher gains. Therefore, now I am stumped as I am not sure how to speed up the database without losing all these great features I have setup and almost all the 12,800 records have been sorted into their correct groups. 
      I need suggestions on either how I can break up the database file into multiple files so that the file is not slow or anything else, I am not even sure what to ask, but I am just looking for a way to speed up the database and keep all the features either in 1 file or several files that are connected to each other. 
      Any suggestions would be very helpful!
      Thx in advanced!
      Kara

        • 1. Re: DATABASE SLOW!!!!
          philmodjunk

          You'll need to look at the specific functions that generate the slow behavior. Once you identify them, you can start thinking about changes that speed up your responsiveness.

          The following partial list of questions is just intended to get you started thinking along those lines.

          Is the DB slow to open? (Check for bad external data source references and scripts set to run when the file open that need optimizing)

          Do you encounter major delays performing a find? (check to see if you are entering criteria in an unstored or unindexed field)

          Do you encounter delays during a sort? (Same issue with field storage and indexing)

          Does it take a long time for a layout to update. (You may have summary field, unstored calculations, pop up menu fields, and/or conditional formats that are slowing things down-especially if they refer to very large numbers of records in order to evaluate.)

          Once you've identified the causes, you can look at some fixes. We have a database with over several million records and is at about 4 GB now in size. By respecting the number of records involved, it works well for us even when pulling up a 5 year report that spans millions of those transactions.

          Here's just a few tips:

          If a layout is slow, try simplifying it. Remove layout objects from the layout one at a time and note any changes in performance. When you find one that's a major factor, try to either modify it to evaluate more quickly or live without it.

          Try keeping a layout with summary fields set up with a found set of zero or just a few records as it's "default" found set. You can set this up with the file open in just FileMaker PRo before uploading it to the server. (If you navigate to this layout when large numbers of records are in the found set, you'll be hit with delays while the summary fields update. If you are going to use a find to pull up diferent records, this is just a waste of your users time.)

          See if certain reports can be "pre-calculated" to reduce the number of calculations needed when a report of that type is needed. We pre-summarize certain data off our invoices on a nightly schedule that takes data from over a thousand line items and summarizes them down to less than 20 for the day. That's the trick that let's us look at 5 years worth of monthly totals for a given item without needing to wait a long time for it.

          If you need perform a find that enters criteria in an unstored or unindexed field, enter just the criteria you have for the indexed fields and perfom a find, then return to find mode, enter the criteria n the unstored fields and use constrain found set to reduce this hopefully much smaller set of records to the final group you needed.

          If you have an unstored calculation field that's bogging you down and it cannot be defined to be a stored value, try using script triggers to update a simple data field with the same value. This can be tricky to do, but if you can pull it off, you can then use the new data field in place of the unstored calculation field.

          As a final thought. You might, as a test, try hosting your database using a copy of FileMaker Pro on one of the computers on your network instead of using the hosting service. If you can get much better performance from the local host, that tells you that your database is being slowed down by the speed of your internet connection and/or the hosting service.

          • 2. Re: DATABASE SLOW!!!!
            KAmsinger

            Hi Phil,

            Thanks for the reply.

            Okay, so I am in the process of doing all your suggestions.

            I have found that if I remove all my image containers and then remove all of my group folder systems portals the records move much faster. So to fix the first part of my problem. Is there a way I can create a 2nd database file (not a table in the same database file but a completely separate file in its self) that is only for images, kinda like an image gallery. And have this new file hold the image files, then link it to show the images as a reference file in my current database file?

            Also, is there a way to move the image containers from the first database file to a new database file without losing the images and have them still be connected to the recordID of my current database file? Once I get this part figured out then I am going to try and figure out what to do about the group folder system.

            Thx

            kara

            • 3. Re: DATABASE SLOW!!!!
              philmodjunk

              I don't see where moving the images to another file, (which can be done with Import Records), will change your system performance any. It might even make things slower.

              Are these images "Store by reference"? If not, you might experiment with that option and see if it makes a difference.

              Can you "down sample" the image to make it lower resolution and smaller and still have acceptable quality on your layouts? (That reduces the number of bytes that have to be transmitted to your client in order to display the image.)

              With Store by reference, you may be able to store the images as separate files on a shared directory on your local network instead of in the remotely hosted database and that may improve performance here. This requires mapping/mounting the shared directory identically on all client machines in order for all of them to be able to see the images.