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