VistaDB has had connection pooling since one of the early betas.  I was recently speaking with a user who had some questions and I could not remember the answers, so I looked through the code.  I found one property that was not exposed to the public interface, and I decided to take the time to document my finding while it was fresh in my mind.

Take a look in the help file (in Build 50) for a new HOWTO topic on Connection Pooling. Connection pooling enables your .NET application to recyle a connection a pool of connections that have been previously closed. Once a connection with a particular connection string has been created it is placed in a pool during the Close() operator and an application can then resuse that connection without the expense of rebuilding the entire object. When the application requests a connection an existing object is returned from the set of pooled connections. When the object is closed again it will be added back to the pool for reuse.

NOTE: Connection pooling only works when you use the same connection string each time. If any of the settings in the connection string are different a new connection will be opened. VistaDB uses your data source, password, min and max pool sizes to build the string used to determine if a pool is different.

This can have a huge impact on the performance of your application. When a new connection is built it must open the database, validate the headers of the file, check to make sure the version of the database is the same as the engine, etc. In other words a lot of housekeeping happens when you first build a connection.

Starting in Build 50 connection pooling may be enabled by setting the "Pooled=true" keypair in your connection string. The default minimum pool size is 5, and the maximum is 50. Connection pooling should not be used with EXCLUSIVE modes. This can lead to difficult to debug issues within your application.

Keep in mind the sequence of events for connection pool may be out of order if you do not put using statements in your code.
Look at the following example:

·   Connection1 opens exclusive to the database. And is then closed.
·   POOL1 is now assigned the connection.
·   Connection2 opens database using same connections settings and is given POOL1. POOL1 is now empty. Connection2 calls close in the finalizer for the class and is non-deterministic.
·   Connection3 opens database using same connections and fails because Connection2 is still open. In general connections should be treated either as a global that you lock and manage yourself, or opened with using statements and torn down as quickly as possible.

Testing the connection pool

There are many tests in the NUnit samples, but I wanted to write one to test this new property. I made a quick test and used this as my connection string:

VistaDBConnectionStringBuilder connstring = new VistaDBConnectionStringBuilder(@"data source='c:\vistadb3\data\Northwind.vdb3';Min Pool Size=10; Max Pool Size=100;Pooling=true");

I then took the Northwind database and performed some basic actions and added timers to measure the performance. The basic structure was a function that opened a connection, got some data, and then closed everything and got out.  This would be a good test for connection pooling because there is really only one open at a time, and this sort of open / close cycle is very expensive for file based databases like VistaDB. I ran 25 times to average out the GC /JIT time and I called each routine once before starting the timing.  The average load time is the time from when the routine started until it got back it’s connection.  Total runtime is the total time to complete the function. Here are the performance numbers:

25 Runs No Pooling

Average Load Time: 8 ms
Total Run Time:  31.2 ms

25 Runs with Connection Pooling

Average Load Time:  1.7 ms
Total Run Time: 15.6 ms

So you can see the startup time did have a significant impact on the function.  Connection pooling worked in this case, and performance of the application was about twice as fast.