FireDAC.IFDPhysCommand.Batch Sample

From RAD Studio Code Examples
Jump to: navigation, search

This sample demonstrates the use of the IFDPhysCommand interface to implement the Array DML execution technique in FireDAC.

Location

You can find the Batch sample project at:

  • Start | Programs | Embarcadero RAD Studio Athens | Samples and then navigate to:
    • Object Pascal\Database\FireDAC\Samples\Phys Layer\IFDPhysCommand\Batch
  • 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 Batch sample shows you how to use the Array DML execution technique to execute multiple SQL INSERT commands in a single step to optimize database population performance. To this end, the sample uses the IFDPhysCommand interface to set an array of values for each parameter and the Execute method in Array DML mode to populate the database in a single step.

Note: In this demo the "batch" and "Array DML" terms are interchangeable.

How to Use the Sample

  1. Navigate to the location given above and open IFDPhys_Batch.dproj.
  2. Press F9 or choose Run > Run.

Files

File in Delphi Contains

IFDPhys_Batch.dproj
IFDPhys_Batch.dpr

The project itself.

fBatch.pas
fBatch.fmx

The main form.

Implementation

When you run the application, you see the following components on the form:

  • A TCheckBox object labeled as Insert blobs. It is unchecked by default.
    This sample executes multiple SQL INSERT commands. First, the sample executes the SQL INSERT commands using an ordinary execution mode. Then, the sample executes the SQL statements using an Array DML execution mode. The state of the Insert blobs check box changes the SQL statement:
    • If Insert blobs is unchecked, the SQL statement is set to 'insert into {id FDQA_Batch_test}(tint, tstring) values(:f1, :f2)'.
    • If Insert blobs is checked, the SQL statement is set to 'insert into {id FDQA_Batch_test}(tint, tstring, tblob) values(:f1, :f2, :f3)'.
  • A TLabeledEdit object named Records to insert.
    This label represents the number of SQL INSERT commands that are executed in a single step. The preset value is 10000.
  • A TComboBox object labeled as Use Connection Definition.
    When you click the Use Connection Definition combo box, the menu shows all the persistent connections defined on the file C:\Users\Public\Documents\Embarcadero\Studio\FireDAC\FDConnectionDefs.ini. Select an option in order to define a connection to a database. When the connection is defined, the sample uses the CreateCommand method of IFDPhysConnection to create a command interface. Then, the sample uses this command interface to execute multiple SQL INSERT commands in run time. The sample implements the execution of the multiple SQL INSERT commands using two different execution modes: the ordinary execution and the batch execution.
    1. Ordinary execution
      The sample calls 10000 times the Execute method to execute the 10000 SQL INSERT statement.
    2. Batch execution
      The sample calls the Execute method in Array DML mode to execute the 10000 SQL INSERT statement in a single step. The Execute method is called only once as follows: Execute(iBatchSize, 0), where iBatchSize is the length of the DML array. In this sample, iBatchSize is set with the value of the Records to insert label.
  • A TMemo object.
    The sample uses this object to report the execution time of both execution methods.

Uses

See Also

Samples