![firedacimagenotitle02 | Embarcadero RAD Studio Delphi C++Builder Blogs](https://i0.wp.com/blogs.embarcadero.com/wp-content/uploads/2020/06/firedac-image-no-title02.jpg?resize=1140%2C680&ssl=1)
This sample demonstrates how to use Array DML in FireDAC and compares its performance for different array sizes.
Table of Contents
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
- Navigate to the location given above and open
FireDACArrayDML.dproj
. - 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
and100000
, 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 length100
. This mean that for each DML array, the test executes100
SQLINSERT
commands in a single step in order to optimize database population performance. Moreover, as it is done for100000
records, the sample uses1000
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.
- The StartTransaction method to start the database transaction before executing the
- 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
and10000
. - 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}'
.
![arraydml | Embarcadero RAD Studio Delphi C++Builder Blogs](https://i0.wp.com/blogs.embarcadero.com/wp-content/uploads/2020/10/arraydml.png?resize=750%2C551&ssl=1)
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
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today
Free Delphi Community Edition Free C++Builder Community Edition