Hi!, Optimizing by hand is one way to go, but it can get tedious with multiple SQL statements requiring carefully sequenced prepares, binds, transactions, pragmas, commits, exception-handling, compiler options etc.
For automated optimization, you can try StepSqlite (https://www.metatranz.com/stepsqlite/) - a commercial PL/SQL compiler for SQLite which does several standard optimizations automatically for you without compromising ACID. If needed, you can always do more optimizations by hand on top of this. One major benefit of going automated: your code remains clean. Inlined below are results of the bechmark program compiled using StepSqlite: For disk-based db: INSERT 500000 records time: 6.18 secs SELECT 500000 records time: 1.87 secs Benchmark code in PL/SQL: ======= create table parts(part_no integer, quantity integer, part_name varchar, part_desc varchar); PACKAGE BODY MyPackage IS total integer; BEGIN for i in 1 .. 500000 loop insert into parts (part_no, part_name, quantity) values(i, 'Name'||i, 2); end loop; for partsRec in (select quantity from parts) loop total := total+partsRec.quantity; end loop; dbms_output.put_line('Total Quantity:' ||total); commit; END; ======== Regards, SK On Sun, Oct 11, 2009 at 12:48 PM, Ron Arts <r...@arts-betel.org> wrote: > Are there compile time switches which I can use to speed up > selects in memory databases? Will the amalgamated version be faster > than linking the lib at runtime? > > Thanks, > Ron > > 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 >> >> 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
