I'm relatively new to sqlite and this list but I have recently used it
(v3.2.7) as an alternative to the db backend of an application which
previously used SQLServer or Access as part of a proposed port to
Unix/Linux. So far so good - the app works just as it did before with
SQLServer. However, I'm interested to know how I can tune sqlite to best
advantage for the app.
The database is only accessed by one thread and only to store the
current state of the application, so the database is read once at start
up and then does nothing but writes (inserts, updates and deletes) after
that. The schema is simple, essentially consisting of a table where each
row represents object instances, another table which stores the values
for each object instance, and a third which stores relationships between
objects. All tables have simply a primary key which covers between 2 and
4 columns depending on the table. There are no complex queries performed
on the database (no multi table selects, joins etc.).
The majority of SQL commands issued are INSERTs and DELETEs, and so it
is these that I would like to optimise. Performance at the moment is
respectable, although I would prefer to see DELETEs go a bit faster. To
minimise the number of SQL commands issued I have implemented some
batching of row DELETEs (e.g. DELETE FROM Instances WHERE Id in
(1,2,3,4,5.)). All commands are issued within transactions.
Any suggestions? I have read the tuning hints at www.sqlite.org
<http://www.sqlite.org/> but can't tell which of these favour reading
> The majority of SQL commands issued are INSERTs and DELETEs, and so it
>is these that I would like to optimise. Performance at the moment is
>respectable, although I would prefer to see DELETEs go a bit faster.
In my application I use transaction for multiple inserts and they seem
to run faster than MySQL, I have not noticed that deletes were
particularly slow but if they are then the best way to optimise some job
is not to do it -- since inserts are very fast you can change logic to
insert new data into growing table and update some rows pointing to
current set of inserted rows, so instead of: DELETE + INSERTS, you will
have INSERTS + UPDATE. You will need to truncate table once in a while
or even drop/create it.