SQLite Forum

partial file load?
Login

partial file load?

(1) By Johan Lammens (jlammens) on 2021-05-28 13:58:22 [link]

Is it possible to partially load a database file upon opening it, rather than reading it completely into memory? I have a use case where I need to access just one small table from a series of database files, some of which are quite large, and the process takes long because apparently the entire file is loaded before any table can be accessed. In particular, I only need to access the sqlite_master table from these files.

Thanks for any and all help,
Johan

(2) By Larry Brasfield (larrybr) on 2021-05-28 14:08:58 in reply to 1 [link]

> because apparently the entire \[database\] file is loaded before any table can be accessed.

What leads you to believe that? I think once you discover the actual cause of the delay you seek to avoid, you will not feel a need to induce "partial file load" (which happens without much effort.)

(4) By Johan Lammens (jlammens) on 2021-05-28 14:29:12 in reply to 2 [link]

What leads me to believe that is the time it takes to open the db file in any client I've tried (e.g. SQLite Expert or python sqlite3 interface), and the corresponding disk activity I see until the database becomes available. Large db files (several GB) take minutes, small files (few MB) take seconds. The sequence of operations is open db file, run the sql script below, close db file:

SELECT 
  'n'||'='||count(*)||'+'||group_concat(namelen,'+') as db_schema
FROM (
  select name, name||'='||length(sql) as namelen, type from sqlite_master
  WHERE type = 'table' and name not like 'Persistent%'
  order by name
  )
group by type
;

(6) By Larry Brasfield (larrybr) on 2021-05-28 14:44:02 in reply to 4 [link]

From that evidence, it is reasonable to guess that SQLite reads the whole DB file upon open. But it is unreasonable to conclude so. As Richard says, SQLite reads in what it must to satisfy queries. It does read in whole "pages" at a time, so that "must" includes portions of pages which may not be strictly required. Possibly, your large DB files have bigger pages, which would include the one(s) in which the sqlite_schema (aka "sqlite_master) table resides.

You need to discover why all that disk activity is occurring. There is no SQLite option to avoid it because SQLite avoids it without being told. Your SQLite library wrapper might be responsible, or there may be things happening not yet apparent to you.  (Those are my guesses.)

(9) By Johan Lammens (jlammens) on 2021-05-28 15:12:50 in reply to 6 [link]

OK, thanks for your response.

(7) By Tim Streater (Clothears) on 2021-05-28 14:53:54 in reply to 4 [link]

Have you tried doing the same using the SQLite shell program? (sqlite3.exe, sqlite3.app, ...)

(10) By Johan Lammens (jlammens) on 2021-05-28 15:13:14 in reply to 7 [link]

Yes, sqlite3.exe shows the same behavior.

(11) By Richard Hipp (drh) on 2021-05-28 15:14:53 in reply to 10 [link]

Might there be some kind of virus scanner on Mr. Lammens's Windows machine
that is causing the observed behavior?

(8) By RandomCoder on 2021-05-28 14:56:42 in reply to 4 [link]

I suspect there must be more going on than is present in your description.

If I run this query on a instrumented build of SQLite that logs all reads/writes and point it at a 16709362688 byte SQLite database, the process reads only 4712 bytes from the file.  Indeed it finished very quickly, suggesting not all of the 16gb file was read.

(12) By Johan Lammens (jlammens) on 2021-05-28 15:15:59 in reply to 8 [link]

Thanks for looking into this, I will need to investigate further.

(3) By Richard Hipp (drh) on 2021-05-28 14:23:56 in reply to 1

SQLite reads the minimal amount of the file that it can get away with
in order to answer your query precisely.  What makes you think that
it reads the entire file into memory?

(5) By Johan Lammens (jlammens) on 2021-05-28 14:35:51 in reply to 3 [link]

see my response just before; here is a formatted version of the script:

```
SELECT 
  'n'||'='||count(*)||'+'||group_concat(namelen,'+') as db_schema
FROM (
  select name, name||'='||length(sql) as namelen, type from sqlite_master
  WHERE type = 'table' and name not like 'Persistent%'
  order by name
  )
group by type
;
```

(13) By Johan Lammens (jlammens) on 2021-05-28 15:34:45 in reply to 1 [link]

You are all right - there was indeed an intervening process (gunzipping the db file before it was opened) that was responsible for the delay. On an uncompressed db file the response time is very good.

Apologies for the confusion, and thanks all for your help!
Johan