8 Replies Latest reply on Oct 19, 2012 4:03 AM by DanielShanahan

    Slow LookupNext Function - is this normal?


      I'm experiencing very slow performance with lookups. I'm using indexed fields with script triggers and inside the script using Set Field with the LookupNext () function. The first time I run the script it takes about 10 seconds. If I change the quantity after that, it is almost instantaneous.


      I'm working on a development copy on my machine so everything is local (Mac OS X Lion 2.66 GHz Intel Core 2 Duo). Here is some more information on my setup:


      Tables: SalesOrderLine and PriceList



      Records: 1238



      Records: ~ 290k


      Relationship defined as:

      SalesOrderLine::itemID = PriceList::itemID

      SalesOrderLine::qtyOrdered = PriceList::qty


      itemID is a text field in both tables.

      qtyOrdered and qty are number fields.


      All fields are indexed.


      I ran through the example in FileMaker's Help Center and created a db with the Items and Shipping Costs. It works fine and fast, but there are only four records. Also, I have a multi-predicate relationship whereas the Help Center example matches just on one field.


      I haven't used the next higher/next lower value before, either in the Lookup field definition or LookupNext () function, so I'm not sure if this is normal FMP behavior. My client is moving away from a DOS-like application where everything is keyed and she rarely uses the mouse. I've watched her enter data for sales orders and she is blazing fast. There is no way that an initial 10 second delay will work.


      Any thoughts?

        • 1. Re: Slow LookupNext Function - is this normal?
          Stephen Huston

          In order to determine the NEXThigher or-lower value, all of the existing records in the lookup-source table have to be compared, which means caching all of them into memory where they are essentially sorted for future reference, which is why it's only the first time that you see this delay. That caching is a far different experience than simply reading the first matching value.


          Consider this: when the user logs in, include some scripted routine that is performed behind the scenes to trigger a lookup into a test record for an old/sample order record which you can then either ignore or delete, so the values will already be cached by the time the user is ready to begin entering their orders. It may mean that the apparent login process will take 10 seconds longer to get the user up and running at the beginning of work, but that is far better than when they are in the order entry process with a customer.

          • 2. Re: Slow LookupNext Function - is this normal?

            Thanks Stephen.  I think I did not explain properly.  The delay happens the first time I enter a particular item so every new line item has a delay.


            I've done more testing and here are some new findings:


            When I enter a new item, each new item has a signitifcant cost is processing time (again, this is all local):

            Item A - 22 sec

            Item B - 1:44 min

            Item C - 10 sec


            So, I thought, if I run Item B again, it should be cached.  However, here is what happened:

            Item B - 10 sec


            I heard/read somewhere (Mark Richman WAN optimization?) that the order of a multi-predicate relationship matters so I changed the relationship from this:

            SalesOrderLine::itemID = PriceList::itemID

            SalesOrderLine::qtyOrdered = PriceList::qty


            to this:

            SalesOrderLine::qtyOrdered = PriceList::qty

            SalesOrderLine::itemID = PriceList::itemID


            The good news is that it is faster; almost instantaneous.  The bad news is that it grabs the wrong value!  So I changed it back.


            Then, I wondered if the quantity makes a difference.  So I tested that and here is what I found:

            Item A - qty: 17 - 2:29 min

            Item A - qty: 5 - 22 sec

            Item A - qty: 5 - 22 sec


            Item A only has one price, so the lookup quantity is 1.


            So I introduced a new item with 4 pricing options (Items A and C have 1 pricing option and Item B has 2):


            Item D with price breaks at 1, 3, 6, 12


            Item D - qty: 6 - 0 sec

            Item D - qty: 12 - 0 sec

            Item D - qty: 11 - 6 sec

            Item D - qty: 50 - 18 sec


            I wondered if the search for 12 had somehow helped the larger search of 50 so I closed database and reopened it and performed one search:

            Item D - qty: 50 - 18 sec


            So I am perplexed!  My suspicion now is that it might have to do with the multi-predicate relationship, the number of records in the table of the sourceField, or developer ignorance.


            I will keep testing and welcome more thoughts/suggestions.

            • 3. Re: Slow LookupNext Function - is this normal?

              I didn't mention this but the solution is in FileMaker Pro 12.  I was curious what, if any, difference there would be in FileMaker Pro 11.  I exported the two tables in question, created a relationship and wrote a script that bascially sets the field using the LookupNext () function (it also times the script, commits the record, and refreshes the window).  I realize this is not exactly an apples to apples comparison as the whole solution is in FMP 12 so lots of tables, layouts, scripts, etc. and the only thing in FMP 11 are the two tables, a couple of layouts, and one script.  So, with that disclaimer, here is what I found:


              FileMaker 11


              Item D - qty: 50 - 7 sec

              Item D - qty: 50 - 7 sec

              Item D - qty: 42 - 7 sec

              Item D - qty: 12 - 0 sec

              Item D - qty: 11 - 3 sec

              Item D - qty: 72 - 7 sec

              Item D - qty: 272 - 7 sec

              Item D - qty: 272 - 8 sec


              Just a reminder that Item D has four price breaks at the quantities of 1, 3, 6, 12.


              There is a difference and it looks like FMP 11 is faster.  Nonetheless, searching for the same number a second time does not produce an instant result which is what I would expect (rightly or wrongly).  Also, unlike FMP 12, increasing the quantity does not seem to increase the query time.


              Still open to thoughts/suggestions.

              • 4. Re: Slow LookupNext Function - is this normal?

                I had run into sluggish LookupNext performance as well, a while back.  I had the same scattered results.  After intensive testing and packet sniffing...it came down to the sort order of the relationship ( whether you have it sorted or not is not exactly the issue ).


                FileMaker gets hyper chatty when you perfom the LookupNext function. I don't entirely understand the reason for the variation in speed, but I do understand the transfer of data.


                In the end, I opted for a find / variable combo...find the records that apply and do the math to pull the correct value myself.  It cut the execution time to less than 1% of the original ( LookupNext ) approach.

                • 5. Re: Slow LookupNext Function - is this normal?

                  I want to second Joshua's suggestion about the find/variable solution. I've used this in multiple situations where more traditional built-in functions (like LookupNext) didn't perform at acceptable levels. Just finished a script that does a find in a million record table using 14 criteria, loops through the found set appending the primary key to a variable, and then places the data in the variable into a global field to show the found set in a portal. The found set may get as large as 400-500 records and the performance is close to instantaneous.


                  Gordon Shewach

                  Desktop Services

                  Ann Arbor, MI

                  1 of 1 people found this helpful
                  • 6. Re: Slow LookupNext Function - is this normal?

                    Gordon: do you do this in the same window the user is viewing? Or an auxiiary window? On screen or off?

                    • 7. Re: Slow LookupNext Function - is this normal?

                      For the example above, I do it in the same window, on screen. There isn't even a flash. The user exits a field and all he/she sees is a portal fill up with data. With this general technique (find/variable), I've also done it in an auxiliary window off screen.


                      The use of this technique (aren't you the pioneeer?) is SO BLAZING FAST that it has become a primary weapon in my scripting arsenal.



                      • 8. Re: Slow LookupNext Function - is this normal?

                        Thanks Joshua and Gordon.  I took your advice and scripted the process.


                        The nice thing about the LookupNext function is that it is so easy.  One script step does the job.  Unfortunately - in some cases - it appears that it doesn't do it very quickly.


                        Since the solution is in FMP 12 I decided to use the new ExecuteSQL ().  This, of course, allows me to find both the quantity and the price and return them in a sorted list, using ORDER BY.  The quantity and price are then put into their own variables and then I loop through each quantity and compare it to the quantity the user entered.


                        There are a number of conditions that need to be checked.  Nonetheless, preliminary testing (still on my machine) is promising.


                        Thanks again.