FireDAC.IFDPhysConnection.Pooling Sample

This sample describes how to use FireDAC in a multithreaded environment. 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 Rio | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Phys Layer\IFDPhysConnection\Pooling
• Subversion Repository:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

Description

The Pooling sample shows you how to use the IFDPhysConnection interface together with the TThread class in order to establish connections to a database in a multithreaded application. To this end, the sample launches 10 threads. Each thread connects to the database and performs 50 SQL queries. In multithreaded applications, each thread starts and establishes a connection, performs a certain short task and then releases the connection. The repetitive connection establishments in multithreaded aplications may lead to performance degradation across the whole system. To avoid this, the application allows you to make use of the connection pooling. To show the benefits of the connection pooling, the sample compares and shows the time used to run the application with or without enabling the connection pooling.

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:\Users\Public\Documents\Embarcadero\Studio\FireDAC\FDConnectionDefs.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...'.

Uses

• IFDPhysManager
  • CreateConnection
  • Pooled
• IFDPhysConnection
  • CreateCommand
  • Open
  • Close
• IFDPhysCommand
  • Prepare
• TThread
• TComboBox
• TCheckBox
• TMemo
• TButton

See Also

• FireDAC
• Defining Connection (FireDAC)
• Establishing Connection (FireDAC)
• Multithreading (FireDAC).

Samples

• FireDAC Connection Definitions sample
• FireDAC TFDConnection Pooling sample