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
