As stated during our
recent benchmarks, the default SQlite3 write speed is quite slow,
when running on a normal hard drive. By default, the engine will pause after
issuing a OS-level write command. This guarantees that the data is written to
the disk, and features the ACID
properties of the database
engine.
ACID
is an acronym for "Atomicity Consistency Isolation
Durability" properties, which guarantee that database transactions are
processed reliably: for instance, in case of a power loss or hardware failure,
the data will be saved on disk in a consistent way, with no potential loss of
data.
You can overwrite this default behavior by setting the
TSQLDataBase.Synchronous
property to smOff
instead of
the default smFull
setting. When Synchronous
is set
to smOff
, SQLite continues without syncing as soon as it
has handed data off to the operating system. If the application running
SQLite crashes, the data will be safe, but the database might become
corrupted if the operating system crashes or the computer loses power before
that data has been written to the disk surface. On the other hand, some
operations are as much as 50 or more times faster with this setting.
When the same tests are performed with Synchronous :=
smOff
, "Write one" speed is enhanced from 8-9 rows per second into about
400 rows per second, on a physical hard drive (SSD or NAS drives may not suffer
from this delay). We'll show below the detailed benchmark results.
So depending on your application requirements, you may switch Synchronous
setting to off, to enhance server-side responsiveness.