SQLite in memory database from SQLite (3.x) file database?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite in memory database from SQLite (3.x) file database?

John Duprey
Is it possible to load an SQLite file database into an SQLite "in
memory" database?  If so what is the most efficient method to do this?
 I'm looking for the fastest possible performance.  Taking out the
disk I/O seems like the way to go.

Thanks,
-John
Reply | Threaded
Open this post in threaded view
|

Re: SQLite in memory database from SQLite (3.x) file database?

Jay Sprenkle
> Is it possible to load an SQLite file database into an SQLite "in
> memory" database?  If so what is the most efficient method to do this?
>  I'm looking for the fastest possible performance.  Taking out the
> disk I/O seems like the way to go.

create a memory database, attach the file based database,
insert using a select as the data source and it should work pretty well.
Reply | Threaded
Open this post in threaded view
|

Re: SQLite in memory database from SQLite (3.x) file database?

Cory Nelson
In reply to this post by John Duprey
assuming you open up a :memory: database:

attach 'foo.db' as bar;
create table baz as select * from bar.baz;
detach bar;

doesn't copy indexes, so you'll have to remake them.  dont think it
copies triggers either.

On 7/6/05, John Duprey <[hidden email]> wrote:
> Is it possible to load an SQLite file database into an SQLite "in
> memory" database?  If so what is the most efficient method to do this?
>  I'm looking for the fastest possible performance.  Taking out the
> disk I/O seems like the way to go.
>
> Thanks,
> -John
>


--
Cory Nelson
http://www.int64.org
Reply | Threaded
Open this post in threaded view
|

Re: SQLite in memory database from SQLite (3.x) file database?

John Duprey
Interesting.. can multiple threads share the same in-memory database
through multiple sqlite_open()s? From what I can scrape together from
the wiki page http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase),
it sounds like the best one could do is create the in memory db handle
once in the main thread, then provide serial access to the db handle
via a get method for all the each thread.

It would be nice to have option that just loads the db file into
memory or  otherwise caches the contents wholly in memory.  Are there
any caching options in sqlite that would mirror this behavior?

Thanks,
-John

On 7/6/05, Cory Nelson <[hidden email]> wrote:

> assuming you open up a :memory: database:
>
> attach 'foo.db' as bar;
> create table baz as select * from bar.baz;
> detach bar;
>
> doesn't copy indexes, so you'll have to remake them.  dont think it
> copies triggers either.
>
> On 7/6/05, John Duprey <[hidden email]> wrote:
> > Is it possible to load an SQLite file database into an SQLite "in
> > memory" database?  If so what is the most efficient method to do this?
> >  I'm looking for the fastest possible performance.  Taking out the
> > disk I/O seems like the way to go.
> >
> > Thanks,
> > -John
> >
>
>
> --
> Cory Nelson
> http://www.int64.org
>
Reply | Threaded
Open this post in threaded view
|

Re: SQLite in memory database from SQLite (3.x) file database?

Dirk Theisen
Hello, John!

> It would be nice to have option that just loads the db file into
> memory or  otherwise caches the contents wholly in memory.  Are there
> any caching options in sqlite that would mirror this behavior?

You could set the cache size as big as your database file (via pragma).
This should load all (used) data into memory. This is probably not a
good solution if your application does not run for a longer time.

Greetings,
   Dirk

Reply | Threaded
Open this post in threaded view
|

RE: SQLite in memory database from SQLite (3.x) file database?

Damian Slee
In reply to this post by John Duprey
You could create a new os_ram.c file, with new versions of these functions, but instead of read/writing to disk you are read/writing from a linked list of filenames, of which each could contain a ptr to the file data.  You would then be responsible for copying it back to disk at some point.  And loading it from disk when it starts, into your psuedo ram file system.

I guess this would also allow multithreaded access to an in memory database (if the locking is also implemented).  Where as opening the "%memory%" one, is only single threaded (in the cache), I think?

But you loose sharing the db across processes.   Which I don't think matters to you, if you want to load it all into ram anyway.

sqlite3OsOpenReadWrite
sqlite3OsOpenExclusive
sqlite3OsOpenReadOnly
sqlite3OsClose
sqlite3OsRead
sqlite3OsWrite
...
 

-----Original Message-----
From: Dirk Theisen [mailto:[hidden email]]
Sent: Thursday, July 07, 2005 7:09 AM
To: [hidden email]
Subject: Re: [sqlite] SQLite in memory database from SQLite (3.x) file database?

Hello, John!

> It would be nice to have option that just loads the db file into
> memory or  otherwise caches the contents wholly in memory.  Are there
> any caching options in sqlite that would mirror this behavior?

You could set the cache size as big as your database file (via pragma).
This should load all (used) data into memory. This is probably not a good solution if your application does not run for a longer time.

Greetings,
   Dirk


--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 6/07/2005
 

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 6/07/2005