in reply to Re^10: threads::shared seems to kill performance (Workaround).
in thread threads::shared seems to kill performance

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:

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.

Replies are listed 'Best First'.
Re^12: threads::shared seems to kill performance (Workaround).
by Jacobs (Novice) on Jul 24, 2013 at 22:46 UTC

    Actually, those URI question mark query parameters don't seem to be getting across - they're just considered part of the filename for whatever reason: 

        CONNECT=> 'dbi:SQLite:dbname=file:mysqlite.db?cache=shared'
19456 Jul 25 00:32 file:mysqlite.db?cache=shared

    CONNECT=> 'dbi:SQLite:dbname=mysqlite.db?cache=shared'
19456 Jul 25 00:34 mysqlite.db?cache=shared

    [download]
    I'm getting exactly the same results on OS X 10.8 and Ubuntu 13.04. I even tried updating DBI and DBD::SQLite to the latest versions, but with no effect. I'm using the exact code you posted today and I'm just changing the CONNECT constant as shown above.

    I must be doing something very stupid I just can't find what that is :-(

[reply]
[d/l]

      Did you read the "backwards compatibility" section of http://www.sqlite.org/uri.html?

      Now I've understood the reason behind the straems thing; it seems likely that the DBD::Sqlite(2) package is not configuring the compile to allow the URI-forms.

      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.
[reply]

        After recompiling the SQLite used by DBD::SQLite with SQLITE_USE_URI, it works flawlessly!

        Btw I did read that document about 17 times, but I always skipped the Backwards compatibility section as not important and focused on the Format instead. And then banged my head against the wall wondering why it doesn't work :-(

        Just one last thing - do you know if I can somehow use any of those other 2 ways to enable this via DBD::SQLite? I want to avoid having to recompile SQLite on every machine on which I use this. They seem to be meant primarily for the C interface, but I wonder if there's some way to invoke them from Perl as well:

        • The sqlite3_config(SQLITE_CONFIG_URI, 1); configuration option is set at application start-time.
        • The SQLITE_OPEN_URI bit is OR-ed in with the set bits passed in as the 3rd parameter to the sqlite3_open_v2() interface.

[reply]

In Section Seekers of Perl Wisdom