cost of sqlite3_open

[Christoph Schreiber]

Hi, I'm working on a multi-threaded image server and I have 2 questions:

1) How "expensive" is a call to sqlite3_open. Does a call to sqlite3_enable_shared_cache make it "cheaper"?

2) If sqlite3_open is an expensive operation then I would like to keep a list (cache) of open database connections (sqlite*'s). Here's how it *should* work: The main thread calls sqlite3_open a couple of times and pushes the resulting sqlite-handles into a concurrent queue. The worker thread then pops the handle from the queue, does what needs to be done and pushes the handle back. No sqlite-handle is used by more than one thread at the same time. Is that safe?

Cheers, Christoph

[Pavel Ivanov]

Post by Christoph Schreiber
1) How "expensive" is a call to sqlite3_open. Does a call to sqlite3_enable_shared_cache make it "cheaper"?

Its cost depends on the size of your schema as it gets parsed during
open. Shared cache doesn't make it cheaper. It can make somewhat
cheaper (in some cases) to use several connections to the same
database with the cost of mutual exclusion of sqlite3_step calls
working with any handle to the same database. But that's it.

Post by Christoph Schreiber
2) If sqlite3_open is an expensive operation then I would like to keep a list (cache) of open database connections (sqlite*'s). Here's how it *should* work: The main thread calls sqlite3_open a couple of times and pushes the resulting sqlite-handles into a concurrent queue. The worker thread then pops the handle from the queue, does what needs to be done and pushes the handle back. No sqlite-handle is used by more than one thread at the same time. Is that safe?

Yes, that's perfectly safe.


Pavel

Post by Christoph Schreiber
1) How "expensive" is a call to sqlite3_open. Does a call to sqlite3_enable_shared_cache make it "cheaper"?
2) If sqlite3_open is an expensive operation then I would like to keep a list (cache) of open database connections (sqlite*'s). Here's how it *should* work: The main thread calls sqlite3_open a couple of times and pushes the resulting sqlite-handles into a concurrent queue. The worker thread then pops the handle from the queue, does what needs to be done and pushes the handle back. No sqlite-handle is used by more than one thread at the same time. Is that safe?
Cheers, Christoph
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Jean-Christophe Deschamps]

Pavel,

Post by Christoph Schreiber

Post by Christoph Schreiber
1) How "expensive" is a call to sqlite3_open. Does a call to

sqlite3_enable_shared_cache make it "cheaper"?
Its cost depends on the size of your schema as it gets parsed during
open.

Isn't this contradictory with an answer by Igor made in a recent thread?

Post by Christoph Schreiber
Subject: Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

Post by Christoph Schreiber
Is there a reason that sqlite3_open() will happily "open" a non-sqlite
file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which
would seem a more obvious return value?

SQLite doesn't actually touch the file until the first "substantive"
statement is executed on the connection. This allows one to set
various PRAGMAs that can only be set before the database is created.

[Pavel Ivanov]

Post by Jean-Christophe Deschamps

Post by Christoph Schreiber

Post by Christoph Schreiber
1) How "expensive" is a call to sqlite3_open. Does a call to

sqlite3_enable_shared_cache make it "cheaper"?
Its cost depends on the size of your schema as it gets parsed during
open.

Isn't this contradictory with an answer by Igor made in a recent thread?

It's not contradictory. I say that "real cost" of sqlite3_open is
parsing the schema. Igor says that this cost is actually deferred from
inside sqlite3_open call to the first "substantive" sqlite3_step call.
So you will have to pay this price anyway, just profiler output would
be somewhat confusing.


Pavel

On Tue, Nov 30, 2010 at 1:50 PM, Jean-Christophe Deschamps

Post by Jean-Christophe Deschamps
Pavel,

Post by Christoph Schreiber

Post by Christoph Schreiber
1) How "expensive" is a call to sqlite3_open. Does a call to

sqlite3_enable_shared_cache make it "cheaper"?
Its cost depends on the size of your schema as it gets parsed during
open.

Isn't this contradictory with an answer by Igor made in a recent thread?

Post by Christoph Schreiber
Subject: Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs

Post by Christoph Schreiber
Is there a reason that sqlite3_open() will happily "open" a non-sqlite
file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which
would seem a more obvious return value?

SQLite doesn't actually touch the file until the first "substantive"
statement is executed on the connection. This allows one to set
various PRAGMAs that can only be set before the database is created.

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

[Jean-Christophe Deschamps]

Post by Pavel Ivanov
It's not contradictory. I say that "real cost" of sqlite3_open is
parsing the schema. Igor says that this cost is actually deferred from
inside sqlite3_open call to the first "substantive" sqlite3_step call.
So you will have to pay this price anyway, just profiler output would
be somewhat confusing.

I agree that the price has to be paid somewhere in time. The issue I
raised was about the precise moment in was occuringin time, nothing
else. Nothing really important anyway.