Re: [sqlite] sqlite tuning for db writes?

[Alex Chudnovsky]

Guy Hindell wrote:


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.


--
regards,

Alex Chudnovsky
Majestic-12: Distributed Search Engine
http://www.majestic12.co.uk

[sqlite] sqlite tuning for db writes?

[Guy Hindell]

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
  but can't tell which of these favour reading
or writing.
 
Many thanks
guy