[yunqa.de] Re: DISQLite3 Load to Memory

  • From: Delphi Inspiration <delphi@xxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Sat, 12 Mar 2011 08:51:16 +0100

On 12.03.2011 03:16, Edwin Yip wrote:

> SQLITE query is very fast and it might not necessary to load the entire
> DB to memory before querying.
> 
> On Sat, Mar 12, 2011 at 6:59 AM, Alexandre Velinot <avelinot@xxxxxxxxx
> <mailto:avelinot@xxxxxxxxx>> wrote:
> 
>     I have a 100 mega bytes sqlite db file that I would like to load to
>     memory before performing sql queries.
>     Is it possible to do that with DISQLite3  ?

Adding to Edwin's answer:

1.

DISQLite3 is indeed very fast and it is mostly unnecessary to load
databases into memory. Depending on the database size, this loading may
take up considerable time up front. You will need to run lots of queries
to make up for this time-gap. In addition, the memory consumed will of
course be unavailable to other applications.

2.

Next it is important to know that DISQLite3 already loads parts of the
database file into memory when performing queries. These parts are
called database pages and DISQLite3 keeps those pages in its own
internal cache to reuse them if necessary.

In other words: Just by running a query, you are causing DISQLite3 to
load the database into memory - but since it only loads those pages it
needs for the query, this is usually faster than loading the entire
database up front.

You can control the page cache size by executing this SQL:

  PRAGMA cache_size=10000;

where 10000 should be the maximum number of pages you want to cache in
memory. For details read the documentation in

  DISQLite3.chm -> SQL Syntax -> PRAGMA.

3.

At last, it is possible to load the entire database into memory if none
of above satisfies your needs. The concept is to create an in-memory
database by opening a database named ':memory:'. Then you backup your
on-disk database to the memory database.

The relevant sections in the DISQLite3.chm documentation are:

  DISQLite3.chm -> SQLite3 Documentation -> In-Memory Database
  DISQLite3.chm -> SQLite3 Documentation -> Online Backup

A backup example project is available in

  \DISQLite3\Demos\\DISQLite3_Backup\

If you want to write your modified in-memory database back to disk you
just need to swap source and destination databases and run the backup again.

Steps 2. and 3. require DISQLite3 Pro, they do not work with DISQLite3
Personal.

Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa



Other related posts: