SQLite can live in memory alone, just connect to the database :memory:. The SQLite wiki has more information on SQLite Table in Memory.

Cool! But that is not using a database (file) that already exists (what I could find), but using memory from scratch! How to actually "move" the file there? How about any file?

???
open file
open memory
copy from file to memory
close file
1+1=2

That is called a ramdisk, just google for how to create one. On linux, you create it, and mount it to some mountpoint, then you can copy into it. However, you may NOT see any speed improvement, because if you have enough ram , the OS's memory management system will probably already keep the sqlite db file in memory, if it is used on a frequent basis. If you don't have enough ram, you shouldn't be using a ramdisk anyways.:-)

I asked a similar question when I first started, about putting perl and it's libs in a ramdisk, to improve speed. But I found that it was like above. It takes time for the OS to load it the first time, then after that, it keeps a copy in memory automatically, unless it is forced out by some other memory hog.

However, you may NOT see any speed improvement, because if you have enough ram , the OS's memory management system will probably already keep the sqlite db file in memory, if it is used on a frequent basis.

While this is true for many databases and other applications, it is not really true for SQLite. SQLite does an fsync() after every transaction, thus flushing out the cache, then closes and reopens the file. You therefore lose a lot of the OS's caching benefits, because changes will be written out immediately and reread. See my other post further down on how to get around that (another option IIRC is to run SQLite in non-synchronous mode, but that's only useful if you don't care much about your data :-).

Yes, this crossed my mind too! But I want control! :) Aka, KNOW its there! And Im also for a solution that works in Windows too! Thus, explicitly do it in Perl! (and not rely on some 3:rd part application, creating a Ramdisk...) But I guess the OS puts it there if used regularly.. (although it would be quite fonky to do this with Perl anyway :) )

Take a look at this free ram disk. I've been using it (occasionally) for a couple of years now both on NT and XP without problems.

Whether your application performance will benefit I don't know, but it very small and easy to install and it is certainly the easy option for what you are trying to do. It would take you less than 5 minutes to try it out.

---

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal?

"Science is about questioning the status quo. Questioning authority".

The "good enough" maybe good enough for the now, and perfection maybe unobtainable, but that should not preclude us from striving for perfection, when time, circumstance or desire allow.

As others have said, there's :memory: (though this suffers the deficiency of not allowing more than one connection and also makes persisting a pain), or you can create a virtual disk in memory (Refer to your operating system's documentation on how to do this, on Linux you could start with

In practice though I'd suggest you thoroughly benchmark any such solution (and benchmark it on your production system or a system which resembles it as closely as possible). You may find that you can achieve almost the same speed by intelligently designing your queries. The main speed hit SQLite takes is when it opens and closes the file at the beginning and end of a transaction. So, if you can perform all your work in a single transaction, rather than using a transaction for every query, you'll be almost as fast on a disk-based db as on a RAM-based one. For example the following quick-and-dirty benchmark:

#!/usr/bin/perl

use warnings;
use strict;
use DBI;
use Benchmark 'cmpthese';

my %db=( "on disk" => "/tmp/sqlitetest",
     "in mem"  => ":memory:",
     "ramdisk" => "/mnt/ramfs/sqlitetest" );

my $dbh;
for my $current_db (keys %db) {
  print "## Testing backing store $current_db: ##\n";
  $dbh = DBI->connect("dbi:SQLite:dbname=$db{$current_db}","","")
    or die "Couldn't create $current_db db";
  $dbh->do(qq{create table test ( id integer, text varchar, nuthertext
+ varchar)}) 
      or die $dbh->errstr();

  for my $queries (10,100,300) {
      print "$queries actions per connection:\n";
      cmpthese (-5, 
        { "no transact."    => sub {
            my $sth
            = $dbh->prepare(q(insert into test values(?,?,?)));
            for my $i(1..$queries) {
            $sth->execute($i,'No business','like perl business');
            my @row_ary 
                = $dbh->selectrow_array('select count(*) from test');
            }
            $sth->finish();
            
            $dbh->do(qq{delete from test});  
        },

          "transact." => sub { 
              $dbh->begin_work();
              my $sth = $dbh->prepare(q(insert into test values(?,?,?)
+));
              for my $i(1..$queries) {
              $sth->execute($i,'No business','like perl business');
              my @row_ary 
                  = $dbh->selectrow_array('select count(*) from test')
+;
              }
              $sth->finish();
              $dbh->do(qq{delete from test});
              $dbh->commit();
          }
          }
        );
  }
  $dbh->disconnect();
  unlink $db{$current_db};
}
[download]

## Testing backing store ramdisk: ##
10 actions per connection:
              Rate no transact.    transact.
no transact. 372/s           --         -37%
transact.    586/s          58%           --
100 actions per connection:
               Rate no transact.    transact.
no transact. 37.1/s           --         -44%
transact.    66.5/s          79%           --
300 actions per connection:
               Rate no transact.    transact.
no transact. 11.4/s           --         -44%
transact.    20.3/s          78%           --
## Testing backing store in mem: ##
10 actions per connection:
              Rate    transact. no transact.
transact.    621/s           --          -1%
no transact. 630/s           1%           --
100 actions per connection:
               Rate no transact.    transact.
no transact. 66.1/s           --          -0%
transact.    66.4/s           0%           --
300 actions per connection:
               Rate no transact.    transact.
no transact. 20.2/s           --          -0%
transact.    20.2/s           0%           --
## Testing backing store on disk: ##
10 actions per connection:
               Rate no transact.    transact.
no transact. 42.5/s           --         -84%
transact.     271/s         538%           --
100 actions per connection:
               Rate no transact.    transact.
no transact. 4.65/s           --         -92%
transact.    59.4/s        1177%           --
300 actions per connection:
               Rate no transact.    transact.
no transact. 1.58/s           --         -92%
transact.    19.6/s        1141%           --
[download]

There is really no way to force memory not to swap using a pure-Perl solution. This is something that is managed at the kernel level and is therefore very OS-dependent.

However, putting files in memory is possible in Perl, if you're using PerlIO (e.g. if you have perl >= 5.8.0). You can do this by:

open my $SRC, '<', $source_filename;

until (eof $SRC) {
   my $buffer;
   read($SRC, $buffer, 200_000);
   $memfile .= $buffer;
}

close $SRC;
open my $MEM, '<', \$memfile;

## now you can read from $MEM as a filehandle
[download]

The above is example code only, there's a lot of error-handling and other considerations missing.

For a SQLite2 DB, you could attach to the database as a file, then attach to the special database :memory:, and use a couple of SQL statements to transfer the entire DB to RAM, then do all of your queries from there.

All of that said, don't be so sure you'll gain anything by doing this. Depending on what your real problem is (i.e. "why do you want this?"), you might be better off profiling your existing code and removing bottlenecks.