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.