Documentation Source Text

when reading the blobs from individual files.  In other words, the
overhead of calling open() and close() is greater than the overhead
of using the database.  The size reduction arises from the fact that
individual files are padded out to the next multiple of the filesystem
block size, whereas the blobs are packed more tightly into an SQLite
database.

<h1>How Performance Was Measuremented</h1>

<p>The performance comparison is accomplished using the
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] program
found in the SQLite source tree.
To compile the 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>

Generate a test database with 100,000 random blobs, each 10,000 bytes in
size using a command like this:

<codeblock>
./kvtest init test1.db --count 100k --size 10k
</codeblock>

<p>
................................................................................
using commands like this:

<codeblock>
mkdir test1.dir
./kvtest export test1.db test1.dir
</codeblock>









<p>
Measure the performance for reading blobs from the database and from
individual files using these commands:

<codeblock>
./kvtest run test1.db --count 100k --blob-api
./kvtest run test1.dir --count 100k
................................................................................
will be amortized over more bytes transferred using read().  The break-even
point, the point where it becomes faster to read directly from the filesystem,
will vary from one system to another.

<p>The --blob-api option causes database reads to occur using the
[sqlite3_blob_open()], [sqlite3_blob_reopen()], and [sqlite3_blob_read()]
interfaces instead of using SQL statements.  Without the --blob-api
option, a separate SQL statement is run for reading each blob and
the performance of reading from the database is approximately
the same as the performance from reading directly from files.
This is still a significant finding, however, since few people would
expect an SQL database to run as fast as a direct read from a file
(much less faster) and yet it does.

<p>If the --random option is added to the "run" command, that causes the
blobs to be read in a random order.  This causes a noticable decrease in
the performance of database reads, since it forces more movement of B-Tree
cursors.  When --random is used and --blob-api is omitted, reading directly
from files on disk is generally a little faster, but reads from the database
are still competitive.

<h1>Other Considerations</h1>

<p>Other SQL database engines advise applications to store blobs in separate
files and then store the filename in the database.  In that case, where
the database must first be consulted to find the filename before opening
and reading the file, simply storing the entire blob in the database is
gives much faster read performance with SQLite.
See the [Internal Versus External BLOBs] article for more information.

<p>This report only looks at the performance of reads, not writes.

when reading the blobs from individual files.  In other words, the
overhead of calling open() and close() is greater than the overhead
of using the database.  The size reduction arises from the fact that
individual files are padded out to the next multiple of the filesystem
block size, whereas the blobs are packed more tightly into an SQLite
database.

<h1>How Performance Was Measured</h1>

<p>The performance comparison is accomplished using the
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] program
found in the SQLite source tree.
To compile the 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>
Use the resulting "kvtest" program to
generate a test database with 100,000 random blobs, each 10,000 bytes in
size using a command like this:

<codeblock>
./kvtest init test1.db --count 100k --size 10k
</codeblock>

<p>
................................................................................
using commands like this:

<codeblock>
mkdir test1.dir
./kvtest export test1.db test1.dir
</codeblock>

<p>
At this point, you can measure the amount of disk space used by
the test1.db database and the space used by the test1.dir directory
and all of its content.  On a standard Ubuntu Linux desktop, the
database file will be 1,024,512,000 bytes in size and the test1.dir
directory will use 1,228,800,000 bytes of space (according to "du -k"),
about 20% more than the database.

<p>
Measure the performance for reading blobs from the database and from
individual files using these commands:

<codeblock>
./kvtest run test1.db --count 100k --blob-api
./kvtest run test1.dir --count 100k
................................................................................
will be amortized over more bytes transferred using read().  The break-even
point, the point where it becomes faster to read directly from the filesystem,
will vary from one system to another.

<p>The --blob-api option causes database reads to occur using the
[sqlite3_blob_open()], [sqlite3_blob_reopen()], and [sqlite3_blob_read()]
interfaces instead of using SQL statements.  Without the --blob-api
option, a separate SQL statement is run to read each blob and
the performance of reading from the database is approximately
the same as the performance from reading directly from files.
This is still a significant finding, since few people would
expect a [full-featured SQL] database to run as fast as direct file reads,
and yet SQLite does.

<p>If the --random option is added to the "run" command, that causes the
blobs to be read in a random order.  This causes a noticable decrease in
the performance of database reads, since it forces more movement of B-Tree
cursors.  When --random is used and --blob-api is omitted, reading directly
from files on disk is generally a little faster, but reads from the database
are still competitive.

<h1>Other Considerations</h1>

<p>Some other SQL database engines advise developers to store blobs in separate
files and then store the filename in the database.  In that case, where
the database must first be consulted to find the filename before opening
and reading the file, simply storing the entire blob in the database is
gives much faster read performance with SQLite.
See the [Internal Versus External BLOBs] article for more information.

<p>This report only looks at the performance of reads, not writes.