Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Connection pooling setup

https://forums.devart.com/viewtopic.php?f=28&t=39169

Page 1 of 3

Connection pooling setup

Posted: Wed 14 Aug 2019 06:43
by andrea.magni
Hi, this is my first post on this forum.
I am approaching UniDAC so maybe I am missing something basic but I cannot make connection pooling to work.

Here is my code: https://pastebin.com/7ZiLVkYJ

Anyone could please point me out why it is not working?
My environment:
- Win10 64bit (VM)
- Delphi 10.3.2
- UniDAC8.0.1
- Firebird 3 (32bit)

Thanks in advance

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 08:44
by mika
Hi,
I have been using with ODBC without problems

Code: Select all

function CreateConnection(const  ADataBaseName: string);
begin
   Result := TUniConnection.Create( nil );
    Result.LoginPrompt := false;
    Result.ProviderName := 'ODBC';
    Result.Server := ADataBaseName;
    Result.Pooling := true;
    Result.Connect;
end;
I didn't see connect in your code..

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 08:52
by andrea.magni
I've just tried, thanks. Nothing changed unfortunately...
Other ideas?

Sincerely,
Andrea

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 10:18
by ViktorV
Please describe in detail what is not working as expected in the pooling.

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 12:19
by andrea.magni
Hi, thanks for chiming in.
I would expect to see a performance boost but I am seeing same performance whether Pooling is set to True or False.

My timing is around 40 seconds. In a similar scenario I would expect a 10x boost at least... Or at least some difference changing Pooling value... What am I missing?

Thanks a lot
Andrea

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 15:27
by FredS
Interesting test, made some modifications (see code below) but here are my results:

No Pool: 42.044-4221
Pooling: 43.957-2788

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  LIndex: Integer;
  LStopWatch: TStopWatch;
  LTasks: TArray<ITask>;
  LConnection : TUniConnection;
  LPooling : string;
begin
  Button1.Enabled := False;
  LConnection := TUniConnection.Create(nil);
  LConnection.ProviderName := 'InterBase';
  LConnection.Server :='localhost';
  LConnection.Database := 'employee';
  LConnection.Username := 'SYSDBA';
  LConnection.Password := 'masterkey';
  LConnection.LoginPrompt := False;
//  LConnection.SpecificOptions.Values['InterBase.ClientLibrary'] := GetEmbeddedFbDLL;
  LConnection.PoolingOptions.MaxPoolSize := 100;
  LConnection.PoolingOptions.MinPoolSize := 2;
  LConnection.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
  LConnection.PoolingOptions.Validate := True;
  LConnection.Pooling := ckPooling.checked;
  if LConnection.Pooling then  LPooling := 'Pooling: ' Else LPooling := 'No Pool: ';

  LConnection.Open; // Open before timing starts
  LStopWatch := TStopWatch.StartNew;
  LTasks := [];
  for LIndex := 1 to 1000 do
  begin
    LTasks := LTasks + [
      TTask.Run(
        procedure
        var
          LLConnection: TUniConnection;
          LQuery: TUniQuery;
          i : integer;
        begin
          LLConnection := TUniConnection.Create(nil, LConnection.ConnectString);
          LLConnection.Open;
          try
            LQuery := TUniQuery.Create(nil);
            try
              LQuery.Connection := LLConnection;
              LQuery.SQL.Text := 'select * from EMPLOYEE';
              for i := 0 to 10 do begin
                LQuery.Open;
                LQuery.Last;
                LQuery.Close;
              end;
            finally
              FreeAndNil(LQuery);
            end;
          finally
            FreeAndNil(LLConnection);
          end;
        end
      )
    ];

  end;

  TTask.WaitForAll(LTasks);
  LStopWatch.Stop;
  Memo1.Lines.Add(LPooling + LStopWatch.Elapsed.ToString(tssDebug));
  LConnection.Free;
  Button1.Enabled := True;
end;

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 15:36
by ertank
FredS wrote: ↑Wed 14 Aug 2019 15:27 Interesting test, made some modifications (see code below) but here are my results:
Any chance you share complete test project? Thanks.

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 15:38
by FredS

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 299
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 80
    Top = 72
    Width = 201
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object ckPooling: TCheckBox
    Left = 320
    Top = 76
    Width = 97
    Height = 17
    Caption = 'ckPooling'
    TabOrder = 1
  end
  object Memo1: TMemo
    Left = 80
    Top = 120
    Width = 201
    Height = 89
    TabOrder = 2
  end
end
Against a networked Docker Server:
No Pool: 01:12.378-1402
Pooling: 01:21.227-0238

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 16:09
by ertank
I use Delphi 10.3.2, UniDAC 8.0.1, target is Win32 executable.

My test is some what different. Here is code:

Code: Select all

unit Unit2;

interface

uses
  Winapi.Windows,
  Winapi.Messages,
  System.SysUtils,
  System.Variants,
  System.Classes,
  Vcl.Graphics,
  Vcl.Controls,
  Vcl.Forms,
  Vcl.Dialogs,
  Vcl.StdCtrls,
  DASQLMonitor,
  UniSQLMonitor;

