FireDAC.TFDQuery.ArrayDML Sample

From RAD Studio Code Examples
Jump to: navigation, search

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

Location

You can find the ArrayDML sample project at:

  • Start | Programs | Embarcadero RAD Studio Athens | 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

  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}'.

Uses

See Also

Samples