8 Replies Latest reply on Aug 10, 2017 12:42 AM by user19752

    CREATE / DROP Index, not working ? asynchronous ?




      In order to get somewhat workable import speed wuen importing 4 Millions records, I decided to fiddle with indexing.

      Without any index, importing takes 2 minutes (this is still a lot of time but I can't do any faster, of course I'm on SSD).

      With indexing turned on, before import, on the fields : 11 minutes !

      (and all of the testing are redone from a blank truncated table).


      So I thought about truncating the table, dropping the indexes, import the 4M records, then turn on indexing.


      so I used 4 (I've 4 field, and you have to make a call per field) BE_FileMakerSQL ("DROP INDEX Import_bench.Field1") calls

      and 4 BE_FileMakerSQL ("CREAE INDEX Import_bench.Field1") calls.


      That didn't worked out, till I've put a 5 s pause between each statement.

      On the creation part, the script was said to be done (I've put a custom dialog at the end), but then I got an indexing filed4 dialog, after the script ended.


      So it appears to me that unfortunately those CREATE / DROP Index function are asynchronous. Which is absolutely evil in the scripting world.


      So, by luck my 5 second pauses worked this out (I've put one last after the last create index call).

      But that's not reliable I guess. So is there a way to poll the state of the CRETA / DROP call, to ensure that they're really done ?


      You can play with the file (login : Admin, password blank)


      Dropbox - Import_bench.zip

      (be sure to keep the bench_data.txt in the same folder as the fmp12 file)



      P.S : doing the index dance thing, lead to 4,36 minutes total time ! Rather than 11 minutes that shows how inefficient the importing process is in regards to indexing.