[odb-users] slow persist for bulk insert

[Boris Kolpackov]

Hi Erez,

Erez GB <erezgb at walla.com> writes:

> We are working with ODB and implement a complex schema and it works 
> great :-)

Thanks, glad you are finding ODB useful!


> However, we need better performance when we are insering 10000 of
> objects into our SQLite DB. Can you please help with guidelines for
> improving ODB performance with SQLite? Is there another way for
> fast bulk insert of many ODB objects.

SQLite doesn't support bulk INSERT (unlike, say, Oracle or SQL Server)
so you have to execute a separate INSERT statement for each object
(which is what ODB does). So the only way to optimize this is to try
various way you do it.

Number one is probably selecting the right transaction granularity.
This earlier post has more information on this:

http://www.codesynthesis.com/pipermail/odb-users/2013-May/001270.html

Number two is to temporarily disable foreign key constraint checking
(if you use foreign keys). You lose some safety (you need to make sure
all the references are valid) but you usually gain quite a bit in
performance. Here is what the code would look like:

connection_ptr c (db->connection ());

c->execute ("PRAGMA foreign_keys=OFF");

transaction t(c->begin());

for(unsigned int i = 0; i < 10000; i++)
{
  if (i % 100 == 0)
  {
    t.commit ();
    t.reset (c->begin ());
  }

  ...
}

t.commit();

c->execute ("PRAGMA foreign_keys=ON");

If that's not sufficient, then I found this fairly exhaustive list of
things you can try (most of them via the PRAGMA as above) to improve
performance of INSERT in SQLite:

http://stackoverflow.com/questions/1711631/how-do-i-improve-insert-per-second-performance-of-sqlite

Note also that in case of SQLite doing any kind of parallel insertion
(e.g., by running multiple transactions from several threads in parallel)
will only make things worse (SQLite has very poor concurrency support,
especially for modifications).

Boris