Documentation Source Text

experiment we have not yet run.

<p>Remember that the relative performance of database reads and reads from
the filesystem will depend on both the hardware and the operating system.
Please try the tests above on your own system.  If you encounter cases
there database reads do not perform favorably in comparison to filesystem
reads, please report your findings in the [mailing lists|SQLite mailing list].

<h1>Update as of 2017-05-22</h1>

<p>The table below shows data collected using 
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] on five different
systems:  An old Dell laptop running Windows7, a new Lenovo laptop running
Windows10, a Mac-Pro, an Ubuntu desktop machine, and an older Android
phone (a Galaxy S3). All machines use SSD except the Dell which has a
hard-drive. The test database is 100K BLOBs uniformly
distributed between 8K and 12K in size, each.  The database page size
is 4KiB.  The chart shows average BLOB access time in microseconds.

<center>
<table border=1>
<tr>
<th>kvtest arguments
<th>Win7
<th>Win10
<th>MacPro
<th>Ubuntu
<th>Android
</tr>
<tr>
<td>(direct file access)
<td>100<td>46<td>7.9<td>3.2<td>145
<tr>
<td>(plain database access)
<td>22<td>6.9<td>4.4<td>2.4<td>128
<tr>
<td>--blob-api
<td>18<td>5.8<td>3.6<td>1.9<td>105
<tr>
<td>--blob-api --random
<td>28<td>8.4<td>4.8<td>2.6<td>135
<tr>
<td>--mmap 1G
<td>9<td>3.4<td>5.2<td>2.1<td>45
<tr>
<td>--mmap 1G --blob-api --random
<td>13<td>4.4<td>4.7<td>2.2<td>78
</table>
</center>

<p>
Every run of kvtest gives a slightly different time, of course.  The
numbers above are averages over between three and five runs and are
rounded to reflect the fact that they are imprecise.
Always remember:  <i>Your mileage may vary</i>.
Rerun these tests yourself on your own hardware using data that is
a close match to your production data before drawing conclusions.

<p>
Notice that the individual file access times on Windows are dramatically
slower than on the unix systems.  This might be because the 100K BLOBs are
all stored ina single directory and Windows is inefficient at searching
directories with large numbers of files.  The Windows file access times
might be improved if the BLOBs were stored in a hierarchy of directories,
rather than dumping them all into a single big directory.

<h2>Key Points</h2>

<ol>
<li><p>
Do not assume that directory file I/O is faster than using an SQLite database.
Reading from SQLite can be faster, sometimes much faster, than reading
separate files from disk.

<li><p>
The relative performance between direct file access and database access
depends a lot on the operating system, the hardware, and what database
access method is being used.  Make your own measurements.
</ol>