[odb-users] Bulk insert for SQLite

[Boris Kolpackov]

Hi Ivan,

Иван Романенко <viva.cpp at gmail.com> writes:

> In SQLite 3.7.11(2012-03-20) added INSERT
> <http://www.sqlite.org/lang_insert.html> syntax to allow multiple rows to
> be inserted via the VALUES clause.
> 
> So it is possible to implement bulk insert for SQLite.
> 
> 1. Why ODB do not support bulk insert for SQLite ?

Well, this is not quite the bulk insert that you find in, say,
Oracle or SQL Server (which ODB supports). In those databases
you execute a single INSERT (i.e., with a single row in VALUES)
multiple time on an array or rows (say 10,000 rows per batch).

What we have in SQLite is the ability to have an INSERT statement
with multiple rows in VALUES. So if we wanted to insert 10,000
rows at a time, the INSERT statement (i.e., its literal text)
would have to have 10,000 rows:

"INSERT INTO ... VALUES (?, ... ?), (?, ... ?), (?, ... ?), ..."

So that would be one giant statement. We would also have to bind
data for all 10,000 rows at once. That's a lot of bindings. So,
overall, for the way ODB uses SQLite statements (i.e., prepared
with parameter binding), this feels like the wrong way to do it
and which will most likely results in worse performance.

But, if you (or someone else) wants to create a benchmark, I am
willing to be proven wrong ;-).


> 2. Does it improve performace of insert operations for large
> amount of objects( > 1 million) for SQLite?

As I said above, I would very much doubt it. My feeling is that
it will be a lot slower. One of the main bottlenecks of SQLite
insert is foreign key constraint checking. So if you need to
insert, say, 1M rows and you can make sure no FK violations
are possible, it would make sense to temporary disable FK
checking for such a transaction. Also splitting this insertion
into several smaller transactions might help.

Boris