type
  TForm2 = class(TForm)
    Button1: TButton;
    UniSQLMonitor1: TUniSQLMonitor;
    Button2: TButton;
    Button3: TButton;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form2: TForm2;

implementation

{$R *.dfm}

uses
  Uni,
  Diagnostics,
  Threading,
  InterbaseUniProvider,
  System.TimeSpan;


procedure SetupPooledConnection(const AUniConn: TUniConnection);
begin
  AUniConn.ProviderName := 'InterBase';
  AUniConn.Server :='localhost';
  AUniConn.Database := 'C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB';
  AUniConn.Username := 'SYSDBA';
  AUniConn.Password := 'masterkey';
  AUniConn.SpecificOptions.Values['ClientLibrary'] := 'C:\Program Files\Firebird\Firebird_2_5\WOW64\fbclient.dll';
  AUniConn.LoginPrompt := False;
  AUniConn.PoolingOptions.MaxPoolSize := 100;
  AUniConn.PoolingOptions.MinPoolSize := 2;
  AUniConn.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
  AUniConn.PoolingOptions.Validate := True;
  AUniConn.Pooling := True;
end;

procedure SetupRegularConnection(const AUniConn: TUniConnection);
begin
  AUniConn.ProviderName := 'InterBase';
  AUniConn.Server :='localhost';
  AUniConn.Database := 'C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB';
  AUniConn.Username := 'SYSDBA';
  AUniConn.Password := 'masterkey';
  AUniConn.SpecificOptions.Values['ClientLibrary'] := 'C:\Program Files\Firebird\Firebird_2_5\WOW64\fbclient.dll';
  AUniConn.LoginPrompt := False;
  AUniConn.Pooling := False;
end;

procedure TForm2.Button1Click(Sender: TObject);
var
  LIndex: Integer;
  LStopWatch: TStopWatch;
  LTasks: TArray<ITask>;
begin
  LStopWatch := TStopWatch.StartNew;
  LTasks := [];
  for LIndex := 1 to 1000 do
  begin
    LTasks := LTasks + [
      TTask.Run(
        procedure
        var
          LConnection: TUniConnection;
          LQuery: TUniQuery;
        begin
          LConnection := TUniConnection.Create(nil);
          try
            SetupPooledConnection(LConnection);

            LQuery := TUniQuery.Create(nil);
            try
              LQuery.Connection := LConnection;
              LQuery.SQL.Text := 'select * from EMPLOYEE';

              LQuery.Open;
              LQuery.Last;
            finally
              FreeAndNil(LQuery);
            end;
          finally
            FreeAndNil(LConnection);
          end;
        end
      )
    ];
  end;

  TTask.WaitForAll(LTasks);
  LStopWatch.Stop;
  Button1.Caption := LStopWatch.ElapsedMilliseconds.ToString;
end;

procedure TForm2.Button2Click(Sender: TObject);
var
  LIndex: Integer;
  LConnection: TUniConnection;
  LQuery: TUniQuery;
  LStopWatch: TStopWatch;
begin
  LStopWatch := TStopWatch.StartNew;
  for LIndex := 1 to 1000 do
  begin
    LConnection := TUniConnection.Create(nil);
    try
      SetupRegularConnection(LConnection);

      LQuery := TUniQuery.Create(nil);
      try
        LQuery.Connection := LConnection;
        LQuery.SQL.Text := 'select * from EMPLOYEE';

        LQuery.Open;
        LQuery.Last;
      finally
        FreeAndNil(LQuery);
      end;
    finally
      FreeAndNil(LConnection);
    end;
  end;
  Button2.Caption := LStopWatch.ElapsedMilliseconds.ToString;
end;

procedure TForm2.Button3Click(Sender: TObject);
var
  LIndex: Integer;
  LStopWatch: TStopWatch;
  LTasks: TArray<ITask>;
  LConnection : TUniConnection;
  LPooling : string;
