Pooling

[hsvandrew]

Hi,

Could you please post a complete answer & code sample on the topic of 'pooling with sdac' - how is it meant to be done.

We use RemObjects which create threads and TRORemoteDataModule's to process requests server side. We want to ensure we are using the SDAC components as designed to handle a high number of server requests.

Components are created dynamically. Lets assume we need a TMSConnection and a TMSQuery to make a query to an SQL server, and let's assume, for the entire project, there is just a single database server and database name we want to talk with.

How should we dynamically create the components to setup a pool to talk with this database.

Should a single, global TMSConnection be created which can then be shared with every TMSQuery, or do we need a new global TMSConnection per thread? Is it ok for multiple TMSQuery's to be assigned to the same TMSConnection in a multi-threaded environment or will this cause conflicts.

[azyk]

SDAC and multi-threading
In a multi-threaded application, a separate TMSConnection instance should be used for each thread. You can pass a previously created TMSConnection instance to a thread or create it in a thread.

Connection Pooling in SDAC
If a connection uses pooling, then on calling the TMSConnection.Close method, the connection will not be closed, but it will be placed to the pool for a number of milliseconds specified in TMSConnection.PoolingOptions.ConnectionLifetime. After this period ends, the connection will be closed and deleted from the pool if no other TMSConnection uses it.

When opening a new connection, the pool will be checked first. If a connection with the same parameters is found in the pool, it will be returned from the pool. Otherwise, a new connection will be created.

If the number of open connections in the pool is equal to the value of TMSConnection.PoolingOptions.MaxPoolSize, then on an attempt to open a new connection in the pool, you will get an error message: 'Maximum connections reached in pool'.

See more details about TPoolingOptions in our documentation:
https://www.devart.com/sdac/docs/?devar ... embers.htm and an article about using Connection Pooling: https://www.devart.com/sdac/docs/?work_pooling.htm

The following is what a multi-threaded application may look like, that uses SDAC Connection Pooling (a separate instance of TMSConnection is created in each thread):

Code: Select all

program sdac_pooling;

{$APPTYPE CONSOLE}

uses
  MSAccess,
  ActiveX,
  Classes,
  SysUtils,
  Windows;

type
  TSDACPoolingThread = class(TThread)
  public
    Con: TMSConnection;
    Qry: TMSQuery;
    procedure Execute;override;
    constructor Create;
    destructor Destroy;override;
  end;

constructor TSDACPoolingThread.Create;
begin
  inherited;
  CoInitialize(nil);
  FreeOnTerminate := True;
  Con := TMSConnection.Create(nil);

  Con.Server := 'XXXXX';
  Con.Database := 'XXXXX';
  Con.Username := 'XXXXX';
  Con.Password := 'XXXXX';

  Con.Pooling := True;
//  set up custom pool size
//  Con.PoolingOptions.MaxPoolSize := 10;

  Qry := TMSQuery.Create(nil);
  Qry.Connection := Con;
end;

destructor TSDACPoolingThread.Destroy;
begin
  Qry.Free;
  Con.Free;
  CoUninitialize;
  inherited;
end;

procedure TSDACPoolingThread.Execute;
begin
  inherited;
  CoInitialize(nil);

//  add custom code here
//  Qry.SQL.Text := 'select * from emp';
//  Qry.Open;

  CoUninitialize;
end;

Var
  i: Integer;
  T: TSDACPoolingThread;
  ThreadArray :Array[0 .. 100] Of THandle;
begin

  for i := 0 to 100 do
  begin
    T := TSDACPoolingThread.Create;
    ThreadArray[i] := T.Handle;
    T.Resume;
    WriteLn('Start ', T.ThreadID);
  end;

  WaitForMultipleObjects(50, @ThreadArray[0], True, INFINITE);
  WaitForMultipleObjects(51, @ThreadArray[50], True, INFINITE);
  WriteLn('Finish');
  ReadLn;
end.

If you have any further questions, please contact us.

[hsvandrew]

Thank you for your reply. However I have a question regarding your sample.

I believed, and my testing seems to show it as correct, that the constructor of a TThread executes in the thread of the calling code that creates the thread, not the final thread that exists during execute and destroy. This seems to be true, for example, if you add this line (or a debug/log line) to the create, destroy, execute and main console areas of your sample code.
ShowMessage( 'TSDACPoolingThread.Create: ' + inttostr( GetCurrentThreadId() ) );

Essentially, the Con := TMSConnection.Create(nil); code is actually being executed in the main thread, but then the usage of that connection is being consumed in another thread (the execution/destruction runs in the thread created by the TThread when it resumes)

I don't see how this separates the connection out into the new thread if it is essentially created in the main thread. Can you explain this?

[azyk]

You can observe how separate connections to server are pulled out and returned to the connection pool from separate threads using our tool - dbMonitor. For this, run dbMonitor.exe or double-click in design time on the TMSSQLMonitor component on the form. In the appeared dbMonitor window select Tool->Options and in the 'Port number for client connection:' edit set the number of the port for connection to SQL Server(by default 1433). And add the following code into the above sample code, in order to enable connection pool monitoring:

Code: Select all

...
uses
...
,MSSQLMonitor, DASQLMonitor;
...
  ThreadArray :Array[0 .. 100] Of THandle;
  MSSQLMonitor1 : TMSSQLMonitor;
begin
  MSSQLMonitor1 := TMSSQLMonitor.Create(nil);

  MSSQLMonitor1.DBMonitorOptions.Port := 1433;
  MSSQLMonitor1.TraceFlags := MSSQLMonitor1.TraceFlags + [tfPool];
  MSSQLMonitor1.Active := True;

Download dbMonitor from: https://www.devart.com/dbmonitor/