7 Replies Latest reply on Dec 15, 2009 8:02 AM by TSGal

    Sort, Edit and File Size



      Sort, Edit and File Size

      Description of the issue

      I am testing FMP 8.5v2 and 10 on both MAC and WIN platforms. The MAC is an iMac(mid 2007), OSX 10.6, Intel Core 2 Duo 2.4 GHz, 4GB of Ram 667MHz, 800 MHz bus. The Windows machine is running XP Pro sp3 with basically the same configuration except the RAM and Bus are faster. The file has 47 Fields: 37 text (21 of which are from the original data), 3 summary and 7 calculated (1 using GetSum). I use the file to analyze, manipulate and report on Customer supplied data. I have noticed on the WIN side (both 8.5 and 10) that when the file size exceeds 125mb, performance hits a wall. I am assuming it has to do with how FMP uses file cache. I have it set to 256mb. I think once the file size exceeds one half the file cache setting, the performance degrades to an unacceptable level. I also noticed, the more blank fields you have, the worse the problem. WindowsFMP 8.5 or 10 ClientFMP Cache set to 256md FMP is doing something else inefficiently (Maybe saves a temporary copy to remember a sort state) Someone PLEASE do a quick test to confirm findings  If you have a file around 100mb UNSORTED. First try doing a sort AND making a single CONTENT CHANGE (just add a space to a text field for one record), then close the file and note the file size, it will almost double. Reopen file (it will be slow), un-sort records AND make a single content change, then close the file and note the file size, it will be back to its original size. In order to get my templates to work efficiently on windows, I created a script: Show All RecordsUnsort RecordsGo to Record/Request/Page[First]Set Field [Clr; ""] Clr is a field I added solely for this purpose I set file options to run the script on close and use it within other scripts, where appropriate. This keeps the file size in check and performance consistent. If your file is around 125mb unsorted, good luck.

        • 1. Re: Sort, Edit and File Size
             Out of curiosity, if you use Save a Copy As... and select the compacted copy option. Does this new copy exhibit the same behavior?
          • 2. Re: Sort, Edit and File Size



            Thank you for the post, sorry it took so long to reply. I needed to do additional testing and also wanted to confirm my findings.  


            Windows XP Pro sp3

            FMP 8.5 Client

            FMP Cache set to 256md


            Saving as a compacted copy does make the file smaller but does not alter the behavior, still slow. FMP does not release/flush the temporary information. However, I did notice another anomaly testing this.


            The file has 47 Fields: 37 text (21 of which are used for the original data), 3 summary and 7 calculated (1 using GetSum). I use the file to analyze, manipulate and report on Customer supplied data.


            Please note that of the 21 fields that are used for importing supplied data, only 11 are used for this test. The file is used to analyze and report on customer data and it is set up to accommodate different variations.


            Also note, that after initial import, all referenced fields are blank for 4 of the calculated fields, and the get sum and the 3 summary fields use those calculated fields. I use 5 scripts to populate the referenced fields, 4 of which are conditional loops.  I bring this up because I think it has to do with the following.


            SOME MORE INFO:

            A. 300,000 records used for testing

            B. Maintenance Script:

                Show All Records

                Unsort Records

                Go to Record/Request/Page


                Set Field [Clr; ""]

            C. All file sizes are determined by performing what is described below and then closing the file.



            1. After initial import, FMP file is 95mb

                After a sort and changing the content of a field, the file size explodes to 170mb and performance crawls

                Run maintenance script and file goes back to 95mb


            2. Run the 5 scripts using the maintenance script and file stays around 95mb

                After a sort and changing the content of a field, the file size increase to ONLY 122MB and performance slow

                Run maintenance script and file goes back to 95mb


            3. Take the 122mb file and save as compacted file, ends up 113mb

                Re-open file, performance still slow

                After a sort and changing the content of a field, the file size increase to 122MB and performance slow

                Run maintenance script and file goes back to 95mb



            Not sure why in step 1 file goes to 170mb, and only 122mb in step 2. I think it has to do with the calculated and summary fields mentioned above. Regardless, FMP DOES NOT EFFECTIVELY FLUSH TEMPORARY DATA..


            There is probably more refinements I could make, but that is not the purpose of this post. What I am trying to accomplish is to get FMI to respond. I think if they can figure out a better way to handle this they would also resolve a bunch of other issues. In an attempt to see if anyone else has noticed this, I spent a few hours searching through this forum. Although I did not find this specific issue, there are other issues related to this (slow opening of files, pausing, slow sorts, painful replace, etc…). Maybe someone with influence can get FMI to address this.

            • 3. Re: Sort, Edit and File Size
                 The thought occurred to me that your issue may be in your indexing options for the fields. It sounds like Indexing needs to be on in order for you to do your reporting. By default Indexing is set to happen as needed. This means that as you work with a set of data Indexing for your fields has to be generated on the fly. This is normally a good thing. In your case, I would think about changing the indexing option to "all". This may cause your import to go slower, as the indexing is created when the record is, but should improve performance of the file. I would also increase the cache size of your filemaker client. (you already had) The file sizes should be more consistent at the larger size. 
              • 4. Re: Sort, Edit and File Size

                Thanks for the post,


                Indexing plays a role, and probably helps explain why the file size almost doubles 95mb to 170mb before running all scripts, and only increases to 122mb after scripts are run. While developing this template, I tested the impact of indexing, ended up having only one field set to minimal indexing, all others are set to index when need. After running all scripts, only 4 additional fields are indexed.  You are correct about the additional import time resulting from having fields set to index. I found this setup to give me the best overall performance.


                There are two issues that I believe causes FMP (worse in v10) to choke.


                The first is the way it handles TEMPORARY INFO (not very well) and it seems that it does not flush it until the file is unsorted AND some content is change. Take any file over 75mb, sort it and make a content change and close it. File size will increase, open it again (note the performance difference) and do a different sort, but don't change any content and close it. File size will not change. Open it again and it will be back to the sort you did just prior to making the content change. FMP DOES NOT EFFECTIVELY HANDLE TEMPORARY DATA. And it DOES NOT FLUSH IT UNTIL YOU MAKE A CONTENT CHANGE AND CLOSE THE FILE. While that temporary data is hanging around, FMP will be sluggish.


                The second problem is either the 256mb cache limit, or how it uses other resources after your file size approaches one half of the cash limit. I tested the same template with 400,000 records (after initial import the file is 125mb) Looking at processing time as records per minute, performance is less than half of the file with only 300,000 records (95mb after initial import).


                FMI needs to look at this issue, a bandaid approach would be to up the cache limit, RAM is so cheap today, or figure out away to improve how it uses other resources. Update the code to use the technology in today's computers.


                For example, if you have two machines with similar configurations and the same generation chipset.  One machine with a 2.66 GHz dual core, the other machine with a 2.5 GHz quad core. You would think the quad core would out perform the dual core, but that is not true, FMP does not use multicore technology effectively. The 2.66 GHz dual will outperform the 2.5 GHz quad.

                • 5. Re: Sort, Edit and File Size



                  Thank you for your posts.


                  I have forwarded all of your posts here (and from the "Using FileMaker Pro" board) to our Development and Software Quality Assurance (Testing) departments for review and confirmation.  When I receive information from either department, I will post again.



                  FileMaker, Inc. 

                  • 6. Re: Sort, Edit and File Size



                    Thank you for addressing issue.


                    One more suggestion. Use of a "blank form" and "Freeze/Refresh Window" is an effective workaround to get performance out of FMP. The problem is that the user has no idea what is going on, there is nothing to indicate progress.


                    I've noticed that use of freeze window has no effect on a sort or replace, both bring up a progress bar. Can a script step that triggers a progress bar similar to sort or replace be added? Title - Processing Script "script". It should automatically do what use of a "blank form" and "Freeze/Refresh Window" does and show progression of the script.

                    • 7. Re: Sort, Edit and File Size



                      Thank you for the suggestion.  However, I urge you to enter this suggestion into our Feature Suggestion web form at:




                      Although I can easily copy your suggestion and paste it into the web form, there are additional questions asked that only you can answer.  Besides, this web form is monitored by both Development and Product Management, and they prefer to read what customers are doing with our products.  I've also notice that they tend to pay more attention to suggestions with real-life examples.  Therefore, just don't say "Add this feature."  Explain what you are trying to accomplish, and how this will help productivity/business/etc.


                      Back to the original topic of this thread, I have not received a reply from either Development or Testing.



                      FileMaker, Inc.