> I tried your example and got ~0.45s for json and ~0.37s for my
> implementation;
I have also tried a custom virtual table, and I also get figures about
20-30% faster than with json.
> Interesting: when NOT using "primary key" (I have a flag for this), the
> times are ~ 0.26s vs 0.18s vs 0.47s !
Interesting indeed, I had (incorrectly) assumed that providing the rowid
would help with performance.
> I agree json works fast enough for such simple example, but I don't know
> how you could use it for e.g. 2 fields;
There are two approaches I found that work with varying degrees of
efficiency depending on the data (how many columns, how large...):
* concat the row data into a string, pass those as a json array, then in
sqlite, use json_each, instr and substr and cast to parse back to columns
* stream the rows as a json array of json objects, and parse in sqlite with
json_each and json_extract, using
select json_extract(value, '$.a') a, json_extract(value, '$.b') b
from (select value from json_each('[{"a":1,"b":2},{"a":3,"b":3}]'))
or
select json_extract(value, '$.a') a, json_extract(value, '$.b') b
from json_each('[{"a":1,"b":2},{"a":3,"b":3}]')
from "a mile high", both forms had a similar performance, but I have not
looked at the vdbe nor at the bowels of json_extract/each.
I will likely got with the custom virtual table in my particular case, but
being able to insert straight from json is something I will definitely use
knowing it's faster than doing it with code (all APIs that revolve around
running queries on input data would be a perfect fit).
Eric
On Tue, May 31, 2016 at 3:32 PM, Gabriel Corneanu <gabrielcorne...@gmail.com
> wrote:
> Hi Eric,
>
> As I know you from Delphi related projects, I thought it would be nice to
> share my ideas.
> I am using a home-grown framework for sqlite persistence, optimized for
> storing records into tables.
> Main ideas:
> - using virtual tables (with the same structure) to speed up sqlite inserts
> a write translates to: insert into test("ID") select "ID" from
> temp.tmp_test
>
> - based on RTTI to generate optimized code for accessing/returning data
> - supports all common data (ints, floats, string, blob/TBytes)
>
> I tried your example and got ~0.45s for json and ~0.37s for my
> implementation; I have also tried using simple recursive cte (see code
> below) but it was slower, ~0.66s.
> I think most of the time is spent in packing the integers and building the
> btree(s) (i.e. CPU bound). Enabling the "name" field below ("storage"
> attribute) increased the processing time (my implementation only) to ~0.47,
> which I think confirms my interpretation.
>
> Interesting: when NOT using "primary key" (I have a flag for this), the
> times are ~ 0.26s vs 0.18s vs 0.47s !
> Looks like sqlite is optimized to generate the rowid, and it is slower when
> explicitly set (even if it's the same value).
>
> I agree json works fast enough for such simple example, but I don't know
> how you could use it for e.g. 2 fields;
>
>
> Hope it helps,
> Gabriel
>
> Code:
>
> type
> TTestRecord = record
> [Storage('ID', [sfPrimaryKey])]
> //[Storage('ID')]
> ID : Int32;
> //[Storage('name')]
> name: string;
> end;
>
> procedure TForm2.Button2Click(Sender: TObject);
> var
> db : TSQLiteDatabase;
> tb : TVirtualTableDataArray<TTestRecord>;
> r : TTestRecord;
> i : integer;
> pf, t,t1,t2,t3: int64;
> json: TStringBuilder;
> begin
> tb := TVirtualTableDataArray<TTestRecord>.Create;
> json := TStringBuilder.Create;
> db := TSQLiteDatabase.Create;
> db.OpenWrite(':memory:');
>
> tb.Setup(db.DB.DB, 'test');
> tb.CreateTable;
> tb.VTInit;
>
> json.Append('[');
> for i := 1 to 1000000 do
> begin
> r.ID := i;
> r.name := i.ToString;
> tb.Items.Add(r);
> if i > 1 then
> json.Append(',');
> json.Append(i);
> end;
> json.Append(']');
>
> QueryPerformanceCounter(t);
> db.db.ExecSQL('insert into test(id) select value from json_each(?)',
> [json.ToString]);
> QueryPerformanceCounter(t1);
> t1 := t1-t;
>
> tb.CreateTable;
> QueryPerformanceCounter(t);
> tb.WriteData;
> QueryPerformanceCounter(t2);
> t2 := t2-t;
>
> tb.CreateTable;
> QueryPerformanceCounter(t);
> db.db.ExecSQL('WITH RECURSIVE ' +
> 'cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE
> x<'+tb.Count.ToString+
> ') insert into test(id) select x from cnt');
> QueryPerformanceCounter(t3);
> t3 := t3-t;
>
>
> QueryPerformanceFrequency(pf);
> ShowMessageFmt('N=%d T1=%.2fs T2=%0.2fs T3=%.2fs', [tb.Count, t1/pf,
> t2/pf, t3/pf]);
>
> tb.Free;
> db.Free;
> json.Free;
> end;
> _______________________________________________
> 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
