Speed Up Your Delphi Database Apps Using Remote SQL

remotesql I recently came across a new product in the Delphi world called RemoteSQL. I use and see a lot of Delphi components in my day to day work, but this one caught my eye because it deals with one of my (/everyone’s) pet peeves – speed. RemoteSQL is an additional client-server application tier that significantly speeds up the connection of a client application to its database, when they are not in the same network. According to GoFast, the creators of RemoteSQL, in a typical environment using their system can speeds up your connection by a factor of between 5 and 40, and in extreme cases up to a factor of 80 which, if true, are rather impressive numbers.

Why?

The people at GoFast focused their attention on Delphi applications that use a database that isn’t in the same local network as the client application. When the client and the DB are in the same LAN, the network speed provided by the default Delphi components is pretty much as fast as it’s ever going to get, so if your database response is slow, the network isn’t to blame. However if your DB is somewhere on the Internet, in a cloud for example, the network conditions can have a great impact on your application’s performance. This is where RemoteSQL comes in play as a solution to some of the most common network speed problems.

How?

RemoteSQL has two parts; RemoteSQL Server and RemoteSQL Client. RemoteSQL Server is a Windows service that you install in the same network as the DB that will become your new point of entry for the database, i.e. the client will connect to the database indirectly via the RemoteSQL Server. RemoteSQL Client are just client components installed into the Delphi IDE.
The system does two things in order to speed up the network communication. Firstly, it holds sending the query to the server until the last possible moment, that way it can group multiple sql queries in a single network request. Secondly, it compresses both the requests and the responses. It’s as simple as that.
RemoteSQL also has a number of other interesting features like data encryption, FireDac support, the ability to transfer the data in JSON format, and mobile platform support, both for Android and iOS. It can also serve as a single entry point for multiple databases systems at the same time, meaning that you no longer need to expose multiple database servers to the internet, rather a single RemoteSQL server (more info on the GoFast website).

Some testing

In order to try out RemoteSQL I made a simple app that gets data from a FireBird database using both RemoteSQL and FireDac, and writes the elapsed time to the console window. The database is in my local network, but I used a WAN emulator with the latency set to 100ms in order to simulate connecting to a database in the cloud.
The FireDac function is pretty straightforward, i.e. create the connection with the appropriate connection parameters, execute the query, and return the elapsed time and record count:

function GetDataOverDirectConnection(ASQL:String; out ATime: int64): Integer;
var
  StopWatch: TStopWatch;
  Conn: TFDConnection;
  DS: TFDQuery;
begin
  Conn := TFDConnection.Create(Nil);
  DS := TFDQuery.Create(Nil);
  DS.Name := 'Direct';
  DS.Connection := Conn;
  StopWatch := TStopWatch.Create;
  try
    // initialize connection
    Conn.Params.Add('DriverID=FB' );
    Conn.Params.Add('Server=DBMSServer');
    Conn.Params.Add('Database=northwind');
    Conn.Params.Add('User_name=SYSDBA');
    Conn.Params.Add('Password=masterkey');
    Conn.FetchOptions.Mode := fmAll;
    Conn.Connected := True;

    // get data, measure time
    StopWatch.Start;
    DS.Open(ASQL);
    StopWatch.Stop;

    ATime := StopWatch.ElapsedMilliseconds;
    Result := DS.RecordCount;
    Conn.Connected := False;
  finally
    Conn.Free;
    DS.Free;
  end;
end;

The RemoteSQL function is, pleasantly enough, surprisingly similar, i.e. create the connection with the appropriate connection parameters, execute the query, and return the elapsed time and record count. This means that if you decided to start using RemoteSQL the changes you’d have to make in your code are minimal.

function GetDataOverRemoteSQL(ASQL: String; out ATime: int64): Integer;
var
  StopWatch: TStopWatch;
  RemSQL: TRemoteSQL_Handler;
  DS: TFDMemTable;
