CREATE / DROP Index, not working ? asynchronous ?

Discussion created by Vincent_L on Aug 9, 2017
Latest reply on Aug 10, 2017 by user19752



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 -

(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.