dimanche 28 février 2016

sqlib iOS preformance issue

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