> Pavel, > > does the cache work for memory datsbases too? Doh, missed the fact that it's a memory database. I believe in-memory database is in fact just a database cache that never deletes its pages from memory and never spills them to disk. Although anything about size of database cache will not be applicable here... But wrapping all selects into one transaction will work with in-memory databases too (somewhere in another thread there were even benchmarks showing the effect of this). And maybe in fact you never even need to commit or rollback transaction in in-memory database because there's no other connection which will need to get access to the database and to the committed data...
BTW, another option for speeding up selects is to recompile SQLite without multi-threading support and maybe even without some other stuff that you never use. At least lack of multi-threading support can make a significant difference in performance. Pavel On Sun, Oct 11, 2009 at 12:39 PM, Ron Arts <r...@arts-betel.org> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
