Background Info: I am using the following
- Filemaker pro Advanced 11
- Mac OS X 10.7
- Single user mode
- 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.