Re: [sqlite] In-memory database with persistent storage

[Amit Chaudhuri]

..representation of a decent sized real world network..

On Tue, Mar 22, 2011 at 10:59 AM, Bart Smissaert
wrote:

> > through a version of dijkstra's routing algorithm
>
> Just out of interest, what data is this working on?
>
> RBS
>
> On Tue, Mar 22, 2011 at 7:25 AM, Amit Chaudhuri
>  wrote:
> > [Not at all expert in sqlite but here's a practical example of speed up
> > using ":memory:" and perhaps a slightly different strategy for getting at
> > the persistent data.]
> >
> > I use sqlite3 with Qt4 / C++ for an application which reads in an
> undirected
> > graph and then chunks through a version of dijkstra's routing algorithm.
>  A
> > colleague runs this on his machine and it takes all night on a large
> network
> > running on a database on disk.  On my own machine which is more powerful
> it
> > probably runs a lot faster but still takes a couple of hours plus.
>  Changing
> > to an in memory database, reading data in and processing in memory brings
> > the run time down to a couple of minutes.  So yes - running in memory can
> be
> > much quicker.  At the end of the run I attach an on disk database and
> copy
> > out the tables I need to save using "create table select" .
> >
> > A
> >
> > On Mon, Mar 21, 2011 at 1:13 PM, Simon Friis  wrote:
> >
> >> I know how to create a database that exists only in memory by using
> >> the :memory: filename. This however, creates a new database every time
> >> and it can not be saved.
> >>
> >> Is is possible to make SQLite load a database file into memory and
> >> then save it back to the file again when the connection to the
> >> database is closed?
> >>
> >> Would it improve speed?
> >>
> >> - paldepind
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory database with persistent storage

[Bart Smissaert]

> through a version of dijkstra's routing algorithm

Just out of interest, what data is this working on?

RBS

On Tue, Mar 22, 2011 at 7:25 AM, Amit Chaudhuri
 wrote:
> [Not at all expert in sqlite but here's a practical example of speed up
> using ":memory:" and perhaps a slightly different strategy for getting at
> the persistent data.]
>
> I use sqlite3 with Qt4 / C++ for an application which reads in an undirected
> graph and then chunks through a version of dijkstra's routing algorithm.  A
> colleague runs this on his machine and it takes all night on a large network
> running on a database on disk.  On my own machine which is more powerful it
> probably runs a lot faster but still takes a couple of hours plus.  Changing
> to an in memory database, reading data in and processing in memory brings
> the run time down to a couple of minutes.  So yes - running in memory can be
> much quicker.  At the end of the run I attach an on disk database and copy
> out the tables I need to save using "create table select" .
>
> A
>
> On Mon, Mar 21, 2011 at 1:13 PM, Simon Friis  wrote:
>
>> I know how to create a database that exists only in memory by using
>> the :memory: filename. This however, creates a new database every time
>> and it can not be saved.
>>
>> Is is possible to make SQLite load a database file into memory and
>> then save it back to the file again when the connection to the
>> database is closed?
>>
>> Would it improve speed?
>>
>> - paldepind
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory database with persistent storage

[Amit Chaudhuri]

[Not at all expert in sqlite but here's a practical example of speed up
using ":memory:" and perhaps a slightly different strategy for getting at
the persistent data.]

I use sqlite3 with Qt4 / C++ for an application which reads in an undirected
graph and then chunks through a version of dijkstra's routing algorithm.  A
colleague runs this on his machine and it takes all night on a large network
running on a database on disk.  On my own machine which is more powerful it
probably runs a lot faster but still takes a couple of hours plus.  Changing
to an in memory database, reading data in and processing in memory brings
the run time down to a couple of minutes.  So yes - running in memory can be
much quicker.  At the end of the run I attach an on disk database and copy
out the tables I need to save using "create table select" .

A

On Mon, Mar 21, 2011 at 1:13 PM, Simon Friis  wrote:

> I know how to create a database that exists only in memory by using
> the :memory: filename. This however, creates a new database every time
> and it can not be saved.
>
> Is is possible to make SQLite load a database file into memory and
> then save it back to the file again when the connection to the
> database is closed?
>
> Would it improve speed?
>
> - paldepind
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory database with persistent storage

[Simon Friis]

Thanks! The Online Backup API does exactly what I want.

2011/3/21 Drake Wilson :
> That depends on your application.  In general if you think too many
> file accesses is a speed problem and you are willing to spend more
> memory to deal with it, some of the first things to try would be
> fiddling with the cache_size, synchronous, and journal_mode PRAGMAs,
> depending on what tradeoffs you want to make.

I'm thinking of using it in a relatively small database which is going
to be accessed often. But maybe increasing the cache_size would be
better. Maybe having it all in memory could be problematic if it's not
big enough.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory database with persistent storage

[Drake Wilson]

Quoth Simon Friis , on 2011-03-21 14:13:46 +0100:
> Is is possible to make SQLite load a database file into memory and
> then save it back to the file again when the connection to the
> database is closed?

You cannot do that with a single database exactly, but you can use the
Online Backup API  to copy all data
from a file database into a memory database and then copy the new data
back when you are finished.

> Would it improve speed?

That depends on your application.  In general if you think too many
file accesses is a speed problem and you are willing to spend more
memory to deal with it, some of the first things to try would be
fiddling with the cache_size, synchronous, and journal_mode PRAGMAs,
depending on what tradeoffs you want to make.

> - paldepind

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory database with persistent storage

[Igor Tandetnik]

Simon Friis  wrote:
> I know how to create a database that exists only in memory by using
> the :memory: filename. This however, creates a new database every time
> and it can not be saved.

It can, with backup API: http://www.sqlite.org/backup.html

> Is is possible to make SQLite load a database file into memory and
> then save it back to the file again

Yes - with backup API.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] In-memory database with persistent storage

[Simon Friis]

I know how to create a database that exists only in memory by using
the :memory: filename. This however, creates a new database every time
and it can not be saved.

Is is possible to make SQLite load a database file into memory and
then save it back to the file again when the connection to the
database is closed?

Would it improve speed?

- paldepind
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users