Add AnyDAC. Very High Performance using the Array DML.


This article is permanently located in AnyDAC Online Documentation. There you can find many other usefull AnyDAC articles and references.
Introduction
This tutorial has three main sections:


How to prepare your test environment.

The main elements of the Array DML commands.

The typical results of the Array DML test run.
AnyDAC encapsulates all database server specific implementation of the Array DML commands and lets you use identical code for all server types. Obviously, the resulting performance will differ based on the server implementation; especially Oracle, MS SQL Server and IBM DB2 have very powerful support of the Array DML and the resulting performance increase is just amazing. Please use the sample code to get a feeling for the potential performance increase within your application and network.
Prepare your Test Environment
The following example works with the AnyDAC sample database environment. For further details about the installation of this database look into AnyDAC Demo Databases. You find the demo projects in your sample directory:


This tutorial code - \Samples\Comp Layer\TADQuery\ExecSQL\AD03-ArrayDML

A basic example code - \Samples\Comp Layer\TADQuery\ExecSQL\Batch.
How does the Array DML command work?
Imagine a "use case" where you have to INSERT, UPDATE, DELETE or run any other parametrized command N times, typically one command per single record. This means, that each set of input parameters requests to execute a SQL command and is transferred separately between the client and the server. This leads to a heavy load on the network, client and server. Array DML allows you to transport not only one, but N-sets of data within one transfer. Have a look at the following example: ADQuery1.SQL.Text:= 'insert into ADQA_Batch_test (tint, tstring) values(:f1, :f2)';
You can speed up your code dramatically by using Array DML commands. Such commands transfer not only one, but N sets of parameters. ADQuery1.Params.ArraySize := 100; ... for i := 0 to ADQuery1.Params.ArraySize do begin   ADQuery1.Params[0].AsIntegers := i;   ADQuery1.Params[1].AsStrings := 'Test' + IntToStr(i); end; ADQuery1.Execute(ADQuery1.Params.ArraySize);
This means the Params property of the query is no more a one- but a two-dimensional array, that allows you to store N sets of parameter values before sending them to the server.
Usage Hints



Can be used for any SQL command that uses parameters (INSERT, UPDATE, DELETE ...).

The error handling is supported on record level and described in a separate article.

AnyDAC unifies the Array DML for different server types (no need for you do dig into the API).
Typical Results of the Array DML Test Run
The attached test code allows you to experiment within your specific environment.
 
Results of the test example can differ a lot depending on host and network performance. A typical picture of a local Oracle on a rather old laptop will still show > 100'000 records per second as you can see in this screen shot:

A larger Array DML ArraySize results in a higher performance (in our case up to a factor of 2000). We expect that the performance boost in your own environment will surprise you as well.
Performance Hints
Array DML command performance is influenced by:


The fact that they are a lot faster on slow networks as these commands create less TCP/IP packages.

They reduce the CPU load on the client side, as most of the time the server has to work on the array command.

The theoretical speed of > 100'000 rec/sec is not often reached as the server normally has to evaluate triggers and indexes.

For real large batch inserts (e.g. > 1'000'000 records), you should consider to drop and recreate non primary key indexes to reach a maximum performance.
Related Discussions
  • HOW TO GREP CHARACTERS IN DELPHI 3 (2001-01-03 03:29:32)
    Var Grep : String; Begin Grep := Copy('2000-46',6,2); End; The first parameter is a string or a dynamic array, the second is the index of...
  • TOO STUPID TO WORK OUT HOW TO USE GETKEYBOARDSTATE (2001-01-07 00:32:24)
    GetKeyboardState just gets the information of the input devices. For example: var KS: TKeyboardstate; GetKeyBoardState(KS); //0=OFF...
  • WAYS TO SHRINK DELPHI EXECUTABLE SIZE (2001-01-08 19:23:49)
    You have a couple of options: 1) Get an EXE shrinker - basically compressing the file and then decompress as it runs. See:...
  • REGISTRY BINARY DATA (2001-01-10 01:42:13)
    I took this from a newsgroup and was originally posted by Ralph from Team B. uses Registry; procedure...
  • EXAMPLE OF STRINGTOWIDECHAR (2001-01-10 16:26:22)
    var WideCharBuf: array of WideChar; begin StringToWideChar(StringToConvert, WideCharBuf, SizeOf(WideCharBuf) div 2); end;
  • PASSING FUNCTIONS AS PARAMETERS (2001-01-11 13:29:41)
    This code was posted by Glynn Owen on the newsgroup. Newsgroup: borland.public.delphi.objectpascal unit Unit1; interface uses...
  • FILE SENDING VIA TCP/IP SOCKET (2001-01-15 05:05:47)
    If you don't want to use a buffer, you can use TSocket.SendText. It works very well. If you want a sample program, mail-me (prolp@ifrance.com)
  • HOW DO I GET THE IP ADDRESS OF A COMPUTER? (2001-01-15 21:33:16)
    unit Unit1; interface uses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, Winsock; type...
  • HOW CAN I GET THE NT-USERNAME WITH DELPHI? (2001-01-19 16:29:29)
    Expanding on the first questions : How do we get the full user name. Ex 'Mark Silvia' rather than just 'marks' Mark
  • CHECKING FOR DEPHI IDE / DEBUGGER (2001-01-17 07:38:26)
    Yes, right, it should work with strings as well. Sorry, I used this example a long time ago, as I hadn't a lot of expirience with delphi.
Latest News
Submit News Form Past News
Latest Forum Entries