4 Replies Latest reply on Jan 20, 2012 5:42 AM by pdoak_1

    Slow database

    pdoak_1

      Title

      Slow database

      Post

      Background Info: I am using the following

      1. Filemaker pro Advanced 11
      2. Mac OS X 10.7
      3. Single user mode
      4. I have been using Filemaker Pro for 1.5 yrs and am very comfortable with scripting

      I have a database which tracks stock portfolios. One table holds all the individual trades for each portfolio and another holds the daily stock prices by date. A field in the trade table holds the current portfolio date which is updated via another table. The trade database looks up the stock price for the date specified by the portfolio date and then performs calculation based upon that stock price.

      Although the data base works well it is becoming slow as there are a lot of calculations which go on in the trade database based upon the price. When I look at layouts it can take a while for the data to display and can be extremely slow to scroll down the page of the layout.

      I would like to find a way to increase the speed and the only way that I can think of is by copying the calculated values from the trade database on a daily basis into another table which only holds static data ie. this new table would hold the static values by date. I could then view all data from the static table only thereby increasing the speed as there would no longer be on the fly calculations being performed.

      Firstly, is the best solution to the speed problem and secondly if it is what is the best way to copy the data? I could write a script to take each row and copy the individual fields but that is also is quite a slow procedure. I have tried this and it takes about 30 mins to run the script.

      Any help or suggesting would be appreciated.

        • 1. Re: Slow database
          philmodjunk

          I suspect your idea for speeding this up can work, but I don't know enough about how your database works to be sure.

          How do you get current stock prices? Do you use a web viewer and extract the price from the viewer? Download a set of pricing data from a web site and import it or ???

          • 2. Re: Slow database
            pdoak_1

            Phil

            Stock prices are imported daily into a table within the database.  The stock price table holds stock prices for every day.  I don't think that this table is the cause of the problem as it just holds static data.

            If this table is not the problem and the solution is to copy a snapshot of the entire data in the trade table on a daily basis, what is the best method of doing that?

            • 3. Re: Slow database
              philmodjunk

              Ok, that's not quite what I was picturing here.

              On the layout where you see things as too slow, first experiment with simpler layout designs to see if excessive conditional formats, unstored calculations etc or other elements are the biggest factor. You can duplicate your layout and then delete elements from your duplicated layout to see what items are the largest source of the "slows" for you. From there, you can explore options for speeding up your layout.

              Summary or unstored calculation fields that have to access large numbers of records to compute the needed value is the prime suspect here, but sometimes it's a conditional format that you find you can live without in exchange for faster screen updates.

              • 4. Re: Slow database
                pdoak_1

                I found the problem.  It relates to many fields within the table that have unstored calculations.  I will now try and put those calcluations into a static table and reference the results from there.