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.

Using the TListView to select multiple items

Its very easy to create a drop down list using the TComboBox to select a single item, but how can you easily select multiple items from a list across iOS and Android? Once easy way is to use the TListView component in EditMode. To view an example of this, add a TListView to a new form and using the FormCreate add in 20 items using a simple loop.

procedure TForm1.FormCreate(Sender: TObject);
var
  I: Integer;
begin
  ListView1.BeginUpdate;
  try
    for I := 1 to 20 do
      ListView1.Items.Add.Text := I.ToString();
  finally
    ListView1.EndUpdate;
  end;
end;

Also note the use of BeginUpdate and EndUpdate using the Try Finally block to speed up the processing. This prevents the ListView from redrawing between each item add.

Now using two buttons we can add on click events to see how to toggle the EditMode..

procedure TForm1.btnEditClick(Sender: TObject);
begin
  ListView1.EditMode := not ListView1.EditMode;
  (Sender as TButton).Text := 'EditMode '+ListView1.EditMode.ToString(TUseBoolStrs.True);
end;

..and secondly show the selected items.

Each TListViewItem has a Checked property that can be queried to discover if the item is selected. Again using a simple look it is possible to check each item and then add it to a list that we will show.

procedure TForm1.btnShowSelectedClick(Sender: TObject);
var
  SL : TStringList;
  I: Integer;
begin
  if ListView1.Items.CheckedCount = 0 then begin
    ShowMessage('No selected items');
    Exit;
  end;

  SL := TStringList.Create;
  try
    for I := 0 to ListView1.ItemCount -1 do begin
       if ListView1.Items[I].Checked then
         SL.Add(ListView1.Items[I].Text);
    end;
    ShowMessage('Items selected: '+SL.CommaText);
  finally
    SL.Free;
  end;
end;

For simplicity in the code above I have used the TStringList.CommaText to give a formatted output of the data.

ListView Multi-Select on iOS
ListView Multi-Select on iOS
Android ListView Multi-Select
Android ListView Multi-Select

Calling Objective C API’s with Delphi and Appmethod

Jacob Thurman from TwoDesk Software did a great developer skill sprint session on “Integrating more iOS with an Objective-C Call in your Delphi App” last week. If you have not been on them, the developer skill sprints series are excellent 20 minute deep dives into specific technologies in RAD Studio and Appmethod using both Delphi programming and C++ programming language.

What I like about this video is that it assumes no knowledge of Objective C and explains excellently the difference between Delphi, C++ and Objective C.

AppTethering Leaderboard – Passing Objects Remotely and using Resources

AppTethering opens up a range of powerful features for coupled applications. Beyond the basic features for calling remote actions (which is rather cool and quick to setup) it is also possible to share resources between applications.

To give you an example I have built a Scoreboard that records the best scores in a simple game. As the scores come in and the leaderboard is updated, it rebuilds the resource that is then automatically sent out to the connected clients.

Using the TTetheringAppProfile.Resources you can add at Design time or run time a TLocalResource. The TLocalResource has a concept of being either Shared or Mirrored (set via the Kind property). In the example of the leaderboard both the Game (clients) and Leaderboard (server) have the same resource added to their TetheringAppProfile with the difference being that the clients have the kind as Mirror rather than Shared.  As the central Shared resource is updated this is broadcast to the clients and the TLocalResource then fires its OnResourceReceived event.

So with that in mind, see it in action and download the code sample.

How to convert an object to JSON and back with a single line of code

Ever wanted to take an Object into a format that is easily persisted and back? Well now you can. New in XE6 is the REST.JSON unit. This allows you access to TJSON a class with some very helpful class methods. Using TJSON you can convert an object to a JSON string and back with a little help from generics along the way.

The following code uses a class called TFoo that has a Foo and a Fee property (string and Integer) Using TJson you can then see how to covert the object to a string and back ready for storage, transport etc.

uses REST.JSON; // Also new System.JSON
procedure TForm1.Button1Click(Sender: TObject);
var
  Foo: TFoo;
begin
  Foo := TFoo.Create;
  try
    Foo.Foo := 'Hello World';
    Foo.Fee := 42;
    Memo1.Lines.Text := TJson.ObjectToJsonString(Foo);
  finally
    Foo.Free;
  end;
  Foo := TJson.JsonToObject<TFoo>(Memo1.Lines.Text);
  try
    Foo.Fee := 100;
    Memo1.Lines.Add(TJson.ObjectToJsonString(Foo));
  finally
    Foo.Free;
  end;
end;

If you want to watch this being demo’ed this very show video shows it in action http://youtu.be/TSqWoFvjj5g

@Appmethod and @InterBase in Sweden

Yesterday I had the pleasure of speaking at the 2014 Dev:Mobile event in Gothenburg on Navigating the enterprise road to mobile with @Appmethod and also around Secure Multi-platform database development with @InterBase.

Stephen Ball in Gothenburg with Appmethod

The sessions had lots of Interesting questions, some of which from the database side will be covered in the next weeks webinar “Rising to the data security challenge” (Wednesday 28th May)

There were lots of amazed faces seeing how with Appmethod you can develop fast applications faster with Visual Live Bindings, access to BaaS etc and still have the applications as compiled native code! For those wanting to learn more quickly then I recommend the new series of 20 minute drop in Developer Skill Sprints running every Tuesday and Thursday (starting on the 29th May)

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

Remote Object Persistence with Parse and Kinvey

New BaaS components introduced in RAD Studio XE6 now make it easily to use remote data storage services offered by Kinvey and Parse along with Push notifications, File Storage and user authentication.

This can greatly reduce the infrastructure that you need to code, develop and manage with your application which speeds up time to market for including these features and can drastically reduce cost.

In the typical RAD, Object Orientated style there are a set of new interfaces behind the scenes that are implemented by new provider components that hook into the core components that work across providers, allowing you to code this effort once and work with it over Windows, Mac, iOS and Android.

In this short introduction video you will see how to work with the remote data store as an object persistence layer, learn about the new TBackendObjectList that contains the objects along with TBackendEntityValue to give you unique ID’s for each object without having to code that yourself, which is very cool indeed!

The video uses demo code that is in code central, submission 29846. NOTE: You will need to have a Kinvey or Parse account to use the demo, but a suitable accounts is quick and free to setup.