AnyDAC: Data Access Performance | RemObjects Software

Data Access Performance

Applications of all types will benefit from multiple AnyDAC high performance features, such as

Rowset Fetching
Array DML Command Execution
Command Batches
Metadata Caching
Asynchronous Command Execution
Update Commands Caching
Connection Pooling

and more.

Also, AnyDAC code is deeply optimized. Our software engineers spend considerable time making AnyDAC one of the fastest data access engines on the market.

Rowset Fetching

Rowset Fetching allows you to specify the number of records that will be fetched from the server in one network round trip. You can optimize this separately for each select statement that you execute, thereby minimizing the number of network round trips. For example, Zeos DBO does not support Rowset Fetching, so AnyDAC is a few times faster than Zeos DBO for fetching.

Array DML Command Execution

Array DML Command Execution provides the possibility to insert, update, delete many records or call a stored procedure many times via a single Execute call. This minimizes network traffic and server workload, and is primarily useful in batch applications. To illustrate, let's consider an example: we will insert 1000 of records into DB. Here is the classic approach:

var
  i: Integer;
begin
  ADQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2)';
  for i := 1 to 1000 do begin
    ADQuery1.Params[0].AsInteger := i;
    ADQuery1.Params[1].AsString := 'Str' + IntToStr(i);
    ADQuery1.ExecSQL;
  end;
end;

And here is the Array DML equivalent:

var
  i: Integer;
begin
  ADQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2)';
  ADQuery1.Params.ArraySize := 1000;
  for i := 1 to 1000 do begin
    ADQuery1.Params[0].AsIntegers[i - 1] := i;
    ADQuery1.Params[1].AsStrings[i - 1] := 'Str' + IntToStr(i);
  end;
  ADQuery1.Execute(1000, 0);
end;

Depending on the DBMS, this will provide an enormous performance gain, even though the coding is almost similar to the classic approach.

Command Batches

A Command Batch is a group of SQL statements sent at one time from an application to the DBMS for execution. DBMS compiles the statements of a batch into a single execution plan. The statements in the execution plan are then executed one at a time. This minimizes network traffic and server workload. After execution, the DBMS returns to the client result sets produced by the commands in a batch. AnyDAC allows you to process all result sets one by one. For example:

ADQuery1.SQL.Add('select * from [Orders]');
ADQuery1.SQL.Add('select * from [Order Details]');
ADQuery1.Open; // Orders table rows are accessable here
ADQuery1.NextRecordSet; // Order Details table rows available here

Unlike some libraries, AnyDAC will automatically skip empty result sets that do not have columns and rows.

Metadata Caching

To perform some tasks, like data editing, stored procedure execution, AnyDAC need to fetch metadata from the DBMS, which can be a time consuming operation. AnyDAC has a built in metadata cache, which will return data previously fetched from the DBMS metadata. This minimizes network traffic and server workload. Metadata Caching is transparent for the application programmer. A future AnyDAC version will be able to cache metadata across multiple application runs, by storing metadata in an external file on the client.

Asynchronous Command Execution

In addition to being ready for use in multi-threaded applications, AnyDAC makes it easy to for the developer to not have to worry about multi-threading in the first place by allowing you to control the timing aspects of the data access operation execution. This includes Execute, Open and Fetch operations.

A programmer can specify the timeout (ResourceOptions.AsyncCmdTimeout) for data access operation execution and choose between four modes (AsyncCmdMode). In the Cancel Dialog mode, for example, a user will be notified about long running operations.

Update Commands Caching

Update Commands Caching allows AnyDAC to reuse data updating commands, which are sent to the DBMS as result of Edit / Append / Delete / Post dataset method calls. This reduces the workstation workload and speeds up batch data editing algorithms. Update Commands Caching is transparent for the application programmer.

Code Optimizations

AnyDAC code has passed very deep optimization under AQ Time profiler. To prove that AnyDAC is one of the fastest data access engine on the market, we have created the ADSpeed application - AnyDAC benchmark suite (see AnyDAC\Tool\Speed). For the tests, one table is filled with 200 records, with each having a 150Kb BLOB field. A second table contains 200,000 records, each with string, number and datetime fields.