I got a strange performance issue on iOS using C library version.
I found all the "common" tips regarding performance and using transaction, prepared statements, memory cache, late writing and so on.
The code is a loop that spins through a text file, inserting 370000 rows. The time for inserts increase drastically as I ran trough it. Here is the timeing for the first 130000 rows:
Wrote 10000 rows: 5 seconds.
Wrote 10000 rows: 8 seconds.
Wrote 10000 rows: 11 seconds.
Wrote 10000 rows: 18 seconds.
Wrote 10000 rows: 25 seconds.
Wrote 10000 rows: 35 seconds.
Wrote 10000 rows: 61 seconds.
Wrote 10000 rows: 114 seconds.
Wrote 10000 rows: 136 seconds.
Wrote 10000 rows: 163 seconds.
Wrote 10000 rows: 180 seconds.
Wrote 10000 rows: 198 seconds.
Wrote 10000 rows: 215 seconds.
So, the first 10000 took 5 sec, the 10000 from 120000 to 130000 tok 215 seconds. So, is this a limitation of sqlite on iOS? Or is there something I have missed?
Before I start, I use:
PRAGMA synchronous=OFF
PRAGMA journal_mode=MEMORY
PRAGMA temp_store=MEMORY
In addition I have tried inserts without keys or indexes in the table, just to see if that got something to do with it.
I do commit each 10000 row, then starts a new transaction. I ran one prepare, then binds and insert data 10000 times before starting over.
I also read threads like Improve INSERT-per-second performance of SQLite? without much help in my problem.
Udate:
The timing is done like this:
db->Prepare(insertSQL);
timer=time(NULL);
while((pos=content.find('\n',lineStart))!=std::string::npos)
{
if (!(lines%10000) && lines>0)
{
printf("Wrote 10000 rows: %d seconds.\n",(int) (time(NULL)-timer));
timer=time(NULL);
db->Prepare("COMMIT");
db->Execute();
db->Prepare("BEGIN");
db->Execute();
db->Prepare(insertSQL);
}
values.clear();
<Some voodoo that fills the values array>
for (j = 1; j < numFields; j++)
db->Bind("@COL" + std::to_string(j), values[j]);
db->Execute();
db->Reset();
lineStart=pos+1;
lines++;
}
db->Prepare("COMMIT");
db->Execute();
Aucun commentaire:
Enregistrer un commentaire