9 Replies Latest reply on Apr 10, 2017 4:47 PM by agisllc

    Performance: Auto-enter vs Lookup vs Execute SQL?

    agisllc

      Hi Everyone,

       

      We are about to optimize the data file for a solution containing 100 tables+. One of our to-dos is to convert lookups to auto-enters because it is our understanding that auto-enters are faster. We also plan to convert many of the "related field" auto-enters to SQL queries rather than using relationships for the same reason. Does anyone happen experience with similar efforts? Specifically:

       

      • Are auto-enters actually faster than lookups and, if so, by how much?

      • Is using SQL to gather a related record's field faster than using a relationship and, if so, by how much?

       

      Any insight would be appreciated. 

       

      Thanks,

       

      Michael Bethuy

        • 1. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
          philmodjunk

          Never heard of auto-enters being faster than lookups. There is a slight behavior change with auto-enters in that you can't use Relookup with them so take that difference into account.

           

          With SQL, there are too many variables for anyone to provide a single answer. Please keep in mind that SQL queries are interpreted into regular FileMaker actions that the DRACO DB engine can process so at the very least, there is that additional interpretation processing that has to take place before the query can be executed.

          1 of 1 people found this helpful
          • 2. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
            philmodjunk

            Another thing to keep in mind is that the total number of records in your table make a huge difference when evaluating whether or not one method of working with that data is "faster" than another.

             

            If you have less than 100 records in a table, a method that is 100 times slower than another will likely still complete so quickly that you can't perceive any delay. Try that on 10's of thousands of records or more and there will be a clear difference in performance. Most algorithms that have to work with your records in an aggregate--to get a total, to query, to sort are generally such that the time to complete is a function of the total number of records and its usually a non-linear function where doubling the number of records more than doubles the amount of time required to complete the operation. Efficient algorithms will generally be of the type known as O (N * Log N ) where inefficient methods can be as bad as O ( N * N ).

            • 3. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
              agisllc

              Thanks for the helpful information. Our understanding that auto-enters are faster came from one of the sales engineers, but that was probably 5-6 years ago. I believe one of my team members also heard the same thing at one of the DevCon presentations, but that was also a handful of years ago.

               

              I'm interested in seeing if anyone else replies, but given your input, we're going to test a few scenarios on a remotely hosted table with a lot of records. We'll report our results here. If we don't see any meaningful differences, then we just saved ourselves a ton of work!

               

              Thanks again for your feedback,

               

              Michael Bethuy

              • 4. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
                agisllc

                Hi There,

                 

                We spent several hours testing various changes. The database was hosted in Virginia. Tests occurred over the WAN from Boise, ID. The primary "lookup source" table for most of our tests contained approximately 30k records. The table we used to test was sales order items using a portal via an "on create" relationship (so that the record was created in the client and with no scripting overhead).

                 

                Creating an order item before making any changes required about 7.5 seconds. Creating the same record with every auto-enter and lookup disabled required 1.75 seconds. We then proceeded to re-enable certain groups of fields before working backwards again with conversion tests.

                 

                Auto-enters appeared to be slightly faster than lookups. Converting 15 fields from lookups to auto-enters shaved ~.66 seconds off of time. Converting 10 of those auto-enters to SQL equivalents added back most of the savings, so you appear correct that Execute SQL is slightly slower than using relationships.

                 

                With all the said, these were highly imperfect tests and the variances were slight, so there doesn't appear to be a meaningful difference between the various options.

                 

                Thanks again for your input,

                 

                Michael Bethuy

                1 of 1 people found this helpful
                • 5. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
                  philmodjunk

                  That's why it's important to test against real world models. Sometimes things are faster in theory, but in practice, the difference is either too small to perceive or perceptible, but so small as to not be worth the effort to make the needed changes to "optimize".

                  • 6. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
                    kazznfx

                    Hey Michael,

                     

                    Just my two cents:

                     

                    In my experience lookups are quite a bit slower than auto-enter calcs. Personally I try to avoid them for that reason.

                     

                    RE the SQL, I've found SQL is considerably faster BUT only when dealing with large data sets. If you want to quickly grab information from somewhere, related just about however you want, and it needs to access large sets of related data, I would recommend SQL. SQL is also incredibly quick at giving summary or aggregate data for large data sets (particularly filtered, sorted, or offset data).

                     

                    If you're only looking up a single value for each record, I would expect a relationship to be faster.

                     

                    Pretty much just echoes the above, but thought I'd share.

                    • 7. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
                      agisllc

                      Thanks for sharing. We didn't run any tests with multi-key relationships or "multiple hop" relationships (i.e. grabbing a field value from a table occurrence more than one level deep in the dependency tree). Most of the lookups in our data file a pretty simple, so we didn't have an incentive to run such tests, but what you suggest makes sense.

                      • 8. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
                        philmodjunk

                        When evaluating an actual layout, you also have to be careful in how you interpret the results. With either auto-enter or looked up value options, did the delay occur in getting the data into the field, in evaluating an expression defined as part of the auto-enter, or is the layout just refreshing slowly because some other part of the layout now has to re-evaluate--such as a summary field computing a total or other aggregate value?

                        • 9. Re: Performance: Auto-enter vs Lookup vs Execute SQL?
                          agisllc

                          We used a layout that did not contain any sum fields or any other objects that would refresh. We started our timer upon exiting the field that triggered the auto-enter/lookup. The field had an "on save" script trigger that yielded a custom dialog. We stopped our timer when it appeared. We ran each "exit field" test twice and considered the average.

                           

                          We also ran the same test two times, once using a related record at the beginning of the foreign data set and another using a record at the end of the dataset (just to see if the record's position in the foreign table had any impact -- it didn't appear to).

                           

                          Our test environment was imperfect in a few ways but the numbers were fairly consistent and predictable. Slowly restoring the auto-enters and lookups back to their starting point did gradually increase the numbers as expected, for example.