Optimize Database Population Performance With Array DML In Delphi

This sample demonstrates how to use Array DML in FireDAC and compares its performance for different array sizes.

Location

You can find the ArrayDML sample project at:

• Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Comp Layer\TFDQuery\ExecSQL\ArrayDML
• 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 FireDACArrayDML.dproj.
2. Press F9 or choose Run > Run.

Files

File in Delphi	Contains
FireDACArrayDML.dproj FireDACArrayDML.dpr	The project itself.
fMain.pas fMain.fmx	The main form.

Implementation

Instead of configuring the main components of this sample at design time using the Object Inspector, this sample configures the setup of the TFDConnection and the TFDQuery in run time. When you run the application, you see the following components on the form:

• A memo object.
• Two edit objects named DML ArraySize and No of Records. The default value of both objects is 100 and 100000, respectively.
• A check box object labeled as Use Transactions. It is unchecked by default.
• Three buttons objects labeled as Run Single Test, Run Array Size Test, and Delete all Rows.

Once the sample is running, you can interact with the sample by changing the edit boxes or clicking on the aforementioned buttons:

• Click on the Run Single Test button to run an Array DML test code using the Execute method in Array DML mode. To this end, the sample takes the length of the DML array from the value of the DML ArraySize edit object. Using the default values of the edit boxes, the sample runs the test for 100000 records using chunks, where each chunk is a DML array of length 100. This mean that for each DML array, the test executes 100 SQL INSERT commands in a single step in order to optimize database population performance. Moreover, as it is done for 100000 records, the sample uses 1000 DML arrays. To implement the test, the sample configures the following:
  • The connection is set to an Oracle demo database.Note: Change the connection parameters according to your environment. For further details, see Database Connectivity (FireDAC).
  • The INSERT command is the following: 'insert into {id FDQA_Batch_test}(tint, tstring) values(:f1, :f2)'.
  • If you check the Use Transactions check box, the sample uses:
    • The StartTransaction method to start the database transaction before executing the INSERT command.
    • The Commit method to permanently store the modifications made by the INSERT command execution.
• Click on the Run Array Size Test button to launch a loop that executes nine times the previous test. Each execution uses a different DML array length. The lengths are the following: 1, 2, 5, 10, 50,100, 500, 1000, 5000 and 10000.
• Click on the Delete all Rows button to delete all the records from the test table. To this end, the sample uses the ExecSQL method with the following DELETE command: 'delete from {id FDQA_Batch_test}'.

For more detailed information and external links to other articles, please follow the link below:

http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.TFDQuery.ArrayDML_Sample