begin
  Button1.Enabled := False;
  LConnection := TUniConnection.Create(nil);
  LConnection.ProviderName := 'InterBase';
  LConnection.Server :='localhost';
  LConnection.Database := 'C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB';
  LConnection.Username := 'SYSDBA';
  LConnection.Password := 'masterkey';
  LConnection.LoginPrompt := False;
  LConnection.SpecificOptions.Values['ClientLibrary'] := 'C:\Program Files\Firebird\Firebird_2_5\WOW64\fbclient.dll';
  LConnection.PoolingOptions.MaxPoolSize := 100;
  LConnection.PoolingOptions.MinPoolSize := 2;
  LConnection.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
  LConnection.PoolingOptions.Validate := True;
  LConnection.Pooling := True;
  if LConnection.Pooling then  LPooling := 'Pooling: ' Else LPooling := 'No Pool: ';

  LConnection.Open; // Open before timing starts
  LStopWatch := TStopWatch.StartNew;
  LTasks := [];
  for LIndex := 1 to 1000 do
  begin
    LTasks := LTasks + [
      TTask.Run(
        procedure
        var
          LLConnection: TUniConnection;
          LQuery: TUniQuery;
          i : integer;
        begin
          LLConnection := TUniConnection.Create(nil, LConnection.ConnectString);
          LLConnection.Open;
          try
            LQuery := TUniQuery.Create(nil);
            try
              LQuery.Connection := LLConnection;
              LQuery.SQL.Text := 'select * from EMPLOYEE';
//              for i := 0 to 10 do begin
                LQuery.Open;
                LQuery.Last;
//                LQuery.Close;
//              end;
            finally
              FreeAndNil(LQuery);
            end;
          finally
            FreeAndNil(LLConnection);
          end;
        end
      )
    ];

  end;

  TTask.WaitForAll(LTasks);
  LStopWatch.Stop;
  Memo1.Lines.Add(LPooling + LStopWatch.Elapsed.ToString());
  LConnection.Free;
  Button1.Enabled := True;
end;

procedure TForm2.FormCreate(Sender: TObject);
begin
  Memo1.Clear();
end;

end.
Here is DFM:

Code: Select all

object Form2: TForm2
  Left = 0
  Top = 0
  Caption = 'Form2'
  ClientHeight = 289
  ClientWidth = 554
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  DesignSize = (
    554
    289)
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 8
    Top = 8
    Width = 75
    Height = 25
    Caption = 'Pooled'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 89
    Top = 8
    Width = 100
    Height = 25
    Caption = 'Direct Connection'
    TabOrder = 1
    OnClick = Button2Click
  end
  object Button3: TButton
    Left = 195
    Top = 8
    Width = 150
    Height = 25
    Caption = 'Alternative with pooling'
    TabOrder = 2
    OnClick = Button3Click
  end
  object Memo1: TMemo
    Left = 8
    Top = 39
    Width = 538
    Height = 242
    Anchors = [akLeft, akTop, akRight, akBottom]
    Lines.Strings = (
      'Memo1')
    ScrollBars = ssBoth
    TabOrder = 3
    WordWrap = False
  end
  object UniSQLMonitor1: TUniSQLMonitor
    Left = 336
    Top = 80
  end
end
I run it on a local FirebirdSQL 2.5 server. Click order: "Direct Connection", "Pooled", Alternative with pooling" results as
Direct Connection: 18728ms
Pooled: 5043ms
Alternative with pooling: 4.8179159 seconds

If I start testing clicking one of the pooled buttons then Direct Connection is way faster like 9017ms which makes me think that even we specify no pool, UniDAC uses it internally for a reason. Though, Direct Connection is still slow like 19-21 seconds even if I click it more than 60 seconds after pooled ones. As connect timeout for pooled is over, UniDAC is seemingly terminating pool connections.

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 16:23
by FredS
My first test was against FB 3.05, the Docker one is 3.04, both in Rio.
This one is using Berlin against the same Docker server.
In Berlin I tweaked System.Threading to allocate new Tasks faster:

No Pool: 28.232-0294
Pooling: 01:20.369-7462

EDIT:
I should add that I added the extra loop because I know the standard System.Threading unit is too slow for short tests like this.

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 16:38
by ertank
FredS wrote: ↑Wed 14 Aug 2019 16:23 No Pool: 28.232-0294
Pooling: 01:20.369-7462
How should we read above figures? Like for no pool: 28 seconds, 232 ms?

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 16:45
by FredS
Sorry, one of my internal helpers:

tssDebug = [d.][hh:][nn:]ss.zzz-tttt
or 1 minute 20 seconds with Pooling

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 16:56
by ertank
FredS wrote: ↑Wed 14 Aug 2019 16:45 Sorry, one of my internal helpers:

tssDebug = [d.][hh:][nn:]ss.zzz-tttt
or 1 minute 20 seconds with Pooling
Your code runs faster if not pooled. Strange...

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 17:32
by FredS
Same test using Berlin against a fresh Docker FB 2.5.9 Super Server:

No Pool: 29.147-8424
Pooling: 01:12.745-0770

NOTE: Used FB Client 3.05

Rio against FB 2.5.9 ss:

No Pool: 52.480-7969
Pooling: 01:10.386-6314
No Pool: 22.634-2510

Note the second 'No Pool' run uses existing cached Tasks, also goes to show how much more responsive my tweaks make System.Threading in Berlin.

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 18:22
by FredS
One more bit of info, ran the first 'No Pool' to allow the Tasks to spin up in Rio.
Ran the second and it ended with 92% CPU usage which is normal.
Immediately ran 'Pooling' it never made it above 42% CPU usage, best guess is that its being throttled by a mutex or whatnot.

Code: Select all

No Pool:    50.901-0948
No Pool:    38.275-8876
Pooling: 01:15.416-9918
All times are UTC
Page 1 of 3
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/