Connection pooling setup

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
andrea.magni
Posts: 4
Joined: Wed 14 Aug 2019 06:36
Location: Italy
Contact:

Connection pooling setup

Post by andrea.magni » Wed 14 Aug 2019 06:43

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

mika
Posts: 13
Joined: Thu 08 May 2014 06:15

Re: Connection pooling setup

Post by mika » Wed 14 Aug 2019 08:44

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..

andrea.magni
Posts: 4
Joined: Wed 14 Aug 2019 06:36
Location: Italy
Contact:

Re: Connection pooling setup

Post by andrea.magni » Wed 14 Aug 2019 08:52

I've just tried, thanks. Nothing changed unfortunately...
Other ideas?

Sincerely,
Andrea

ViktorV
Devart Team
Posts: 2853
Joined: Wed 30 Jul 2014 07:16

Re: Connection pooling setup

Post by ViktorV » Wed 14 Aug 2019 10:18

Please describe in detail what is not working as expected in the pooling.

andrea.magni
Posts: 4
Joined: Wed 14 Aug 2019 06:36
Location: Italy
Contact:

Re: Connection pooling setup

Post by andrea.magni » Wed 14 Aug 2019 12:19

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
Last edited by andrea.magni on Thu 15 Aug 2019 06:49, edited 1 time in total.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup

Post by FredS » Wed 14 Aug 2019 15:27

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;

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: Connection pooling setup

Post by ertank » Wed 14 Aug 2019 15:36

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.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup

Post by FredS » Wed 14 Aug 2019 15:38

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

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: Connection pooling setup

Post by ertank » Wed 14 Aug 2019 16:09

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.
Last edited by ertank on Wed 14 Aug 2019 16:58, edited 1 time in total.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup

Post by FredS » Wed 14 Aug 2019 16:23

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.

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: Connection pooling setup

Post by ertank » Wed 14 Aug 2019 16:38

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?

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup

Post by FredS » 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

ertank
Posts: 160
Joined: Wed 13 Jan 2016 16:00

Re: Connection pooling setup

Post by ertank » Wed 14 Aug 2019 16:56

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...

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup

Post by FredS » Wed 14 Aug 2019 17:32

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.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup

Post by FredS » Wed 14 Aug 2019 18:22

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

Post Reply