Category Archives: FireDAC

Database updates in RAD Studio XE8

Database updates in RAD Studio XE8

With RAD Studio XE8 there have been a number of database updates to expand connectivity, and improve and introduce functionality for us programmers.

FireDAC & IBX support for Change Views

FireDAC has introduced new support for Change Views – enabling rapid identification of what has changed on the server side data and updating of client side stored data. See this <2min video for how these work.

Source code for the example ships in the samples directory:

<Samples><language>Database\FireDAC\Samples\DBMS Specific\InterBase\ChangeView

IBX has also introduced new components to make it easier to work with change views and subscriptions.

 New Platform – Teradata

FireDAC now includes support for Teradata, using the driver name TData.

TeraData in FireDAC

 

An example of how to set the driver properties is shown on the docwiki help for connecting to Teradata with Delphi.

DriverID=TData
Server=192.168.43.140
Database=MyDatabase
User_Name=dbc
Password=dbc

TDataSet TField speed up.

Marco Cantu mentioned on the Launch webinar Q&A a customer was claiming seen a speed up of around 25% on a TClientDataSet application due to the refactoring at the base of how TField is used at the core layer. 🙂 This is really cool for both VCL and also FMX developers.

64bit support for iOS

With the new platform for iOS 64bit, the database layer is also seamlessly available, just recompile and run 🙂

Other updates

There are updates for EMS including push notification. EMS is a great way to access central data from mobile devices. EMS now is updated to include InterBase XE7 for both the server and ToGo side, allowing you to use Change Views on both Server and Clients.

Where FireDAC uses SQLite, it has been updated to a newer driver.

For more of whats new in RAD Studio XE8, including EMS updates, please click here Whats new in Delphi XE8 and C++ Builder XE8

 

Free FireDAC training online

FireDAC Training

Free FireDAC training online!

FireDAC is the Universal Data Access library for developing applications for multiple devices, connected to enterprise databases.

Starting tomorrow you can join the embarcadero team for free FireDAC training online in these special sessions, where you will be taken from a beginner to an expert by showing you how to utilise FireDAC in multiple situations.

Having used multiple database technologies over the years of developing with Delphi, I have been exceptionally impressed by the raw power of FireDAC and how fast and easy it makes the most difficult jobs as a database developer.

The sessions run every Thursday from Jan 22-Mar 26 in 3 time zones so no excuse for missing out!

Register now for free

Date Topic
January 22 Introduction to FireDAC
January 29 Monitoring and Tracing
February 5 Cached Updates and Auto-inc Fields
February 12 Array DML
February 19 Cascading Options System
February 26 Preprocessing
March 5 ETL
March 12 In-Memory Dataset
March 19 Local SQL
March 26 BDE to InterBase Migration


Register now for free

Free XE7 Trial

If your not a XE7 customer you can get the most out of these session by downloading a Delphi XE7 free trial here.

Speeding up SQL processing with Array DML

If you want to really make your application scream, then one key area to get your interaction right is with the database layer. The example below becomes 8 (eight) times faster processing 10,000 records!

A number of high end databases (including Oracle and InterBase) include support for array inserts which, in short, is a much faster way to insert data. Rather than having to process record after record, the process accepts a block of changes that are processed in one hit. To be clear, this is not the same as starting and ending a database transaction, (something else that can also really help with speed).

Array DML in FireDAC helps you take advantage of database engines that support this feature easily. To see the difference in code and speed, lets explore a simple example. The example uses the following table called test containing two fields, Integer and string

CREATE TABLE test(Field1 INTEGER, field2 VARCHAR(20))

The typical way to insert 10000 records is to use a loop, where we have a query that just updates the parameters and executes the SQL.

const
  NUM_INSERTS = 10000;

procedure TForm7.Button1Click(Sender: TObject);
var i: integer; t: integer;
begin
  FDQuery1.ExecSQL('DELETE FROM test');
  FDQuery1.SQL.Text := 'INSERT INTO test(field1, field2) values (:field1, :field2)';
  t := GetTickCount;
  FDQuery1.Connection.StartTransaction;
  try
    for i := 0 to NUM_INSERTS-1 do
    begin
      FDQuery1.Params[0].AsInteger := i;
      FDQuery1.Params[1].AsString := 'Str' + IntToStr(i);
      FDQuery1.ExecSQL;
    end;
  finally
    FDQuery1.Connection.Commit;
  end;
  (Sender as TButton).Caption :=  FloatToStr((GetTickCount() - t) / 1000);
end;

To alter this code to use Array DML, there is very little to do. Firstly, before setting the parameter values you can set the size of the array you want to insert. (You can always set this higher that needed if your not sure)

Once the array size is set, there is a small modification to the way the parameter values are set. FireDAC supports the plural setters for setting each record in the dynamic array. (see code below)

Finally, there execute command is changed to tell it to run for the total records you want to insert.

procedure TForm7.Button2Click(Sender: TObject);
var i: integer; t: integer;
begin
  FDQuery1.ExecSQL('DELETE FROM test');
  FDQuery1.SQL.Text := 'INSERT INTO test(field1, field2) values (:field1, :field2)';
  t := GetTickCount;
  FDQuery1.Connection.StartTransaction;
  try
    FDQuery1.Params.ArraySize := NUM_INSERTS;
    for i := 0 to NUM_INSERTS-1 do
    begin
      FDQuery1.Params[0].AsIntegers[i] := i;
      FDQuery1.Params[1].AsStrings[i] := 'Str' + IntToStr(i);
    end;
    FDQuery1.Execute(NUM_INSERTS,0);
  finally
    FDQuery1.Connection.Commit;
  end;
  (Sender as TButton).Caption :=  FloatToStr((GetTickCount() - t) / 1000);
end;

So what is the difference at run time? On my PC (virtual machine), running the 10,000 inserts takes 1.014 seconds in the first block of code, compared with 0.125 seconds with the second block of code. i.e. only 12% of the original processing time and over 8 times faster!

Thats about it! The code for the above Array DML FireDAC demo is available at code central http://cc.embarcadero.com/item/29832

An video example of this in action, along with a number of other FireDAC capabilities is also available in the FireDAC Session from Developer Direct Season 4 video.