7 Replies Latest reply on May 10, 2015 4:48 PM by rrrichie

    Can we keep the ESS shadow tables updated?

      We've implemented an integration of product data for our web site using an ODBC connection to the server, but thanks to our slow rural broadband connection, both the updating of product info and the downloading of web orders is anything but speedy.

       

      What I'm seeing is that it takes up to a minute before the shadow table of recent web orders get refreshed when we go to that layout. Once there, the information is imported into our local table promptly enough.

       

      Latency times for our connection to the web server are extremely variable as you can see from these ping stats:

           38 packets transmitted, 37 packets received, 2.6% packet loss

           round-trip min/avg/max/stddev = 43.903/449.707/539.919/116.666 ms

       

      Is there any way of keeping the shadow tables of web order_details and order_items refreshed?

       

      Thanks in advance,

       

      John

      Hamilton, NZ

        • 1. Re: Can we keep the ESS shadow tables updated?
          mikebeargie

          If you're using filemaker server, you could script a process, similar to EasySync, to run on a scheduled script and pull the data from the ESS shadow table to a local filemaker table. Your clients would only connect to see the local, up to date filemaker table. This also has the benefit of only worrying about the drivers and ESS connection in a single point, rather than on every client.

          • 2. Re: Can we keep the ESS shadow tables updated?
            wimdecorte

            Or alternatively, not use ESS but use a server-side straight ODBC import.  That would do away with the refresh delay but you'd pay potentially for the time to do the import.  Your SQL query however could be made efficient to only new & updated records.

            • 3. Re: Can we keep the ESS shadow tables updated?

              Thanks Mike & Wim for your helpful suggestions.

               

              A server-side script to import the data into a couple of local tables is the obvious answer while latency remains an issue.

               

              Out of curiosity, I pinged the web server during the very small hours one morning and got these stats:

              31 packets transmitted, 29 packets received, 6.5% packet loss

              round-trip min/avg/max/stddev = 43.077/73.654/519.469/95.106 ms

               

              If we could get those stats during working hours, we could easily live with the much shorter delay. That also tells me we are fighting for bandwidth during the day and an easier solution will be a faster broadband connection, which is promised but who knows when it will be rolled out for our rural location.


              Regards,


              John

              • 4. Re: Can we keep the ESS shadow tables updated?
                wimdecorte

                Hi John,

                 

                Keep in mind that pinging is not quite the same as the persistent connection that FMP needs.

                The packet loss in your result is very worrying.  That indicates a very bad route to the web server.

                • 5. Re: Can we keep the ESS shadow tables updated?
                  rrrichie

                  Yes packet loss while pinging is not good :-)

                   

                  I use a local copy of the "ESS" tables and a server scheduled script to sync.  Before I was accessing the ESS tables directly but getting errors on record commits that drove me crazy.   There is also a "caching" issue.  New entries on ESS tables don't show in FileMaker.  I think I solved that by doing a search, simply going to "last record" did not give me the last record.  I think there is an article somewhere on when the ESS cache gets cleared/refreshed.  I'll post it here if I find it.

                   

                  This inspired me to do the find...

                   

                  Updating data between FileMaker and ODBC data sources | FileMaker

                  • 6. Re: Can we keep the ESS shadow tables updated?

                    Hi Wim & rrrichie,

                     

                    Our FMServer is within our LAN and anytime I access it from the WAN, I frequently get disconnected. FM V13 seems more fragile in that respect than earlier versions.

                     

                    From what you've said about packet loss, I'm now surprised that our access to the web server works at all. Would not the TCP protocol ensure that lost packets are resent? So far we've not seen different data on the web site when compared with our local Product data. That applies to both jpg images as well as data in the various fields.

                     

                    Most days there's not much data going to or from the web server, so I suppose that's a blessing. Several times though we have seen our updating script fail to complete, but only when updating several hundred products in one batch. What we see is a failure of the SetURL step, which is writing a php script result from the web server to a global field on the Product layout that remains foremost during the update.

                     

                    When we were looking for a company to redevelop the web site, one contender proposed writing special routines to sync the data from our FM tables to the web developer's CMS. Their indicative costing was almost half as much again as the $10K we had budgeted for the web site's redevelopment. It was only by using ESS that we could keep within our budget but admittedly using hefty chunks of my time which was an internal cost and thus external to the budget.

                     

                    Do I hear you saying that would you NOT recommend an ESS connection where there was such a weak internet connection between FMS and the web server?

                     

                    Regards,

                     

                    John

                    • 7. Re: Can we keep the ESS shadow tables updated?
                      rrrichie

                      John,

                      Yes tcp/ip does it's best to reconstruct data at the receiving end using various techniques.  a ping uses icmp and has no retransmit, you receive the ping or you don't.  So ping loss doesn't mean you are losing tcp packets, but if you get ping time-outs (providing the time-out is reasonable) means your connection (or pinged machine) is probably busy doing other things.  A ping is a very small packet so if you get a lot of time-out it just means your connection is not that great.  If the ping value is big it means your connection is slow (or the machine on the other end is really busy).

                       

                      In our case the FM Server was local and the ESS tables were on a another internet host (mySQL tables).  I'm using the the Actual ODBC drivers.  The drivers also make a difference.  We had some issues in the beginning and Actual Tech looked into it and we solved it... don't ask for details as this was 5 years ago... :-)

                       

                      When I ping that internet host, values vary a lot, but I seldomly get time-outs...

                       

                      If the connection is that weak, I would not advise ESS connections, but solve it another way.  I also have site I pull data off via a simple php construct.  FM pro just does and insert from URL (or use the Base Elements Plugin) and get json formatted data back.

                       

                      You definitely need to look at transactions if the connection is weak, so you can do rollbacks in case of interruptions.