8 Replies Latest reply on Nov 30, 2011 10:38 AM by philmodjunk

    Occurrences - Performance question

    Matty_1

      Title

      Occurrences - Performance question

      Post

      Hello,

       

      Several months ago I embarqued on a project to build a fairly complex data base that keeps track of inventory, contract, transactions and invoicing, projections, etc etc etc.

       

      In the begining I must say my knowledge of filemaker pro was very basic and as I moved forward and our company kept seeing more protential I was forced to keep pushing and learning far more complex ideas on my own.

       

      The reason I'm posting today is to get some insight on FilemakerPro's background performance.  My issue is that only now have I realized I made a fundomental mistake when I first set up my occurences.  Here's a very simple example of the situation I've put myself in.

      I have table IN, OUT, INVENTORY

      My first goal was to create a tool that could track what's come in and what's gone out and a central page where we could view current inventory.  Instead of having an occurence called INVENTORY and 2 unique occurences for the IN and OUT (ie: IN-INVENTORY and OUT-INVENTORY) I directory attched them to the original occurence IN and OUT.  From a visual stand point, think a "V" at a 90 degree angle.  INVENTORY would be at the point of the "V" and the IN and OUT occurences are running out the sides.

      Our company then wanted to add contracts so instead of creating new occurences for the IN and OUT I directly connected the contracts to the IN and OUT that were connected to the INVENTORY occurence further extending the "V" shaped graph.  Take that idea and continue it 5-10 fold.  Only after the requests became extremely complex did I start to run into an error message saying this occurence could not be related to the same thing twice.  I always managed work arounds but never fully understood the reasons why I was getting the error message.

      It finally clicked that not only direct relations (one occurence to the next beside it) count but any occurence related to any other linear occurence will be able to see each other.

      My concern is that this could be severly hurting the data bases performance and I'm wondering if someone could shed some light on the situation?  Generally the data base has been running fairly smooth but I see a slight performance issue since it was first rolled out.  The program has only been online for 3 months, records will very quickly pile up and I don't want this to become unusable come the 1 year mark.

       

      Do I have to change all my relationships??

      Graph.jpg

        • 1. Re: Occurrences - Performance question
          philmodjunk

          I'm afraid the verbal description without laying out the details of a relationship make it hard to picture your database structure and how your user interface interacts with this structure.

          It might help to upload either a screen shot of Manage | Database | Relationships or a simplified graphic representation of them.

          Only after the requests became extremely complex did I start to run into an error message saying this occurence could not be related to the same thing twice.

          It's possible to make multiple, direct relationships between the same two tables by creating multiple table occurrences that all have the same data source table. If you have not already done so, this tutorial may help you better understand this concept: Tutorial: What are Table Occurrences?

          Combine those concepts with the Anchor Bouy method of setting up your relationships in FileMaker: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

          And you may find that you have a better organized set of table occurrences in your relaitonship graph on which to base the function of your database.

          but any occurence related to any other linear occurence will be able to see each other.

          Sounds like you have have a chain of related tables: Table1----Table2----Table3----Table4 and have discovered that you can access related records in Table4 from a layout based on Table1. If so, then this does indeed work, but is a method to avoid unless you have a specific reason to use this method. Accessing data in Table4 from a layout base on Table1 requires not only that you have valid relationships between each pair of table occurrences in this chain, but their data source tables must contain records that correctly link your data. If table2, for example has no records in it, you can't access any records in Tables 3 or 4...

          • 2. Re: Occurrences - Performance question
            Matty_1

            Hey Phil,

             

            I've uploaded a picture of my relationship graph, if you look towards the left of the image you will see an occurence of Inventory.  Directly attched to that are "Interim" and "SalesMix" and as you see, going forward Sales and Interim and connected to all sorts of occurences.

             

            Here's my concern, my inventory acts as a ledger to whatever comes in and out (there are roughly 20-25 different types of inventory items to track)  will this "bottleneck" setup cause my data base to lack in performance speed because all occurences are connected in some way or another (with the exception of only a few on the sides)  Now that I have a clerarer understanding on how relationships should be properly setup I know that this isn't ideal but will it be ok regardless?  

             

            Thanks!

            • 3. Re: Occurrences - Performance question
              philmodjunk

              I'm afraid that I can't read the text of your relationship graph's table occurrence boxes. That makes it difficult to make any suggestions. The structure to your relationships are only part of the picture. How you use these relationships in calculations, scripts etc is also a factor.

              You might try posting again with the graph enlarged and cropped to remove all the unused white space. You can use the fairly new "upload an image" controls now included below the Post A Answer text box to upload an image to your next post in this thread.

              • 4. Re: Occurrences - Performance question
                Matty_1

                Is this better?

                • 5. Re: Occurrences - Performance question
                  Matty_1

                  Hey PHil,

                   

                  I also wanted to note that I checked out your link to the Anchor Bouy method.  This is exactly what I realized I should have done in the beinging but I was still a newby.  I haven't so now I'm wondering how badly my setup with effect over all performance a year down the road when there are 10+ thousand records.  Will the inventory table be unsusable?

                   

                  • 6. Re: Occurrences - Performance question
                    philmodjunk

                    Anchor Bouy doesn't really affect system performance, it helps improve your performance as a developer by organizing your table occurrences in a structure that is much easier to work with.

                    Nothing appears glaringly wrong with the relationships  for Inventory and the other tables.

                    Things that can slow you down as your record count increases:

                    1. A layout with summary fields that total values from 1,000s or even millions of records
                    2. calculation fields with aggregate functions such as sum, count, average, etc that reference a lot of related records--especially in a list or table view layout that lists a large number of records in its found set.
                    3. Sorts and finds that reference summary or unstored/unindexed fields
                    4. List or table view layouts with lost of conditional formats and/or filtered portals.
                    5. Thus, how you design your layouts and scripts can have much impact on how responsive your system becomes.

                    Here are some tricks that can make speed improve with large database files:

                    Create a summary table that stores totals/subtotals for commonly pulled up reports in simple, indexed number fields. This only works with data that is not subject to frequent changes. We nightly summarize sales info from our line items table into such a summary table, condensing over a 1000 line item records into about a dozen records-one per type of item listed on an invoice. Summary reports based on this table update many time faster than the same report on a layout based on the original line items records.

                    Perform finds that require specifying criteria in an unstored or unindexed field in two stages. In stage one, perform the find specifying only criteria in indexed fields. In stage two specify the criteria in the unstored fields and constrain your found set.

                    If a critical field is a calculation field referencing data in other tables, replace it with a simple data field and use script triggers on the fields referenced in the original calculation to update this new data field. This produces a field that can be indexed.

                    Use a Convert to Seperation Model so that you can update much of your database by swapping out the old interface file for the new without having to import any data when you do so.

                    If all else fails, consider adding an archive file and use Import Records to move records from the current table to the archive table, then delete the records from the original table. This can produce a table that searches, sorts etc. much more quickly but you can still go to the archives table to access historical data

                    • 7. Re: Occurrences - Performance question
                      Matty_1

                      Good to know thank you Phil!  I appreciate the pointers on making things more efficient.  Quick question:

                      If a critical field is a calculation field referencing data in other tables, replace it with a simple data field and use script triggers on the fields referenced in the original calculation to update this new data field. This produces a field that can be indexed.

                      So instead of having field A caluclate the sum of B and C you suggest having a "on exit" or " on commit" script tirgger in field B and C that does the calculation A normally did??   

                       

                      Create a summary table that stores totals/subtotals for commonly pulled up reports in simple, indexed number fields. This only works with data that is not subject to frequent changes. We nightly summarize sales info from our line items table into such a summary table, condensing over a 1000 line item records into about a dozen records-one per type of item listed on an invoice. Summary reports based on this table update many time faster than the same report on a layout based on the original line items records

                      So if I'm understanding this corectly, with the above method you only get a real picture of current sales at the end of the day correct??

                      • 8. Re: Occurrences - Performance question
                        philmodjunk

                        Yep, that's the idea. Not something to do unless you can get a significant performance improvement. It can be tricky to set up and maintain in a manner that is fully "bullet proof" especially in a multi-user environment typical of such large file systems where another user many be editing the record your script is attempting to update--at which point an edit lock on the record will keep your script from doing so.

                        We can get a real picture of our current purchasing (It's a scrap metal dealer so we buy from our customers instead of sell to them) at any time.

                        But if I want to see a 5 year cross tab format comparison chart of how many pounds of Aluminum cans were redeemed each month, the data for today won't be in that report until the following morning.