Documentation Source Text

from the SQLite source tree.
To compile this test program, first gather the kvtest.c source file
into a directory with the [amalgamation|SQLite amalgamation] source
files "sqlite3.c" and "sqlite3.h".  Then on unix, run a command like
the following:

<codeblock>
gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c \
    -o kvtest -ldl -lpthread
</codeblock>

<p>Or on Windows with MSVC:

<codeblock>
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
</codeblock>
................................................................................
<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>

<img src="images/faster-read-sql.jpg">

<br>
Chart 1:  100K BLOBs, avg size 10KB, read in random order
using SQL
</center>

<p>
The performance can be improved slightly by bypassing the SQL layer
and reading the blob content directly using the
[sqlite3_blob_read()] interface, as shown in the next chart:

<center>

<img src="images/faster-read-blobapi.jpg">

<br>
Chart 2:  100K BLOBs, avg size 10KB, read in random order<br>
using sqlite3_blob_read()
</center>

<p>
Futher 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>

<img src="images/faster-read-mmap.jpg">

<br>
Chart 3:  100K BLOBs, avg size 10KB, read in random order<br>
using sqlite3_blob_read() from a memory-mapped database.
</center>

<p>
The third chart shows that reading blob content out of SQLite can be
twice as fast as reading from individual files on disk for Mac and
Android, and an amazing ten times faster for Windows.
................................................................................
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.

<center>

<img src="images/faster-write-safe.jpg">

<br>
Chart 4:  10K BLOBs, avg size 10KB, written in random order<br>
in WAL mode with synchronous NORMAL, exclusive of checkpoint time.

</center>

<p>
The android performance numbers for the write experiments are omitted
because the performance tests on the Galaxy S3 are so random.  Two
consecutive runs of the exact same experiment would give wildly different
times.  And, to be fair, the performance of SQLite on android is slightly
................................................................................
The next chart shows the performance of SQLite versus direct-to-disk
when transactions are disabled ([PRAGMA journal_mode|PRAGMA journal_mode=OFF])
and [PRAGMA synchronous] is set to OFF.  These settings put SQLite on an
equal footing with direct-to-disk writes, which is to say they make the
data prone to corruption due to system crashes and power failures.

<center>

<img src="images/faster-write-unsafe.jpg">

<br>
Chart 5:  10K BLOBs, avg size 10KB, written in random order<br>
in journaling disabled and with synchronous OFF.
</center>

<p>
In all of the write tests, it is important to disable anti-virus software
prior to running the direct-to-disk performance tests.  We found that
anti-virus software slows down direct-to-disk by an order of magnitude
whereas it impacts SQLite writes very little.  This is probably due to the

from the SQLite source tree.
To compile this test program, first gather the kvtest.c source file
into a directory with the [amalgamation|SQLite amalgamation] source
files "sqlite3.c" and "sqlite3.h".  Then on unix, run a command like
the following:

<codeblock>
gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ &#92;
  kvtest.c sqlite3.c -o kvtest -ldl -lpthread
</codeblock>

<p>Or on Windows with MSVC:

<codeblock>
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
</codeblock>
................................................................................
<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:  100K BLOBs, avg size 10KB, random order
using SQL
</center>

<p>
The performance can be improved slightly by bypassing the SQL layer
and reading the blob content directly using the
[sqlite3_blob_read()] interface, as shown in the next chart:

<center>
<div class='imgcontainer'>
<img src="images/faster-read-blobapi.jpg">
</div>
<br>
Chart 2:  100K BLOBs, avg size 10KB, random order<br>
using sqlite3_blob_read()
</center>

<p>
Futher 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:  100K BLOBs, avg size 10KB, random order<br>
using sqlite3_blob_read() from a memory-mapped database.
</center>

<p>
The third chart shows that reading blob content out of SQLite can be
twice as fast as reading from individual files on disk for Mac and
Android, and an amazing ten times faster for Windows.
................................................................................
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.

<center>
<div class='imgcontainer'>
<img src="images/faster-write-safe.jpg">
</div>
<br>
Chart 4:  10K BLOBs, avg size 10KB, random order<br>
in WAL mode with synchronous NORMAL,<br>
exclusive of checkpoint time.
</center>

<p>
The android performance numbers for the write experiments are omitted
because the performance tests on the Galaxy S3 are so random.  Two
consecutive runs of the exact same experiment would give wildly different
times.  And, to be fair, the performance of SQLite on android is slightly
................................................................................
The next chart shows the performance of SQLite versus direct-to-disk
when transactions are disabled ([PRAGMA journal_mode|PRAGMA journal_mode=OFF])
and [PRAGMA synchronous] is set to OFF.  These settings put SQLite on an
equal footing with direct-to-disk writes, which is to say they make the
data prone to corruption due to system crashes and power failures.

<center>
<div class='imgcontainer'>
<img src="images/faster-write-unsafe.jpg">
</div>
<br>
Chart 5:  10K BLOBs, avg size 10KB, random order<br>
in journaling disabled, synchronous OFF.
</center>

<p>
In all of the write tests, it is important to disable anti-virus software
prior to running the direct-to-disk performance tests.  We found that
anti-virus software slows down direct-to-disk by an order of magnitude
whereas it impacts SQLite writes very little.  This is probably due to the