That file thing is a bit strange indeed, I hope there's some solution to that.

The problem is finding someone with some expertise of the Windows port of sqlite.

BTW: As your data is already in an sqllite DB, rather than copying it all from the into this mode=memory DB(*), why not just open connections to the existing db file from each of the threads using the uri-form connect string + the cache=shared modifier and see how the performance pans out.

(*especially as it seems these involve IO anyway.)

Actually now that I'm looking into this more deeply, turns out I'm already doing exactly that. At least on OS X. There I can't get that :memory: to work with ?cache=shared, so it always uses a file in the end. I get this with your latest code:

$ ls -l file*
ls: file*: No such file or directory
$ ./test.pl > /dev/null
$ ls -l file*
-rw-r--r--  1  19456 Jul 24 22:46 file:memdb2?mode=memory&cache=shared
$ ./test3.pl > /dev/null
$ ls -l file*
-rw-r--r--  1  36864 Jul 24 22:46 file:memdb2?mode=memory&cache=shared
[download]

When I use 'dbi:SQLite:dbname=:memory:' alone, it creates the database in-memory (no file), but I can't share it so that's no use.

I've also tried all of these different URIs, but no joy (below are the corresponding files that get created):

CONNECT=> 'dbi:SQLite:dbname=file:memory:?mode=memory&cache=shared'
19456 Jul 24 23:15 file:memory:?mode=memory&cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory:?cache=shared'
19456 Jul 24 23:18 :memory:?cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory:\?cache=shared'
19456 Jul 24 23:19 :memory:\?cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory:cache=shared'
19456 Jul 24 23:20 :memory:cache=shared

CONNECT=> 'dbi:SQLite:dbname=:memory?cache=shared:'
19456 Jul 24 23:21 :memory?cache=shared:
[download]

So far when I use the file it's fast enough for what I need. But just out of curiosity, any chance that ?cache=share can be set outside of the URI in some way?

There I can't get that :memory: to work with ?cache=shared, so it always uses a file in the end.

You need to read the sqlite docs I linked above :)

It is:

• either: 'dbi:SQLite:dbname=:memory:' which creates a memory db; but cannot be shared between connections; thus, with the DBI restrictions, cannot be shared between threads under Perl.
• or: 'dbi:SQLite:dbname=file:filename?mode=memory&cache=shared' which (should) also create a memory db, but one that can be shared between distinct connection.

  (But that has this anomalous problem with the undeleted file. Leastwise, under Windows.)

I get this with your latest code:-rw-r--r--  1  19456 Jul 24 22:46 file:memdb2?mode=memory&cache=shared

That's interesting. And it may explain the problem under windows.

I was expecting that the uri syntax 'dbi:SQLite:dbname=memdb2?...' would create a file called (just) memdb2.

Ie. That the file: bit was just the URI scheme (equiv. to http:). But it seems that it is being taken as a part of the filename to be used for the db.

Which explains the problem with Windows in as much as Windows uses the syntax filename:streamname to access its Alternate File Streams.

I've also tried all of these different URIs, ...

In order to use your existing file from multiple threads (via multipe connects) you do need to use the URI-form of the connect.

Say your existing file is in the current directory, and is called 'mysqlite.db'; then (I think) you should use the connect string:

    CONNECT=> 'dbi:SQLite:dbname=file:mysqlite.db?cache=shared'
[download]

If it is in some other place then:

    CONNECT=> 'dbi:SQLite:dbname=file:/path/to/mysqlite.db?cache=share
+d'
[download]

I can't verify this at the moment, so see how you get on with that. (And read the docs to see if you agree with me.)

---

With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'

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

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

In the absence of evidence, opinion is indistinguishable from prejudice.