Documentation Source Text

Check-in [1539b18574]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:New hyperlinks to the faster-than-filesystem paper.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1539b1857418371d70ad76fc493053ef837218566016188de567d8656a6dd9fc
User & Date: drh 2017-06-06 15:16:41.901
Context
2017-06-06
17:29
Tweaks to the faster-than-filesystem document to make it more mobile-friendly. (check-in: fb0299e43a user: drh tags: trunk)
15:16
New hyperlinks to the faster-than-filesystem paper. (check-in: 1539b18574 user: drh tags: trunk)
15:03
Update the "35% faster than the filesystem" document to talk about write performance. (check-in: d6c152123c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/appfileformat.in.
313
314
315
316
317
318
319
320
321

322

323
324
325
326
327
328
329
but doing is often much harder.  If indices are added, then all application
code that changes the corresponding tables must be located and modified to
keep those indices up-to-date.  If columns are added, then all application
code that accesses the corresponding table must be located and modified to 
take into account the new columns.

<li><p><b>Performance.</b>
In many cases, an SQLite application file format will be faster than a
custom or pile-of-files format.  In the case of a custom format, SQLite

often dramatically improves start-up times because instead of having to

read and parse the entire document into memory, the application can
do queries to extract only the information needed for the initial screen.
As the application progresses, it only needs to load as much material as
is needed to draw the next screen, and can discard information from
prior screens that is no longer in use.  This helps keep the memory
footprint of the application under control.








|
|
>
|
>







313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
but doing is often much harder.  If indices are added, then all application
code that changes the corresponding tables must be located and modified to
keep those indices up-to-date.  If columns are added, then all application
code that accesses the corresponding table must be located and modified to 
take into account the new columns.

<li><p><b>Performance.</b>
In many cases, an SQLite application file format will be 
[faster than the filesystem|faster than a pile-of-files format] or
a custom format.  In addition to being faster for raw read and
writes, SQLite can often dramatically improves start-up times because 
instead of having to
read and parse the entire document into memory, the application can
do queries to extract only the information needed for the initial screen.
As the application progresses, it only needs to load as much material as
is needed to draw the next screen, and can discard information from
prior screens that is no longer in use.  This helps keep the memory
footprint of the application under control.

Changes to pages/intern-v-extern-blob.in.
19
20
21
22
23
24
25







26
27
28
29
30
31
32
For each test case, a database was created that contains 100MB of BLOB
content.  The sizes of the BLOBs ranged from 10KB to 1MB.  The number
of BLOBs varied in order to keep the total BLOB content at about 100MB.
(Hence, 100 BLOBs for the 1MB size and 10000 BLOBs for the 10K size and
so forth.)  SQLite [version 3.7.8] ([dateof:3.7.8]) was used.
</p>








<p>
The matrix below shows the time needed to read BLOBs stored in separate files
divided by the time needed to read BLOBs stored entirely in the database.  
Hence, for numbers larger than 1.0, it is faster to store the BLOBs directly 
in the database.  For numbers smaller than 1.0, it is faster to store the BLOBs
in separate files.
</p>







>
>
>
>
>
>
>







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
For each test case, a database was created that contains 100MB of BLOB
content.  The sizes of the BLOBs ranged from 10KB to 1MB.  The number
of BLOBs varied in order to keep the total BLOB content at about 100MB.
(Hence, 100 BLOBs for the 1MB size and 10000 BLOBs for the 10K size and
so forth.)  SQLite [version 3.7.8] ([dateof:3.7.8]) was used.
</p>

<blockquote><i>
Update: New measurements for SQLite version 3.19.0
([dateof:3.19.0]) show that SQLite is about 
[faster than the filesystem|35% faster] than direct disk I/O for 
both reads and writes of 10KB blobs.
</i></blockquote>

<p>
The matrix below shows the time needed to read BLOBs stored in separate files
divided by the time needed to read BLOBs stored entirely in the database.  
Hence, for numbers larger than 1.0, it is faster to store the BLOBs directly 
in the database.  For numbers smaller than 1.0, it is faster to store the BLOBs
in separate files.
</p>