2 Replies Latest reply on Jul 28, 2015 8:35 AM by ZakButcher

    Freeze Calculation Fields During Script

    ZakButcher

      Title

      Freeze Calculation Fields During Script

      Post

      Hi All,

      I've recently restructured a table in my database from having roughly 75 fields down to 3 fields with about 18 calculation fields.  To keep things short, we're a manufacturing facility and the initial design tracked our unique build process with each record storing a status for each stage of the building process.  Now that I have time I went through and created the table the right way so there were no duplicated fields.  In other words, most of the 75 original fields were repeating Status, Date, and Note fields for each stage.  Now I have a table that uses one record for each stage and the calculation fields are used as triggers to display information to users at specific stages.

      The problem I'm running into now is the script that imports the old table's data into the new table and creates records is excruciatingly slow because of the calculation fields.  The first dry run was done with only the 3 core fields plus some others (primary key, creation date, modification date, etc) and took a couple minutes.  This time it's taking roughly 5 seconds for each original record; of which there are 950.  It's been running for about 20 minutes and as a rough estimate it's going to take 4750 seconds = 1 Hour 20 Minutes total.

      Is there any way to prevent calculation fields from updating while a script is running?  This would be immensely helpful in the likely event I need to re-run the script.  Seeing how it's mid-script and I don't want to interrupt it, I won't be able to try anything for a few minutes but any thoughts would be much appreciated.  One thing I thought to try would be to remove the calculated fields from the table-view so they are not displayed.  The next time I run the script I'll test this idea.

      Any other thoughts?

      Thanks!

      Zak

        • 1. Re: Freeze Calculation Fields During Script
          philmodjunk

          It has nothing to do with the fact that the script is running. If you did a manual import, it would be just as slow. The fields are evaluating and also most likely building/updating indexes on them. You need the fields to evaluate and you probably need those indexes.

          Isn't this a "one time" transfer of your data?  If so, I'd just let it be and wait for completion. I've sometimes had to let an import records operation run overnight.

          If you really want to prevent the fields from evaluating during import, make them unstored calculations before importing.

          • 2. Re: Freeze Calculation Fields During Script
            ZakButcher

            Phil,

            Thanks for the quick update.  It should be a one-time script, however I know I'm going to need to run it at least one more time.  This is for a live database and I needed actual data to rebuild a couple layouts and make sure they behave just as they did before the new table updates.  Once I've made all the changes and have the new layouts working properly I will run the script again, but this time I'll run it after hours when there's no one else in the database making changes to record data.

            Zak