2 Replies Latest reply on Dec 15, 2016 8:59 AM by eworbit

    Import Records step cause redundant SELECTs


      Why does this happen?


      The SELECT statement in the Import Records script step is as simply as is can be. I use SQL Server Profiler to secretly watch FileMaker Server's incoming SELECT statements - just to see what FMS is doing under the hood.


      See attached screenshot.  It's the 3rd request is what I believe that coincides with the FM record import progress dialog.   The first two requests are the real mystery. 


      My guess is that FM wants to either get a column count, test connectivity or get a data sampling before the important 3rd SELECT happens.   But there are not LIMIT clauses on the first two SELECTs.   They're all the same.   FMS might be "canceling" the first two SELECT statements and move on to the third.


      This is okay for big imports and most FM developers are probably unaware this is happening.   It is a problem, though, when you just want to import a few rows (like an order's line items) which should take 2 seconds - but you end up paying 6 seconds or more - triple the cost!


      This at least happens on Mac FMS 14 and 15 with the Actual Technologies ODBC driver.   I don't know if FMS for Windows does this.


      I don't think there is a developer community solution to this.  I just wanted to see if anyone else was aware of this inefficiency.

      -Eric Wood

        • 1. Re: Import Records step cause redundant SELECTs
          Johan Hedman

          Why dont you setup a ESS to have that table inside your relationship graph so you can treat like any other table in your database

          • 2. Re: Import Records step cause redundant SELECTs

            I know, right!?  ESS was cool while it lasted.  We still use ESS for other smaller tables - just not the orders views we were having issues with. 


            With the uncontrollable caching and FMS crashing, we were forced to not use ESS.  Sadly, we had to resort to routine imports bringing the order data into native FM tables.


            Good news is that server crashes have apparently ceased.  But, I wondered why importing 1 order basically took the same amount of time to import 600 orders.   This is when I noticed the redundancy in the calls made to Sql Server.


            Just curious if others have used SQL Server Profile to actually watch what FMS sends it.