Pavel Ivanov schreef: >> I use the following queries: >> >> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name) > > I'm not sure how SQLite treats this table definition but probably > because of your ASC it could decide that id shouldn't be a synonym for > rowid which will make at least inserts slower. > >> But I'm still looking to speed up selects. > > Other conditions that can speed up selects: > - Depending on relation between size of your database and size of > database cache selects could work faster if all database is cached. So > for example in just started application first set of selects will work > slower than second. But if full database cannot fit into cache then > different sets of selects will have the same performance on average. > - If you don't care about changing your database concurrently from > other processes you can place all your inserts in one transaction or > in case of your real application just start transaction at the > beginning and commit/rollback it at the end. > > Tell us if it still doesn't satisfy you. >
Pavel, does the cache work for memory datsbases too? Thanks, Ron > > Pavel > > On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts <r...@arts-betel.org> wrote: >> 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 >> > _______________________________________________ > 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
