SQLite Forum

Connection Pool always empty?
Login

Connection Pool always empty?

(1) By Florian (Flohack) on 2021-06-21 14:56:33 [link] [source]

Hi, I am setting "Pooling=True;Max Pool Size=20" on a connection object. Connections are created for each transaction, execute 1 or more commands and then are being closed/disposed.

Now, query PoolCount after creating a new connection never shows anything other than zero. I would expect to at least see 1 there.

Is it that I just query in the wrong moment?

Thanks BR!

(2) By Gunter Hick (gunter_hick) on 2021-06-21 15:08:01 in reply to 1 [link] [source]

Obviously you are using a wrapper and some kind of object oriented language to call the SQLite library. Maybe you could provide a little more information on what exactly you are using.

(3) By Florian (Flohack) on 2021-06-24 13:30:51 in reply to 2 [source]

Hi,

thanks for the answer. We are using .NET and so the called to the managed lib is basically:

string connectionString= $"Data Source={fileName};Cache Size=-80000;Page Size=4096;Journal Mode={SQLiteJournalModeEnum.Truncate};Pooling=True;Max Pool Size=20";
new SQLiteConnection(connectionString, true);

We then open this connection, attach a transaction and some commands, execute and commit and finally close the connection again. Do we need to keep a reference to the closed connection so its not being disposed?

Thanks BR Florian

(4.1) By Warren Young (wyoung) on 2021-06-24 17:42:26 edited from 4.0 in reply to 3 [link] [source]

Those pooling parameters are not documented for Microsoft.Data.Sqlite. You can't just copy a connection string meant for one DBMS engine or one particular ADO.NET provider and expect it to apply 100% to another.

EDIT: …Or do you mean System.Data.SQLite? (You can't just say ".NET" and expect us to know which library you're using!) I managed to get the CHM help for System.Data.SQLite converted to something useful on this non-Windows system, and I see that it does document these parameters, but all I can tell from the docs is that this library has a connection pool, not what you're supposed to do to make it do anything useful. I suppose you'll have to read the source code to work that out.

(5) By Florian (Flohack) on 2021-06-25 07:54:14 in reply to 4.1 [link] [source]

Oh dang, it might be that I mixed connection string parameters. I am indeed using System.Data.SQLite. I tried it this time with the builder:

            var connStringBuilder = new SQLiteConnectionStringBuilder() {
                    PageSize = PageSize,
                    DataSource = databasePathAndName,
                    JournalMode = SQLiteJournalModeEnum.Truncate,
                    Pooling = true
            };

This results in the following connection string: page size=4096;data source=xyz.db;journal mode=Truncate;pooling=True;cache size=-80000

So we can see that the parameter is called Pooling. Still, on a newly created connection the property PoolCount is zero. Strange...

I really would appreciate to know why this functionality exists and why its so hard to get it activated :)

(6) By Florian (Flohack) on 2021-06-25 10:33:27 in reply to 1 [link] [source]

Alright so it seems to work, but you need to know how:

  • As mentioned here you need to use Default mode for opening the file. Read-Only or fail if missing would deny a pool entry.
  • The pool entries are purged when the last connection is closed. Not quite what I expected. In my assumption the pool would be alive per process, so that even when dropping to 0 open conenctions I have still a few in the pool. THats not the case.

So in order to achieve what I need I have to open a dummy connection and keep it open. Okay, not very beautiful, but it works.

BR Florian