FireDAC.TFDQuery.ArrayDML Sample
This sample demonstrates how to use Array DML in FireDAC and compares its performance for different aray sizes.
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.
Description
The ArrayDML sample shows you how to use Array DML to execute multiple SQL INSERT commands in a single step to optimize database population performance. The sample also compares the Array DML performance for different array sizes.
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 |
---|---|
|
The project itself. |
|
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
- The connection is set to an Oracle demo database.
- 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}'
.
Uses
See Also
Samples
- FireDAC TFDQuery Async sample
- FireDAC TFDQuery Batch sample
- FireDAC TFDQuery Batch Error Handling sample
- FireDAC IFDPhysCommand Batch sample