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 [link] [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 [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