COMMIT does the heavy lifting, which is why it consumes resources. It actualy performs the safe write to the disk.
Alessandro Merolli wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
