Learn How To Use Connection Pooling With A Multi-threaded Environment In Delphi

This sample implements a multithreaded application, where each thread uses the IFDPhysConnection interface to establish a connection. The multiple connection establishments may lead to performance degradation across the whole system. To avoid this, you can enable the Pooled property to use the connection pooling.

Location

You can find the Pooling sample project at:

• Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
  • Object PascalDatabaseFireDACSamplesPhys LayerIFDPhysConnectionPooling
• Subversion Repository:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

How to Use the Sample

1. Navigate to the location given above and open IFDPhys_Pooling.dproj.
2. Press F9 or choose Run > Run.
3. Interact with the sample:
   1. Select an option from the Use Connection Definition combo box.
   2. Click the Run button and see the execution time.
   3. Select the Run Pooled check box, click the Run button and see the execution time.
   4. Compare both execution times.

Files

File in Delphi	Contains
IFDPhys_Pooling.dproj IFDPhys_Pooling.dpr	The project itself.
fPooling.pas fPooling.fmx	The main form.

Implementation

When you run the application, you can interact with the sample using the following objects:

• A TComboBox object labeled as Use Connection Definition.Click the Use Connection Definition combo box and select an option in order to define a connection to a database. The menu shows all the persistent connections defined on the file C:UsersPublicDocumentsEmbarcaderoStudioFireDACFDConnectionDefs.ini. Once you select a connection definition, the sample enables the Run button and the Run Pooled check box.
• A TButton object labeled as Run.If you click the Run button, the sample launches 10 threads. Each thread uses the CreateConnection method of IFDPhysManager to create a connection to the database. Moreover, each thread uses the CreateCommand method of IFDPhysConnection to create a command for each connection. Finally, each thread uses the Prepare method of IFDPhysCommand to execute 50 SQL queries. The executed SQL query is the following SELECT command: 'select count(*) from {id Region}'. Therefore, in this case, each thread creates and uses a dedicated connection object working with the database.
• A TCheckBox object labeled as Run Pooled.If you select this check box, the Pooled property of the connection setting is set to True. The database connection pooling is a method used to keep database connections open so they can be reused by others. Therefore, if you select the option, the threads can reuse the current opened connection.Note: The connection pooling can be enabled only for a persistent or private connection definition.
• A TMemo object.The sample uses the memo object to display the type of connection. If you select the Pooled property, the memo displays the following message: 'Run pooled...'. On the other hand, if you uncheck the Pooled property, the memo displays the following message: 'Run non pooled...'.

Through the link below you can visit the original post about this sample:

http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.IFDPhysConnection.Pooling_Sample