Hi all,
I'm working on a project that makes massively usage of SQL
statements with many inserts/selects. It's a multi-threaded process
that shares a certain amount of SQLite "connections" to be used by
other process through an IPC layer. The connections are created at
startup and 5 other database files are attached to them before being
shared by this process. Most of the time, the statements requested are
grouped into a database transaction like this:
BEGIN TRANSACTION;
INSERT INTO TBTEST (X,Y,Z) SELECT X,Y,Z FROM TBDATA JOIN ... ;
DELETE FROM TBTEST WHERE X NOT IN (SELECT ....);
UPDATE TBTEST SET Y=y WHERE X=x;
COMMIT TRANSACTION;
I'm already considering all the issues that this kind of design
has: database concurrency, database locks, etc... but, this is not the
thing.
I'm experiencing a behavior that seams strange to me and I'd like
to check if anybody can give me some tips to minimize this: The COMMIT
statement consumes 50% or more of the time of the whole transaction;
for example: if the whole transaction costs 2000 ms, only the COMMIT
operation took 1500 ms. I understood all the operations described in
the SQLite documentation about transaction commit/rollback feature,
but this scenario seems to me that some adjustments can be made to
minimize this.
I'm using:
- The server is an Intel 2GHz, 2GB of RAM and a 80GB hard-disk SATA
II (w/ 8MB of cache) 7200 RPM;
- Windows XP SP3 professional using NTFS;
- SQLite 3.6.13 (amalgamation version);
- I'm using shared-cache with the wait-notify feature implemented
in the previous 3.6.12;
- Other compiler options being used are:
SQLITE_THREADSAFE=1;
TEMP_STORE=3;
SQLITE_DEFAULT_CACHE_SIZE=65568;
SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568;
SQLITE_MAX_ATTACHED=30;
SQLITE_ENABLE_COLUMN_METADATA;
SQLITE_ENABLE_UNLOCK_NOTIFY.
- The database size is around 200MB.
What I'm looking for is:
- If someone has a similar environment, what can be done to
optimize Windows XP disk writes?
- What PRAGMA statements or compiler options can help me with this,
without the risk of getting the database corrupted?
Any suggestion is helpful, thanks for the attention.
Best regards,
Alessandro Merolli.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
