@Keith yes, a custom table-valued function should be faster, as it would
alleviate the roundtrip through json, though that json round-tripping
accounts to only about 10-20% of the CPU time AFAICT.
@Richard and others: executing individual insert statements always give
lower performance than using json_each, it is more than 2 times slower when
many rows are involved.
For instance in the case of a ":memory:" database, with the table
create table test (id INTEGER PRIMARY KEY)
and inserting in it numbers from 1 to 1000000 (one million), within a
transaction and with 3.13.0
When using insert into prepared statements, the inserts take 1.95 seconds
here, but if using json_each, only 0.74 seconds (including the creation of
the JSON, which itself is a matter of milliseconds).
Code looks like the following (the methods are ultra-thin wrappers around
SQLite API calls)
db := TSQLiteDatabase.Open(':memory:');
db.ExecSQL('create table test (id INTEGER PRIMARY KEY)');
QueryPerformanceCounter(t1);
db.BeginTransaction;
stmt := db.CreateStatement('insert into test values (?)');
for i := 1 to NB do begin
stmt.BindInteger(1, i);
stmt.Step;
stmt.Reset;
end;
stmt.Finalize;
db.Commit;
QueryPerformanceCounter(t2);
and for the json_each
db := TSQLiteDatabase.Open(':memory:');
db.ExecSQL('create table test (id INTEGER PRIMARY KEY)');
QueryPerformanceCounter(t1);
db.BeginTransaction;
stmt := db.CreateStatement('insert into test select value from
json_each(?)');
stmt.BindStringA(1, json.ToUTF8String);
stmt.Step;
stmt.Reset;
stmt.Finalize;
db.Commit;
QueryPerformanceCounter(t2);
Note that loop itself is not the bottleneck: when commenting out Step/Reset
calls and leaving just 1 million BindInteger calls, it runs in 44 ms (which
is roughly comparable to the time it takes to create the json string)
In my few tests, passing the data as json and using json1 functions was
consistently faster at least up to 3 columns and when using integers or
small strings.
So "insert from select" seems to have some avantage, and I was wondering if
there are other ways to leverage it?
On Fri, May 27, 2016 at 12:29 AM, Dominique Pellé <dominique.pe...@gmail.com
> wrote:
> Richard Hipp <d...@sqlite.org> wrote:
>
> > On 5/26/16, Eric Grange <zar...@gmail.com> wrote:
> >>
> >> I am looking for the fastest way to insert many rows to a simple table.
> >
> > (1) Create a prepared statement: "INSERT INTO xyzzy VALUES(?1,?2,...)"
> > (2) Run "BEGIN"
> > (3) Loop over each row you want to insert, bind values to the prepared
> > statement, then call sqlite3_step() and sqlite3_reset().
> > (4) Run "COMMIT"
> > (5) Call sqlite3_finalize() on the prepared statement to avoid a memory
> leak.
>
>
> Additionally, "PRAGMA synchronous=off;" gives significant
> speed up for insertions, if you don't mind a corrupted database
> in case of system crash. See:
>
> https://www.sqlite.org/pragma.html#pragma_synchronous
>
> Dominique
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
