1 2 Previous Next 22 Replies Latest reply on Aug 2, 2017 8:11 AM by alecgregory

    Selector Connector: performance?

    noamb

      Title

      Selector Connector: performance?

      Post

      Are there any performance enhancements or drawbacks to using Selector Connector, as compared to Anchor Buoy or other methodologies?

        • 1. Re: Selector Connector: performance?
          philmodjunk

          Selector Connector is a form of Anchor Buoy.

          In most cases there does not appear to be a performance hit from using SC. But with any database design, performance is determined by many factors and design details and it's important to consider all design details and make tests to confirm that the design you are implementing does not contain unacceptable delays.

          • 2. Re: Selector Connector: performance?
            KevinSmith

            I’ve found the Selector-Connector to be really useful. toddgeist has built an excellent framework, making it easy to move interface elements and code to other contexts within the system.

             

            When I patched the selector-connector model into a large legacy system I found a serious performance hit. Here’s my explanation of how & why the slow-down occurs.

             

            Each time a user goes to a  new context e.g. a new layout the system downloads to the user’s computer a full “map” of the related table occurrences. If you use the connector model then you get a multiplier effect on the number of table occurrences a particular table is linked to. This adds to the information load.

             

            On a traditional relationship graph a visit to the Orders table occurrence forces the system to pull-through info a about the single related table “Orders_items”:

            screenshot_3007.jpg

             

            This is what it will look like when a connector table is added:

            screenshot_3008.jpg

            Now, when a user navigates to Orders table the info relating to all five related tables must be cached. With a simple and clean design like this one the impact will be negligible. On the system the inclusion of the CONNECTOR link resulted in large mesh with 100+ tables linked.

             

            So this is a note of caution if you’re considering knitting the Selector-Connector system into a highly populated & complex table occurrence graph. I’ve noticed a clear impact when performing scripts that access related data. These scripts did not directly use the connector links. When I removed the link from the local table to CONNECTOR the script ran three times faster.

            1 of 1 people found this helpful
            • 3. Re: Selector Connector: performance?
              jfletch

              Jason Young has said that larger solutions can receive a performance hit when you don't use globals on both sides of the relationships to the Connector table. Even if you are using cartesian joins, the globals do make a difference.

               

              I also put extra effort into making sure that I don't have my related tables more than one hop away from the Selector table (it doesn't appear that you are using one in your examples).

              • 4. Re: Selector Connector: performance?
                Extensitech

                This is what I had understood as well. However, I'm just now implementing selector/connector into some new solutions, and haven't really tried it in a larger solution, so this is definitely a concern. Kevin, as someone with a large solution to test this in, I'm hoping you can confirm? Or perhaps that toddgeist can chime in and confirm?

                 

                I'm sure hoping it's the way you describe, and how I understood it. When a solution gets large and unwieldy is a horrible time to find out about a scalability and performance issue like this, but I absolutely love the method and its implications.

                 

                Following with much interest.

                 

                Chris Cain

                Extensitech.

                • 5. Re: Selector Connector: performance?
                  MatteoKrings

                  Hello,

                   

                  I want to implement the selector connector into a larger database as well and I have built a small database for testing. I want to fully understand the concept before really working with it.

                   

                  Regarding performance: My expectation was that record creation is faster due to the use of the relationship and a single commit, but actually it is slower.

                   

                  These are the results on a LAN:

                  • Creating 100 records (thereby setting 3 fields)
                    • Traditional (go to table + loop): 0,67 seconds
                    • Through relationship: 5,1 seconds

                   

                  • Editing 100 records (setting 1 field)
                    • Traditional: 0,45 seconds
                    • Through relationship: 5,0 seconds

                   

                  It is a relatively simple setup of “Customer”, “Shopping basket” and “Products” and I am creating these records in the basket table. The three fields that are set are the foreign keys for products and customers and a quantity field.

                   

                  There are no validations and no auto-enter calculations. The only two indexed fields in this table are the primary key and the foreign key (from customer).

                   

                  Is there anything I am doing wrong? Any hints?

                   

                  Regards,

                  Matteo

                  • 6. Re: Selector Connector: performance?
                    KevinSmith

                    jfletch I can confirm that the relationships to the connector table are correctly based on global fields.

                     

                    The system I am talking about  has 400 table occurrences. About 30 Table Occurrence Groups are linked (interlinked!) to the connector. If I remove some of the links to connector the system shows a substantial performance improvement in:

                    a) Startup time (no startup script active);

                    b) Layout navigation.

                    • 7. Re: Selector Connector: performance?
                      jbante

                      Can you share your test file with us? We can't see if anything is wrong if we can't see anything at all.

                      • 8. Re: Selector Connector: performance?
                        MatteoKrings

                        Here is the test file. I have translated most parts of it, because it was in German before. I hope the remaining things won't bother you.

                         

                        I have added two methods of creation, so now we have the following results for 100 records over the LAN:

                        • Portal (without the use of the selector): 0,4 seconds
                        • Portal (using the selector): 0,45 seconds
                        • Traditional (go to table + loop): 0,67 seconds
                        • Selector without portal: 5,1 seconds

                         

                        Thank you very much for having a look at it!

                        • 9. Re: Selector Connector: performance?
                          jbante

                          Your file gets similar results on my machine. I saw that your "Refresh" script toggles the join fields in the selector and connector tables. This isn't necessary. You only have to toggle the join fields in the local table — not that it makes a substantial difference to the speed.

                           

                          You've tested create performance, but databases do more than create records. I'd be interested to see the results of testing the rest of CRUD: Read, Update, and Delete. Selector-Connector doesn't need the Refresh script for all of these, so I'm eager to see if the penalty you found for creation still holds.

                           

                          Selector-Connector is designed for code modularity, not for speed; but knowing the speed characteristics can at least better inform our design decisions. Keep going!

                          • 10. Re: Selector Connector: performance?
                            PeterDoern

                            Thanks for this excellent illustration.

                             

                            Each time a user goes to a  new context e.g. a new layout the system downloads to the user’s computer a full “map” of the related table occurrences. If you use the connector model then you get a multiplier effect on the number of table occurrences a particular table is linked to. This adds to the information load.

                             

                            ... is something I'd read in the past but could never track down later on, so thanks for the confirmation.

                             

                            I'm in the process of pulling out chunks of a legacy system (about the same size as the one you're dealing with), piece by piece, into a multi-file system. I intend to use Selector/Connector as a means to replace a lot of the dedicated "create related records" TOs that I think are helping to clog up the current system.

                             

                            My take is that Selector/Connector would be best implemented as a TOG whose TOs have no user-facing layouts, but that Go To Related Records via Selector/Connector would leap-frog the user to a layout whose base TO is the one linked to related records (mainly for portals).

                             

                            In other words, you'd have multiple TOGs:

                            1. Selector/Connector

                            2. Layout TOG with parent TO and related TOs for portals/displaying related data

                            3. Calculation TOG on which parent TO would base calculated fields

                            4. Functional TOGs, e.g. Delete Related Records When

                             

                            Does this make sense? Specifically, when a user jumps to a layout does FileMaker load up the full "map" or just the immediately-related TOs?

                             

                            Thanks for any clarification.

                             

                             

                            Peter

                            • 11. Re: Selector Connector: performance?
                              MatteoKrings

                              There is already a test in updating included in my test file: "each +1" updates each item in the customer's basket, but I have added some methods:

                               

                              Updating 100 records:

                              • Through a portal not using the selector: 0,19 seconds
                              • Through the portal using the Selector: 0,21 seconds
                              • Traditional: 0,45 seconds
                              • Through relationship using the selector: 5,0 seconds

                               

                              Now I have added three methods to delete those 100 records:

                              • Traditional: 0,18 seconds
                              • Through the portal using the Selector: 0,26 seconds
                              • Through a portal not using the selector: 0,46 seconds

                               

                              I know that Selector/Connector is not a model designed to increase performance, but as I have a lot of users who access the larger database from home using Webdirect, I always have to take the speed factor into account.

                              I was suprised by the results, because Todd Geist showed in his video "Understanding commit record"(36:25) that those processes a quicker when making them through relationships. But during this video he is not talking about relationships using the Selector/Connector, so that seems to be a big difference. As my tests show there is only a speed increase when using the Selector/Connector together with a portal.

                               

                              I am interested in the whole model, because I don't need endless TOs for the menu, virtual lists,... hanging from each anchor by using the connector and I want to add transaction safe scripts to my databases. As far as these are concerned I guess I only need a Transaction table hanging of my connector with portals that point to all the tables attached to the Selector.

                              • 12. Re: Selector Connector: performance?
                                Paul Jansen

                                I ran the "buy 100" tests on the file over the WAN and the results were

                                 

                                • Portal (using the selector): 3.100 seconds
                                • Portal (without the use of the selector): 3.114 seconds
                                • Traditional (go to table + loop): 5.435 seconds
                                • Selector without portal: 6.542 seconds

                                 

                                I guess it is clear that using the selector without portal is not the fastest way to create or edit records in bulk.  However it is interesting that in my WAN test the Selector method is only about 20% slower than the traditional method.  Much less of a differential than when running locally.

                                • 13. Re: Selector Connector: performance?
                                  KevinSmith

                                  Feedback from running the test file:

                                  I downloaded MatteoKrings test file and ran the four variant of "Buy 100 products" function locally on Filemaker 13 and FileMaker 15. Traditional (Green) was the fastest and Selector (Red) was the slowest.

                                   

                                  I did not find as big a variance between the two as Matteo reported. Traditional was just under 0.1 seconds and Selector was just over 0.3 seconds.

                                  • 14. Re: Selector Connector: performance?
                                    MatteoKrings

                                    I have extended my testing a bit further and made all tests with 10 000 records (so 100x more).

                                     

                                    Interestingly each process took 100x longer with three exceptions.

                                    I could fix one of them (deleting with selector and portal) by modifying the script and then it took only 100x longer as well.

                                    But editing those 10 000 records takes remarkably longer than expected:

                                    • Through the portal: 236x longer (45 seconds) than with 100 records
                                    • Through the Selector and a portal: 171x longer (36 seconds) than with 100 records

                                     

                                    Is there any explanation for this?

                                     

                                    Regards and thank you so much for all the replies!

                                     

                                    Matteo

                                    1 2 Previous Next