begin
  RemSQL := TRemoteSQL_Handler.Create(Nil);
  DS := TFDMemTable.Create(Nil);
  DS.Name := 'RemoteSQL';
  StopWatch := TStopWatch.Create;
  try
    // initialize RemoteSQL connection
    RemSQL.Server := 'RemoteSQLServer';
    RemSQL.Port := 9020;
    RemSQL.DBMS_Type := Firebird;
    RemSQL.DBMS_ServerName := 'DBMSServer';
    RemSQL.DBMS_DatabaseName := 'northwind';
    RemSQL.Login := 'SYSDBA';
    RemSQL.Password := 'masterkey';
    RemSQL.Connected := True;

    // get data, measure time
    StopWatch.Start;
    DS.LoadFromStream(RemSQL.SQL_GetData(ASQL, dt_AnyDac_Firedac, ft_Firedac_Binary));
    StopWatch.Stop;

    ATime := StopWatch.ElapsedMilliseconds;
    Result := DS.RecordCount;
    RemSQL.Connected := False;
  finally
    RemSQL.Free;
    DS.Free;
  end;
end;

The program calls each of the functions and writes the output to the console window.

const
  _MSG = '%s: %d records, elapsed time: %5d ms';

var
  TimeElapsed: int64;
  RecCount: Integer;
begin
  try
    Writeln(‘Test results:’);
    // RemoteSQL
    recCount := GetDataOverRemoteSQL('SELECT * from "Orders"', TimeElapsed);
    Writeln(Format(_MSG, ['RemoteSQL', RecCount, TimeElapsed]));

    // Direct
    recCount := GetDataOverDirectConnection('SELECT * from "Orders"', TimeElapsed);
    Writeln(Format(_MSG, ['DirectCon', RecCount, TimeElapsed]));

    ReadLn; 
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

The results were in line with what RemoteSQL promises, the run time was a little over 4 times faster when getting 830 records with RemoteSQL than with FireDac (350 ms vs 1481 ms). While the connection between me and my DB server isn´t poor, this test showed that my applications performance could gain a lot from using this product.


Test results:
RemoteSQL: 830 records, elapsed time: 350 ms
DirectCon: 830 records, elapsed time: 1481 ms

Looking good!

I’m happy to say that RemoteSQL met my expectations. I was looking for a way to speed up my application’s communication with the cloud and RemoteSQL completed the task perfectly. Don’t be alarmed by its seemingly complex architecture, it’s a simple system that does exactly what it says it does, and rather well. If you have a Delphi application that uses a database in the cloud, look into RemoteSQL, as it just might be the solution for many of your problems.

5 thoughts on “Speed Up Your Delphi Database Apps Using Remote SQL

  1. Murat Ak

    Hi A,

    Mormot is really good product,
    But remote Syndb is not crossplatform.
    And multiple sql queries in a single network request is so important.
    I check remote syndb, and You can remember i did some work on syndb with clientdataset.
    I wish 2 request can be done in future.
    I will test remote sql too.
    But in Web site i didnt see remote sql is working on ios.
    And i have a doubt about source code of remote sql
    Best regards

    Reply
    1. A. Bouchez

      You are right, SynDBRemote is not NextGen compatible.
      But it works under Linux or OSX, using FPC.
      So it is cross-platform, with a true cross-platform compiler, like FPC.
      IMHO Delphi is not a cross-platform compiler. It has a cross-platform UI/RTL framework, but two diverse compilers. The marketing blabla is somewhat confusing about it.

      Our remote ORM and our SOA solution is cross-platform, including NextGen targets, also for the clients.
      For mobile clients, I would never put the SQL logic within the application.
      I would use high-level SOA services, and the SQL logic on the server side.
      Validating and deploying the app is a real PITA, especially under iOS.

      In fact, I would never advice creating a new application with SQL within the app.
      We definitively need a true 3-Tier or 4-Tier architecture nowadays.
      Remote SQL execution, which is a 2-Tier, may help for legacy RAD applications.
      But I would never invest some money in a 2-Tier architecture, for any modern development.

      Reply
  2. Mike Margerum

    Looks like a nice set of components, but i’m not liking the server activation. I need to know I can keep running this server even if the company goes under.

    Reply
  3. Kmorwath

    Tell me how using a JSON format over the DB native binary format can improve performance… with Delphi standard components (using first cached queries and then datasnap) – and “clever” (aka proper) SQL programming, I was able years ago to obtain very good DB performance over 28.8 kbps modems.
    What you really need is to avoid to query entire tables and unneeded fields, and lots of useless roundtrips. And useless formats like JSON or XML. You can do it with Delphi 3, if you like, and it you’re a proper DB developer.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *