Easily Optimize Multiple SQL INSERT Commands In A Single Step With Array DML In Delphi

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.

Location

You can find the Batch sample project at:

• Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
  • Object PascalDatabaseFireDACSamplesPhys LayerIFDPhysCommandBatch
• 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_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:UsersPublicDocumentsEmbarcaderoStudioFireDACFDConnectionDefs.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 executionThe sample calls 10000 times the Execute method to execute the 10000 SQL INSERT statement.
  2. Batch executionThe 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.

For more information and more external links for other samples, please go to the next link:

http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.IFDPhysCommand.Batch_Sample

Head over and check out the full source code for the Array DML bulk insert sample for Delphi.