1 2 Previous Next 19 Replies Latest reply on Dec 16, 2013 9:40 AM by philmodjunk

    Slow calculations

    ultranix

      Title

      Slow calculations

      Post


           Hey, even though i have only 797 records in my dabatase and no design (in order to make it faster), after i added calculations, it runs very slow and when certain number of records is reached, "?" are displayed and note that "not enough memory".

           Is there a way around to get through this issue, or getting back to excel is the only option?

            

        • 1. Re: Slow calculations
          gcatnine

               What you said is not enough to understand what happen.

               I never had problems like you.

               I would l check my formulas and / or scripts first.

          • 2. Re: Slow calculations
            ultranix

                 Formulas work (no scripts, because i only use that database for temporary calculations).

                 But they only work up until certain number of records is reached.

                 I use several table occurences, and calculation fields that refer to those table occurences.

            • 3. Re: Slow calculations
              gcatnine

                   if you do not tell more details, it is impossible to give you an answer

              • 4. Re: Slow calculations
                philmodjunk

                     Would you perhaps be using GetNthRecord in your calculations?

                     Are there any recursive calculations used? (These can happen both in custom functions and also in regular calculation fields.)

                • 5. Re: Slow calculations
                  ultranix

                       I am not an expert of GetNthRecord usage (i actually don't know how to use it anyway).

                       Several levels of calculations are used, when one field calculates one thing, then second field bases on the 1st field's result, 3rd field bases on 2nd field's result and so on up to some 5-6 levels.

                       So, yes, it may get heavy.

                       As for Filemaker alternative (loop and set field - not the greatest way around, because, if i want to set all those field and loop throughout 30.000 records and counting, it takes at least 10 minutes of time).

                       So I might get back to Excel, where B2-B1 is calculated in a heartbeat, and in Filemaker, you have to set another table occurence for previous day's record, and only then you can calculate (or that's what i am capable) simple difference between two records.

                  • 6. Re: Slow calculations
                    philmodjunk

                         I must repeat what Gianandrea Gattinoni posted. We can't try to help without knowing the details of what you have set up here.

                    I can only note that "out of memory" suggests a runaway recursive calculation--a calculation that directly or indirectly references it self and thus continuously "loops" through the same number crunches until available resources are exhausted.

                    • 7. Re: Slow calculations
                      ultranix

                           Ok, i uploaded file, check it.

                           https://www.dropbox.com/s/ysmkelprhdie0ti/Calc.fp7

                      • 8. Re: Slow calculations
                        philmodjunk

                             I can see why you didn't just post them. They are truly complex. I traced a few calcs and did a few experiments. I spotted two basic factors that are bogging things down:

                             You have relationships that become "recursive" due to using a self join relationship to access data in other records that in turn access data in other related records... This is very similar to having a found set of records where GetNthRecord pulls data from the preceding record--what I thought might be the issue in the first place. Each of these calculations are thus unstored so the FileMaker calculation engine has to recursively traverse relationships back to "record 1" computes a value and then can compute the value for record 2 in order to get a value for record 3 and so forth...

                             The other factor is that your self join relationships use inequality operators--which are much slower than = for matching data.

                             If we could store the results of each record's calculations that combine data with preceding records of the same "tick" value, the calculation load would be drastically reduced. Could it be possible to use an auto-enter field option such as a Looked up value setting to automatically copy the value from the preceding record of the same tick? This would eliminate the long change of calculations, but also makes for update issues if you ever need to go back and modify a value in a preceding record--that would not change the computed value in other existing records "downstream" from the record that you changed. you'd need to use a script to update data in each of those records should such a change be necessary.

                             That's the biggest issue here. There may be some relationships that you have in place where you might be able to get the same record matching without the inequalities but I think those changes aren't the biggest issue here.

                             Other methods for matching to a range of dates in a relationship.

                             Use

                             Table1::DateList = Table2::Date

                             where DateList is a return separated list of dates from DateStart to DateEnd. A custom function or looping script can produce such a list of dates.

                             or use:

                             Table1::cMonth = Table2::cMonth

                             where cMonth computes the date for the first of the month for all records with dates from the same month and year:

                             DateField - Day ( DateField ) + 1

                        • 9. Re: Slow calculations
                          ultranix
                               

                                    You have relationships that become "recursive" due to using a self join relationship to access data in other records that in turn access data in other related records... This is very similar to having a found set of records where GetNthRecord pulls data from the preceding record--what I thought might be the issue in the first place. Each of these calculations are thus unstored so the FileMaker calculation engine has to recursively traverse relationships back to "record 1" computes a value and then can compute the value for record 2 in order to get a value for record 3 and so forth...

                               Exactly, that's what i said earlier.

                               

                                    The other factor is that your self join relationships use inequality operators--which are much slower than = for matching data.

                               Well, it is complex, because for 1st 7 records (or 5, or 9, or 13, as are other table occurences), it takes averages from the number of records that are up to that record, so, if we have records with dates 2010-10-04, 2010-10-05 - it would have average of 2, if we go to the next record with date 2010-10-06, it would take average of 3, then 4, then all up to 7, and only after 8th record of particular tick is reached, it would calculate average of previous 7. That's why i used inequality operators. I am not a programmer, and math has never been my strongest subject in school, so I try to find ways around. I sometimes find, but, as this time, it's way too slow.

                               

                                    If we could store the results of each record's calculations that combine data with preceding records of the same "tick" value, the calculation load would be drastically reduced. Could it be possible to use an auto-enter field option such as a Looked up value setting to automatically copy the value from the preceding record of the same tick? This would eliminate the long change of calculations, but also makes for update issues if you ever need to go back and modify a value in a preceding record--that would not change the computed value in other existing records "downstream" from the record that you changed. you'd need to use a script to update data in each of those records should such a change be necessary.

                               Would be great, but i don't enter any new records manually, i import them from .csv file. So how could this lookup script be constructed?

                               

                                    Other methods for matching to a range of dates in a relationship.

                               

                                    Use

                               

                                    Table1::DateList = Table2::Date

                               

                                    where DateList is a return separated list of dates from DateStart to DateEnd. A custom function or looping script can produce such a list of dates.

                               I am kind of afraid using loop in 30.000 plus records and counting, because, with my current setting, it takes way too much time for it to be convenient.

                               Custom function? Any idea how to construct such? I've never done this, only imported some custom functions i downloaded.

                               

                                    or use:

                               

                                    Table1::cMonth = Table2::cMonth

                               

                                    where cMonth computes the date for the first of the month for all records with dates from the same month and year:

                               

                                    DateField - Day ( DateField ) + 1

                               1st day of the month trick is already solved by you and it's used in another place, not in this table. but thanks anyway.

                                

                          • 10. Re: Slow calculations
                            ultranix

                                 Or is there a way to "store" those calculations? (I thought of using "indexed" instead of "unstored" calculations), because i don't ever need to recalculate them.

                                 But here's a problem, as they are kind of "recursive", if 1 record depends on another, it may not calculate it correctly.

                            • 11. Re: Slow calculations
                              philmodjunk

                                   And that's why I suggested an auto-enter field option to copy the value from the preceding record. Record 1 has no related records to reference and the auto-enter calc uses data in the local record to compute a value in what is now a stored, indexed field. Record 2, references this stored value from record 1 and the  auto-enter calc combines that value with data in the local record to compute a new stored, indexed field. And so forth...

                              • 12. Re: Slow calculations
                                ultranix

                                     Sounds like a plan, how do i do that?

                                • 13. Re: Slow calculations
                                  philmodjunk

                                       Here's an example using simple math to save time. In real life, I would use a summary field for this type of calculation, but it will illustrate the concept:

                                       Say you have 3 fields: Debit, Credit, RunningBalance

                                       Running balance has to take the balance from the preceding record and then add the debit, subtract the Credit to compute a new running balance.

                                       You can set up RunningBalance as a number field with an auto-enter calculation, assuming that you have a relationship to match to the preceding record:

                                       Debit - Credit + PreviousRecord::RunningBalance

                                       As long as you never have to go back and change data for a previous record, this works. Anytime you have to edit a debit or credit field in an existing record or delete the entire record, you'd have to use a script to update records that were created after the one modified/deleted.

                                  • 14. Re: Slow calculations
                                    ultranix

                                         Too bad, that Auto-entered, Looked-up value doesn't work on import. Or does it?

                                         When I manually create new record and enter data in fields, that are matching and therefore essential for lookup - everything happens in right order, but when i import bunch of records, all fields, that should have been filled with auto-enter look-up, are left empty. 

                                    1 2 Previous Next