RAD Studio 10.4.1 is now available! Learn more. Looking for discounts? Visit our Special Offers page!
CodeDatabaseDelphiRAD Studio

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 DelphiContains
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:

  • memo object.
  • Two edit objects named DML ArraySize and No of Records. The default value of both objects is 100 and 100000, respectively.
  • check box object labeled as Use Transactions. It is unchecked by default.
  • Three buttons objects labeled as Run Single TestRun 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


Reduce development time and get to market faster with RAD Studio, Delphi, or C++Builder.
Design. Code. Compile. Deploy.
Start Free Trial   Learn More About Upgrading

Related posts
C++CodeDelphi

Learn To Use Python Objects Inside Your Delphi Source Code With This Windows Sample App

C++CodeDelphi

Manage Your Collection Of Pages Easily With TCardPanel Component For Your Delphi VCL Application

CodeDelphiRAD Studio

Learn How To Use FireDAC In The Dynamic-Link Libraries With DLL Sharing Sample In Delphi

C++CodeDelphi

Learn To Build A Python GUI For Working with HTTP Requests With Requests Library In A Delphi Windows App

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

IN THE ARTICLES