Print Page - Best SERVER setup for to retrieve data from DB

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

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

RTC Forums

Subscription => Support => Topic started by: AndreFM on January 22, 2018, 12:32:58 PM