Table of Contents
Connection Pooling Meaning
A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established.
You can also define a maximum number of connections that a pool will create, and this can be very interesting to reduce the number of database licenses that are needed. In a case like that, when the pool reaches the limit and a new request arrives, this request will not be processed if a vacant connection isn’t made available before a certain time out previously defined.
The key for a connection pooling with limited database connections is to define the ideal number for this pool based on the final number of users and the application architecture. More about this in the sample below.
The FireDAC Connection Pooling Mechanism
The connection pooling mechanism from FireDAC is pretty easy to use and can be activated by setting just one additional connection property from your connection (Pooled=True).
Of course, the pooling feature shines in multi threaded applications, where multiple short tasks are executed simultaneously (or almost), and each one of these tasks requires establishing a connection. When using the polling feature, the connection will be already established and waiting for the task, resulting in a much faster processing time and less resources consuming.
For advanced scenarios, besides the “Pooled” parameter, there are other three parameters that can be considered:
| Parameter | Parameter | Example |
|---|---|---|
| POOL_CleanupTimeout | The time (msecs) until FireDAC removes the connections that have not been used for longer than the POOL_ExpireTimeout time. The default value is 30000 msecs (30 secs). | 3600000 |
| POOL_ExpireTimeout | The time (msecs) after which the inactive connection may be deleted from the pool and destroyed. The default value is 90000 msecs (90 secs). | 600000 |
| POOL_MaximumItems | The maximum number of connections in the pool. When the application requires more connections, then an exception is raised. The default value is 50. | 100 |
FireDAC allows you to use “Persistent” connections (stored in the FireDAC’s .ini file), “Private” connections (available in memory for one application) and “Temporary” (not stored and not named nor managed by the FDManager). You can read more about how to define and establish a connection (with or without a pool) following the documentation below:
- https://docwiki.embarcadero.com/RADStudio/en/Multithreading_(FireDAC)
- https://docwiki.embarcadero.com/RADStudio/en/Defining_Connection_(FireDAC)
Connection Pooling with RAD Server (EMS)
For any serious backend application, RAD Server included, it’s almost mandatory having some pooling mechanism as the number of calls to your application grows. Our sample will demonstrate that using a “Private” connection defined through the FDManager. Of course, you can reuse connections already defined in the FireDAC’s .ini file as well, or even load the .ini file via the FDManager and amend it by adding the connection pooling parameters that are specific to your server application, but not so useful in a desktop app.
Our demo app was created using the RAD Server Wizard (https://docwiki.embarcadero.com/RADStudio/en/Creating_a_RAD_Server_Package) and making usage of the recently added EMSDataSetResource (https://docwiki.embarcadero.com/RADStudio/en/Using_RAD_Server_Components and https://blogs.embarcadero.com/using-emsdatasetresource-component-with-rad-server/), but the same applies to explicit defined RAD Server endpoints.
Important to notice that only one FDManager instance can exist per application instance, so you’ll notice the FDManager being created under the initialization section of our EMS resource, like this:
Running a stress test with our demo project
The video below shows the pooling mechanism being tested using JMeter with a total load of 100 users:
As an additional tip, if your RAD Server application is composed by several packages you can still using the pooling mechanism. All that you need to do is to create a RAD Server resource that is aimed to only define the connection pooling configuration by the FDManager, and make sure you load this resource as the first one in your deployment environment.