Category Archives: InterBase

Articles about the InterBase database

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.

Looking to rise to the data security challenge?

Join me on Wednesday 28th May as we rise to the database security challenge on Windows, Android, iOS, Mac OSX, Linux and Solaris.

Data security is a hot topic right now, and one that is causing a lot of headaches. When it comes to securing your data and applications, small changes can make a massive difference. During the webinar there will be a number of tips relevant to whatever platform or database you develop with. We will also look at how to address a number of these challenges easily by exploring secure database storage with InterBase.

To find out more on this topic, or even if you can’t make it and want to catch the replay later, register now and “Rise to the Data Security Challenge”.

Wednesday, May 28, 2014

  • 6AM San Francisco / 9AM New York / 2PM London / 3PM Milan
  • 11AM San Francisco / 2PM New York / 7PM London / 8PM Milan
  • 5PM San Francisco / 29-May 9AM Tokyo / 29-May 10AM Sydney

Mobilizing Enterprise data – Free White Paper.

If you want to get a head start before the webinar, then it may also be worth downloading this free white paper

OpenSSL and InterBase = all is OK!

I am sure many of you have seen in the news this week the well publicised vulnerability in OpenSSL that has been named Heartbleed. https://www.openssl.org/news/secadv_20140407.txt and http://heartbleed.com/

InterBase is not affected by this issue. InterBase encryption uses OpenSSL versions that are NOT affected by this vulnerability.

For more information on InterBase please visit http://www.embarcadero.com/products/InterBase

InterBase XE3 update 4 released

We are proud to announce InterBase XE3 Update 4 is now available for download for registered users: http://cc.embarcadero.com/reg/interbase

New platform on XE3!

Along with the regular speed improvements, bug fixes etc, this release also brings the InterBase Linux engine up to XE3 from the previous InterBase XE edition.

Full details of what is contained in the update are available in the readme. http://docs.embarcadero.com/products/interbase/IBXE3Update4/Readme.html

InterBase XE3 versions

If you are developing business applications, InterBase gives a real edge around data security and best practice in data protection across all supported platforms, including mobile.

InterBase XE3 Update 4 – editions includes

Server Edition Desktop Edition ToGo and IBLite (embeddable)
  • Windows (32bit & 64bit)
  • Linux
  • Windows
  • Windows (32bit & 64bit)
  • Mac OS X
  • iOS Simulator and Device
  • Android

There is an Update/Patch installer for existing installations of InterBase XE3 Server/Desktop/Developer Edition on Windows. Trial editions will be updated soon.

Embedding InterBase

The quick guide for ToGo has also been updated and is available here http://docs.embarcadero.com/products/interbase/IBXE3Update4/ToGoQuickStart/Introduction.htm or if you prefer video’s check out InterBase Labs

Why InterBase?

If you want to know more about why customers are upgrading to the latest versions of InterBase and using InterBase in their current applications please read this Mobilising Enterprise Data – free white paper and also visit the InterBase pages