[libsql] sqlite 80x faster on transactions

[<re...@du...>]

Hi,

Inspired by cross-platform usage of sqlite vs transaction and threading 
issues, i've been looking why sqlite is so slow with transactions on my 
system (XPsp2, amd2500+, 250GB HD, NTFS). A transaction takes about 80ms 
on my system, with lot of disk head movements involved.
Since i need sqlite especially to be fast for one of my projects (speed 
is more important than possible data loss in case of an operating system 
crash) i've been investigating how to speed up sqlite. To accomplish 
that goal, i've recompiled sqlite3.dll and added an extra pragma to 
sqlite that disables disk flushing on each transaction.

If you want to test (please do not use for production at the moment!), 
you can download a modified sqlite dll from sourceforge:
http://sourceforge.net/project/showfiles.php?group_id=103463&package_id=131328

to disable disk flush, set the pragma win32_flush_buffers to off (it 
defaults to on):
PRAGMA win32_flush_buffers=off

below the readme of the zip, including a simple benchmark.

regards,

rene

-------------------------------------

Important information on this library
This is a custom build of SQLite 3 dynamic link library (dll) for windows.

WARNING: THIS BUILD CANNOT GUARANTEE SQLite ACID behaviour in case of OS 
crash!

Version: Sqlite 3.27 (modified)
Compiler: dev-c++ 4.9.9.2 (minGW)
Modifications: Added pragma for disabling file buffer flush on win32 
(Rene Tegel)

Usage:
Set the sqlite pragma win32_flush_buffers to on or off (defaults to on 
(safe)):
PRAGMA win32_flush_buffers=off

Motivation:
SQLite is very slow and disk-intensive when completing a transaction. 
This is mainly causes by calling the windows api FlushFileBuffers which 
does, probably, a lot more than intended (flushing file buffer to disk).
In case of an application crash, there should be no difference in ACID. 
In case of an operating system crash or power failure, the effects may 
be noticable.
However, by disabling buffer flush a transaction is handled about 80 
times faster on my system! Benchmarks below.

Recommended use:
NTFS file systems
Data logging applications
Other applications that involve numerous transactions but need perfarmance.
Make a backup of your database if you can't risk total data loss but 
still need the performance

Important notice:
I am not resposible for data loss. What is more: Richard Hipp (SQLite 
main developer) is not responsible for data loss or any modifications in 
this library. Do NOT contach him and/or the sqlite mailing list for 
issues caused by flushbuffers switched to off. The rest of the library 
should be identical.

Future compilations:
I will try to patch, compile and release newer versions. After testing, 
i may contact the sqlite mailing list and ask Richard Hipp to include 
the patch.



Benchmarks:
AS tested with sqlite 3.2.7 compiled with dev-c++ on win32
platform: win XP AMD 2500+



sqlite327 standard, non-optimized:

inserting 1 records
Seperate transactions 79 ms
Single transaction 78 ms

inserting 10 records
Seperate transactions 672 ms
Single transaction 79 ms

inserting 100 records
Seperate transactions 6719 ms
Single transaction 78 ms

inserting 1000 records
Seperate transactions 77359 ms
Single transaction 156 ms

inserting 10000 records
Seperate transactions 789453 ms
Single transaction 781 ms



sqlite3.27 optimized, disabled buffer flush:

inserting 1 records
Seperate transactions 0 ms
Single transaction 0 ms

inserting 10 records
Seperate transactions 16 ms
Single transaction 0 ms

inserting 100 records
Seperate transactions 156 ms
Single transaction 0 ms

inserting 1000 records
Seperate transactions 969 ms
Single transaction 78 ms

inserting 10000 records
Seperate transactions 9828 ms
Single transaction 594 ms