Hi
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
or writing.
Many thanks
guy
