Documentation Source Text

of content.  The database page size
is 4KiB.  The -DSQLITE_DIRECT_OVERFLOW_READ compile-time option was
used for all of these tests.
Tests were run multiple times.
The first run was used to warm up the cache and its timings were discarded.

<p>
The chart below shows average time to read a blob from the SQLite database,
relative to the time needed to read the same blob
from a file in the filesystem.  Smaller is better.  The actual timings
vary considerably from one system to another (the Ubuntu desktop is much

faster than the Galaxy S3 phone, for example).  
This chart shows the ratio of the
times needed to read blobs from SQLite divided by the times needed to
read the same blob directly from disk.  The left-most column in the chart
is the time needed to read from disk divided by itself, and thus always
has a value of 1.00.  That column is included for visual reference only.

<p>
In this chart, an SQL statement ("SELECT v FROM kv WHERE k=?1") 
is prepared once.  Then for each blob, the blob key value is bound 
to the ?1 parameter and the statement is evaluated to extract the
blob content.






<center>
<div class='imgcontainer'>
<img src="images/faster-read-sql.jpg">
</div>
<br>
Chart 1:  SQLite read latency relative to direct filesystem reads.<br>
100K blobs, avg 10KB each, random order using SQL
................................................................................
using sqlite3_blob_read().
</center>

<p>
Further performance improves can be made by using the
[memory-mapped I/O] feature of SQLite.  In the next chart, the
entire 1GB database file is memory mapped and blobs are read
(in random order) using the [sqlite3_blob_read()] interface:



<center>
<div class='imgcontainer'>
<img src="images/faster-read-mmap.jpg">
</div>
<br>
Chart 3:  SQLite read latency relative to direct filesystem reads.<br>
................................................................................
FlushFileBuffers().  In other words, there is no attempt to make the
direct-to-disk writes transactional or power-safe.
We found that invoking fsync() or FlushFileBuffers() on each file
written causes direct-to-disk storage
to be about 10 times or more slower than writes to SQLite.

<p>
The next chart compares raw direct-to-disk overwrites against
database updates on the SQLite database in [WAL mode] with
[PRAGMA synchronous] set to NORMAL.
All database writes are in a single transaction.
The timer for the database writes is stopped after the transaction
commits, but before a [checkpoint] is run.
Note that the SQLite writes, unlike the direct-to-disk writes,
are [transactional] and [power-safe], though because the synchronous
setting is NORMAL instead of FULL, the transactions are not durable.

of content.  The database page size
is 4KiB.  The -DSQLITE_DIRECT_OVERFLOW_READ compile-time option was
used for all of these tests.
Tests were run multiple times.
The first run was used to warm up the cache and its timings were discarded.

<p>
The chart below shows average time to read a blob directly from the
filesystem versus the time needed to read the same blob from the SQLite 
database.
The actual timings vary considerably from one system to another 
(the Ubuntu desktop is much
faster than the Galaxy S3 phone, for example).  
This chart shows the ratio of the
times needed to read blobs from a file divided by the time needed to
from the database.  The left-most column in the chart is the normalized

time to read from the database, for reference.

<p>
In this chart, an SQL statement ("SELECT v FROM kv WHERE k=?1") 
is prepared once.  Then for each blob, the blob key value is bound 
to the ?1 parameter and the statement is evaluated to extract the
blob content.

<p>
The chart shows that on Windows10, content can be read from the SQLite
database about 5 times faster than it can be read directly from disk.
On Android, SQLite is only about 35% faster than reading from disk.

<center>
<div class='imgcontainer'>
<img src="images/faster-read-sql.jpg">
</div>
<br>
Chart 1:  SQLite read latency relative to direct filesystem reads.<br>
100K blobs, avg 10KB each, random order using SQL
................................................................................
using sqlite3_blob_read().
</center>

<p>
Further performance improves can be made by using the
[memory-mapped I/O] feature of SQLite.  In the next chart, the
entire 1GB database file is memory mapped and blobs are read
(in random order) using the [sqlite3_blob_read()] interface.
With these optimizations, SQLite is twice as fast as Android
or MacOS-X and over 10 times faster than Windows.

<center>
<div class='imgcontainer'>
<img src="images/faster-read-mmap.jpg">
</div>
<br>
Chart 3:  SQLite read latency relative to direct filesystem reads.<br>
................................................................................
FlushFileBuffers().  In other words, there is no attempt to make the
direct-to-disk writes transactional or power-safe.
We found that invoking fsync() or FlushFileBuffers() on each file
written causes direct-to-disk storage
to be about 10 times or more slower than writes to SQLite.

<p>
The next chart compares SQLite database updates in [WAL mode]
against raw direct-to-disk overwrites of separate files on disk.
The [PRAGMA synchronous] setting is NORMAL.
All database writes are in a single transaction.
The timer for the database writes is stopped after the transaction
commits, but before a [checkpoint] is run.
Note that the SQLite writes, unlike the direct-to-disk writes,
are [transactional] and [power-safe], though because the synchronous
setting is NORMAL instead of FULL, the transactions are not durable.