8 Replies Latest reply on Apr 15, 2013 12:05 PM by LSNOVER

    Has ESS in FMP v12 changed from FMP v11?

      Hello ESS gurus,

       

      Upon migrating our MailOrder solution from V11 where we were successfully pulling our website orders into a local table from the mySQL table of weborders, we've seen what seems like a behaviour change with the ESS of V12 that leaves us perplexed.

       

      The mySQL table has some 11K records and we're needing to dowload only the most recent. The number per day can be anywhere from a few to over 40 depending on how actively our customers are ordering. The V11 script steps we use for finding the lstest orders are shown here:

      ESS script V11.png

       

      When we try to use the same script with V12 there's an interminable wait while the latest records are found on the cart1_order mySQL table. It took some 10 - 15 seconds with V11 (which was usable) but more than 20 minutes in V12, making this method of extracting orders from our website unsuitable for our migrated solution.

       

      Our workaround is to continue using the V11 file for the ESS access, exporting the records as a couple of text files (WebOrders & WebOrderItems) then importing those records into our V12 solution.

       

      I've checked our setup of the DSN in the ODBC Managers on the two server installations and the settings are identical for each version.

      We're using FMP 11.0v4 and FMP12.0v3 on Mac OS X 10.8.2 or 10.6.3. FMS 11 is running under Mac OS X 10.6.8, while the OS for FMS 12 is OS X 10.8.2.

       

      Has anyone else encountered this issue?

       

      Your contributed experieces will be appreciated.

       

      John

      Hamilton, New Zealand

        • 1. Re: Has ESS in FMP v12 changed from FMP v11?
          steve.winter

          Hi John

           

          Have you tried running this with script debugger turned on…? which step in the script causes the bottleneck…? is it the flushing of the joins, or the find or…?

           

          Cheers

          Steve

          • 2. Re: Has ESS in FMP v12 changed from FMP v11?
            taylorsharpe

            Why not just do a direct ODBC import instead?  It has to be faster than going to an ESS table and making FileMaker think through all those rows. 

            • 3. Re: Has ESS in FMP v12 changed from FMP v11?
              taylorsharpe

              Or you could do an ExecuteSQL without ever going to the ESS layout and then loop through the results and save into your local table.  But I still bet the direct ODBC import would be faster. 

              1 of 1 people found this helpful
              • 4. Re: Has ESS in FMP v12 changed from FMP v11?
                jormond

                As of yet, I haven't run into anything different...however, the first thing I would do is see if you can get rid of the "Show All Records" step. 

                 

                Possibly make a relationship to the WebOrders table and use the Last () function to pick up the ID from the last WebOrder...then:

                 

                Enter Find Mode

                Go to Layout [ "WebOrders" (WebOrders) ]

                Perform Find ( )  //using the ID from the Last Function

                Set Field [ Preferences::LastWebOrder ; $LastWebOrderNo ]

                 

                At least that way your are avoiding moving a bunch of data from the ODBC source into your local cache.  Especially because you Flush Cache earlier in the script.

                • 5. Re: Has ESS in FMP v12 changed from FMP v11?
                  BowdenData

                  +1 on using direct ODBC import instead of ESS (unless there is other functionality that you have in the dB that ESS fits). In the few solutions where we interconnect with a SQL source, direct import has been significantly faster than ESS every time in both FMP11 and FMP12.

                   

                  We have only retained ESS in one instance where we are showing the user layouts that are directly to linked to SQL data.

                   

                  Having said this, a couple of things to check with your scripting.

                   

                  • cart1_order layout: this layout should be a 'blank' one and set to form view only.
                  • order_id field in your sql table: it is a key field in the sql and thus fully indexed?
                  • Is the ESS table occurence by chance based on a raw sql table that will continually grow? If so, suggest that your ESS table occurence in FMP be based off of a view in the SQL that would filter the records in some way. Perhaps it would only show orders created in the last week/month/etc. This assumes you have some control over the SQL dB or a good working relationship with the SQL administrators.

                   

                  HTH.

                   

                  Doug

                  1 of 1 people found this helpful
                  • 6. Re: Has ESS in FMP v12 changed from FMP v11?
                    Lemmtech

                    I agree I have been doing this for years on the Mac and use the drivers from Actual technologies to do a direct ODBC import from a shopping cart and it works fine. Not sure what advantage ESS gives you in this context.

                    • 7. Re: Has ESS in FMP v12 changed from FMP v11?

                      Hi Everyone,

                       

                      Many thanks for all of your helpful replies.

                       

                      With my developer colleague, Bill Fowler, on hand to assist we tackled the whole issue afresh.

                       

                      Bill discovered that the Actual Technologies driver on the FMS 12 server was the trial version not the licensed version as it was only letting us view the first three records of a newly found set. With the licensed version properly installed and using some blank layouts for our finds ( > Previous Last Web Order Number) we've managed to get our web order downloads working again with no pauses whatsoever.

                       

                      The worst part of the conversion has been the matching up field names for the several import steps from the "over normalized" set of mySQL data tables. That frustration has provided us with yet more evidence that the original web site designers were not quite smart enough to provide us with the best SQL design for an e-commerce web site.

                       

                      John

                      • 8. Re: Has ESS in FMP v12 changed from FMP v11?
                        LSNOVER

                        That trial license has bit me a few times as well.  Generally speaking ESS works better in 12 than 11.  The SQL buffer is larger, and it will handle larger tables.  However, it still is limited.  I can import much bigger tables using the same drivers through the straight ODBC import.