9

I am a newbie for SQLite and I have read that the Serialized mode of SQLite is thread-safe, and can be safely used by multiple threads with no restriction.

My question is: In a single-threaded app, is there any performance impact if I use one same global connection for all database operation comparing to using one connection per database operation?(Ignore the performance impact for building db connection)

To be specific, imagine such a scenario: in the same thread, I need build two prepared statement to query two table respectively in the same dababase, and I need use STEP() to retrieve data from the two statement alternatively. My question is which will have better performance: I. Using one same connection for both statement; II. one connection per statement?(not account for the performance impact occurred in the process of building connection) Or is it necessary to use a connection pool for performance benefit?

SimonFisher
  • 380
  • 1
  • 4
  • 13
  • What does question have to do with the threading mode, when you're not using multiple threads? – CL. Oct 22 '13 at 07:15
  • CL, you're right, now it seems my question has nothing to do with threading mode. What I really want to know is: in the same thread, is it safe in all situation for multiple prepared-statements share one connection? Is there enough isolation between queries? And has this kind of isolation(if there any) any performance impact comparing with one connection per statement scenario? – SimonFisher Oct 22 '13 at 09:37

1 Answers1

3

In a single-threaded app, you can use either a single or multiple connections. In the latter case, it is possible to run multiple queries in parallel.

Please note that there is one transaction per connection. If you have multiple connections, one writer will block all readers.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • 1
    Two more quick questions: 1.Is it good practice to keep alive and share a global SQLite connection in a app(Windows store app)? I am not familiar with SQLite, but I know lots of other RDBMS encourage the "use and close" pattern. 2: If I keep a sqlite connection alive in the lifetime of an app, is it possible the connection is dropped or not usable in the midway due to abuse or inactive for too long time? Or should I do something to keep it alive(usable/valid) all the time? – SimonFisher Oct 22 '13 at 11:45
  • 1
    Keeping an open connection around will cost nothing but memory. The OS will suspend or terminate the entire app. – CL. Oct 22 '13 at 11:50
  • Does Connection itself occupy much resources? If not, then maybe I can fix the maximum memory my database can occupy by adjusting page cache size("PRAGMA cache_size = -kibibytes"). Am I right? – SimonFisher Oct 22 '13 at 12:14
  • 1
    Yes, other than the page cache, there isn't much. – CL. Oct 22 '13 at 13:13
  • How do I specify a connection pool? – chovy May 17 '22 at 02:45
  • @chovy The SQLite library itself does not have a connection pool. The database driver of your language or OS might have one. – CL. May 17 '22 at 06:47