3 Replies Latest reply on Apr 5, 2010 12:22 PM by philmodjunk

    Unstored Calculations Taking Forever....

    tglatt

      Title

      Unstored Calculations Taking Forever....

      Post

      I have a database with quarterly financial records dating back to 2007 for approximately 8000 institutions. I download updates from a governmental regulatory agency. To make it easy on myself, I modeled my table structure after the way they offer the data files. This leads me to the following table structure:

       

      Table1: Contains the name, unique id, cycle data, basic contact information of reporting institutions, and a set of calculations that determine financial ratios, and a set of calculations that determine a score based on the ratios

      Table2: Contains the unique id, cycle date, and financial data for reporting institutions

       

      The tables are related via the id and cycle date

       

      Each quarter I upload the latest versions for both tables, appending the new data to the appropriate tables in the database. As an aside, the reason I choose to append rather than update the data is so that past quarter calculations are correct.

       

      I have a third table that I use to average the scores for each quarter. The table contains a unique record id, cycle date, and average score calculation for each quarter. This table is related to Table 1 via the cycle date.

       

      The idea is that I can import the latest data and review the scores and performance for any given institution in the database via Table1, and then enter the cycle date in the Table3 to calculate and view the score averages - and then graph the data.

       

      This works as intended - however because Table3 is based on an underlying set of calculations and scores based on nested If..Then statements, any changes to the layout based on Table3 (such as resorting a column) takes about 5 minutes or more to finalize as Filemaker recalculates all of the score average data. 

       

      I was thinking that I could try storing Table3 calculations in a fourth table to use for analysis, graphing, reporting, etc. but was not sure how best to do that.

       

      Any recommendations to speed up the calculations, or store them in a way that makes it easy to use them without perpetual recalculations?

       

      Filemaker Pro 11

      MacBook  2.2 GHz Core 2 Duo with 4 GB RAM

       

      Thanks!

       

       

        • 1. Re: Unstored Calculations Taking Forever....
          philmodjunk

          Sometimes to get acceptable performance you have to denormalize your data. If I understand your data, this is static data that you simply load into your database and it is not subject to updates after you imported. That makes this ideal for setting up a scheme that computes and stores summary totals so that most of your calculations don't have to be unstored and subject to constant recomputation when you pull up a report.

           

          Here's how we do it with invoice data where I work:

           

          At the end of the day, a script summerizes all line item data into a special summary file. Where there are around a thousand line items created each day, the summary file stores that data in less than 20 records. Each record corresponds with one type of item purchased on a given day. Other fields store the total quantity, unit cost and total cost in simple number fields.

           

          The script does this by isolating all the line items for a given type of material in a found set and then uses set field instructions to copy the data from summary fields in the line items table into a single record in the summary table.

           

          We do this so we can pull up 5 year comparison reports with monthly subtotals while avoiding lengthy waits for the numbers to calculate.

          • 2. Re: Unstored Calculations Taking Forever....
            tglatt

            Thank you! I set up a script that goes to the layout containing the summary data, finds the correct quarter, sets the field names and data, and the goes to the storage table and drops the results in. Works perfectly.

             

            Out of curiosity, is it possible to pre-populate an option menu for the quarters based on what data is available in the database? For example, have the script review the quarter dates and the have the user select which quarter to copy? 

             

            As of now, I simply edit the find portion of the script for the correct quarter data. 

            • 3. Re: Unstored Calculations Taking Forever....
              philmodjunk

              You can use a value list to list all unique values from a  specified field in one of your database tables. Is that what you need?