Connection Pool always empty?
(1) By Florian (Flohack) on 2021-06-21 14:56:33 [link]
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]
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
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]
Those pooling parameters [are not documented for Microsoft.Data.Sqlite][1]. 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][2] to work that out. [1]: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/connection-strings [2]: https://system.data.sqlite.org/index.html/dir
(5) By Florian (Flohack) on 2021-06-25 07:54:14 in reply to 4.1 [link]
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]
Alright so it seems to work, but you need to know how: - As mentioned [here](https://system.data.sqlite.org/index.html/tktview/69cfdf79fa11f506602146b0fd192c9a546ad167) 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