Best SERVER setup for to retrieve data from DB

[AndreFM]

Hi Danijel,

I'm started to using RTC with DB and would like to know your opinion to create something simple where users make a request for some data (DB) and I return via JSON.
I read some other posts here that you suggested creating the DB connection and query in runtime (or adding in a DataModule and creating this DM) in case I decide to use threaded server.
What I done for testing purpose was making a small change to the SimpleJSONServer example.
At the event DataReceived inside the condition if trueJSON then, I added following code.

Code:

      
      FDConnection1 := TFDConnection.Create(nil);
      FDQuery1 := TFDQuery.Create(nil);
      FDPhysFBDriverLink1 := TFDPhysFBDriverLink.Create(nil);
      rtcDS := TRtcDataSet.Create;
      Result := TRtcRecord.Create;
      try
        FDConnection1.DriverName := 'FB';
        FDConnection1.LoginPrompt := false;
        FDConnection1.Params.Database := 'C:\Dev\EMPLOYEE.FDB';
        FDConnection1.Params.UserName := 'SYSDBA';
        FDConnection1.Params.Password := 'masterkey';
        FDConnection1.Connected := true;
        FDQuery1.Connection := FDConnection1;
        FDQuery1.SQL.Text := 'select * from employee';
        FDQuery1.Active := true;

        DelphiDataSetToRtc(FDQuery1, rtcDS);
        Result.asDataSet['result'] := rtcDS;
        Srv.WriteEx(Result.toJSONEx);
        FDQuery1.Active := false;
        FDConnection1.Connected := false;
      finally
        Result.Free;
        rtcDS.Free;
        FDQuery1.Free;
        FDConnection1.Free;
        FDPhysFBDriverLink1.Free;
      end;
 

Note that this table contains only 50 records and this is just a test.
Can you tell me what what would be the best setup to use such code?
I tried using the options:
 - MultiThread
 - 500 Threads
 - (blocking or non blocking)
And using SoapUI to generate a load test, i managed to get around 2500 requests answered in 1 minute. (everything in local PC [db, soapui = client, server) which is not ideal, but just a test).
Using a similar program done with INDY, I had same result. I guess the answer will be that the constraint here is the DB connection and data retrieval.
And YES, just doing a simple request (e.g. date/time) to server, the RTC server was able to answer at least 4 times more requests (99K for RTC vs 25K for INDY).

I saw that one option to increase the performance for such DB request would be to use a DB Pool, but just confirming if I'm missing something here.
And in case I wish to send the result from a DataSet via JSON, is the best and easiest way to use DelphiDataSetToRtc? Client would RTC Client and also WEB Client.
Thx in advance

[D.Tkalcec (RTC)]

If you need to create/destroy or open/close "something" very often (like a DB connection), it is always a good idea to create a pool of that "something". Why? Because creating, destroying, opening and closing "things" usually takes A LOT more time than simply taking "things" out of a "pool" and placing them back when they are not needed.  Reusing the same objects (for example, by using a pool of these objects) also helps minimize memory fragmentation, which is another thing to keep in mind when you are writing a Multi-Threaded Server.

Best Regards,
Danijel Tkalcec