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.
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today
Free Delphi Community Edition Free C++Builder Community Edition
Hello, we can’t see the video. “This video is private
Post message timed out.”
(with cookies accepted)
Yes sorry about that. The video is hosted on a service we tried using for a while and it looks like it has become unavailable. I’ve asked Fernado, the author, to see if we can replace it with a new version of the video.
Thanks Ian for the heads up!
The video URL was updated, thank you!
This is a great article for Delphi programmers, and something that I desperately need for my project. But for us C++ Builder people it does not seem to work. Since there is no analog for Initialize in C++ except the ctor, I used the ctor to call the connection setup code using the FDManager singleton. No go. One thing to note, is that the Rad Server Engine allocates, uses, then destroys Resources as they’re called. But since the FDManager is managed by the RTL and the RS Engine this should not matter. I would welcome a discussion on this topic, please.
Ok I don’t have any specific answer on this myself. I will put this to our Learn C++ Community manager and see if he is able to answer for you.
Hi Ian. I have an answer. What was happening was an RTTI error was being generated and logged and therefore causing Rad Server to bomb. Working over a few days with support, they discovered that the “BeforeConnect” event is declared as __public in DataModule. So in our overridden DataModules we need to change the header code from the default “#pragma explicit_rtti methods (public)” to “#pragma explicit_rtti methods (public, __published)”. Further for C++ there needs to be a static method with a #pragma startup XX set above 60 for the SetupFDManger() method. Then we’re able to move ahead as stated above, of course with a port to C++. I’d show an example here but I’m not sure how to put code or an image in these reply’s?
Correction to my post. BeforeConnect is declared __published in the FDManager, not the DataModule, my apologies. Therefore the need to add #pragma explicit_rtti methods (public, __published) in our overridden DataModules. Any can feel free to email me directly for more information. This my public email: [email protected]
In your setup of FDManager, should you free oParams after the call to AddConnectionDef or does FDManager end up owning it? I looked at the FireDAC.Comp.Client and it looks like it makes a copy so it feels like you should free it.
Yes, this makes sense. Thank you!