Olaf Schmidt schreef:
> "Ron Arts" <r...@arts-betel.org> schrieb im
> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>
>> Here's my new benchmark output:
>>
>> sqlite3 insert 500000 records time: 17.19 secs
>> sqlite3 select 500000 records time: 18.57 secs
>> sqlite3 prepared select 500000 records time: 3.27 secs
>> glib2 hash tables insert 500000 records time: 0.38 secs
>> glib2 hash tables lookup 500000 records time: 0.24 secs
>>
>> The prepared select indeed speeds up things tremendously,
>> a 5-fold increase.
>
> Now do the same thing (prepared commands) for
> the sqlite3 - inserts too ... wrapped in a transaction.
>
> Against an InMemory-Table I reach here about
> 120000 Inserts per second (filling up and inserting
> "mixed values" against a 8-column-table).
> That's on a 1.8GHz Intel-(mobile)CPU.
>
> As long as your benchmarked-tabledef (your insert) has not
> much more columns than my above mentioned '8', then
> you should see something like "factor 5" there too.
>
> With a small two-column-table (a prepared two-column-insert-Cmd)
> I see about 360000 inserts per second, somewhat depending
> on the used datatypes (Integers and Doubles work a bit faster of
> course than inserting the same "amount of Columns" as Text-Values).
>
> Another reason for your bad insert-performance could of
> course be, that you already defined an index on the table
> in question (or in case your ID-Field is defined as
> INTEGER PRIMARY KEY *and* you're filling up
> new IDs in non-consecutive order).
> In that case your current results seem a bit more reasonable.
>
> If you don't have an index created yet (on your "HashKey-
> ID-Column" ... or if you don't have mapped your ID-Field
> to SQLites RowID yet (per INTEGER PRIMARY KEY)
> then you maybe should try to create one - if possible, after
> your "main-amount" of fillups was done - that adds some
> additional time to your overall-data-preparation efforts of your
> "table-list" - but will be of benefit for your single-record-lookups,
> based on your "... Where ID = ? ".
>
>
Olaf,
I tried it, and indeed, this speeds up inserts tremendously as well,
but in fact I'm not at all concernced about insert speed, but much more about
select speed. I use the following queries:
CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
Then I insert 500000 records like this:
INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
(with consecutive values for the id value.)
do you expect the id column now to be mapped to the Row ID, so
this results in the fastest possible way of selecting by id?
I now get this:
sqlite3 prepared insert in trx 500000 records time: 5.08 secs
sqlite3 select 500000 records time: 19.28 secs
sqlite3 prepared select 500000 records time: 3.47 secs
glib2 hash tables insert 500000 records time: 0.37 secs
glib2 hash tables lookup 500000 records time: 0.25 secs
But I'm still looking to speed up selects.
Thanks,
Ron
> Olaf Schmidt
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
