FireDAC.IFDPhysCommand.Batch Sample

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 10.1 Berlin | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Phys Layer\IFDPhysCommand\Batch
• Subversion Repository:
  • Delphi: http://sourceforge.net/p/radstudiodemos/code/HEAD/tree/branches/RADStudio_Berlin/Object%20Pascal/Database/FireDAC/Samples/Phys%20Layer/IFDPhysCommand/Batch

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

• IFDPhysConnection
  • CreateCommand
• IFDPhysCommand
  • Prepare
  • Params
  • Execute
• TComboBox
• TCheckBox
• TLabeledEdit
• TMemo

See Also

• FireDAC
• Defining Connection (FireDAC)
• Working with Commands (FireDAC)
  • Executing Commands (FireDAC)
  • Command Batches (FireDAC)
  • Array DML (FireDAC)

Samples

• FireDAC TFDQuery Batch DML sample
• FireDAC TFDQuery Array DML sample