Oleg, for me this is a bug. It should allow me to have multiple connection pools. Is there any reason why this cannot be accomplished?
Looking at your example. IMO it is not a good example to explain how a connection pool works as something does not make sense.
The first connection LConnection you simply connect to database, but there is no query running in this connection, it just stay idle. It is a wasted connection. Or can you explain better why you done the connection?
From what I could see, LLConnection is just grabbing the connection settings and the queries are using this connection. We can comment out the Lconnection (open and close) to avoid unnecessary connection.
Code: Select all
// LConnection.Open;
// LConnection.Close; // Some caching should happen !!!
// LConnection.Open; // Open before timing starts
I tried the your example creating 2 pools to two different databases (SQL Server and MySQL) using MinPoolSize and MaxPoolSize = 5 for each connection.
It works but wrongly.
First, SQL Server opened ONLY 2 connections and MySQL only 1. This is very weird, but could be because I was expecting the amount of connections according to MinPoolSize, but later I noticed that MinPoolSize is to avoid freeing connection and not exactly that it would create the amount of connections in advance.
I added a "sleep(500)"" before closing the query, then I saw 5 connections at the first database and one connection at the other database.
The connections at the second database increased to a higher amount only when the first task completed. Therefore, it seems the amount of connections does not work properly with two connection pools. As you said, it seems that internally UNIDAC only accepts one pool at the time which is not good for me as I need 2 and more connection pools to different databases (could also be the same database).
Code: Select all
var
LPooling: string;
begin
Button1.Enabled := False;
Button1.Caption := 'Running..';
TTask.Run(
procedure
var
LIndex: Integer;
LStopWatch: TStopWatch;
LTasks: TArray<ITask>;
LConnection: TUniConnection;
begin
LConnection := TUniConnection.Create(nil);
LConnection.ProviderName := 'SQL Server';
LConnection.Server := '192.168.0.171';
LConnection.port := 1433;
LConnection.Database := 'sakila';
LConnection.Username := 'tutorial';
LConnection.Password := 'Tutorial.123!';
LConnection.LoginPrompt := False;
LConnection.PoolingOptions.MaxPoolSize := 5;
LConnection.PoolingOptions.MinPoolSize := 5;
LConnection.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
LConnection.PoolingOptions.Validate := True;
LConnection.Pooling := True;
LConnection.SpecificOptions.Values['Provider'] := 'prDirect';
LConnection.SpecificOptions.Values['ApplicationName'] := 'My application';
//LConnection.Open;
//LConnection.Close; // Some caching should happen !!!
//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 dbo.actor';
for i := 0 to 10 do
begin
LQuery.Open;
LQuery.Last;
LQuery.Close;
end;
finally
FreeAndNil(LQuery);
end;
finally
LLConnection.Close;
FreeAndNil(LLConnection);
end;
end)];
end;
TTask.WaitForAll(LTasks);
LConnection.Free;
LStopWatch.Stop;
TThread.Synchronize(nil,
procedure
begin
Memo1.Lines.Add('SQL SERVER - '+LPooling + LStopWatch.Elapsed.ToString());
//Button1.Enabled := True;
//Button1.Caption := 'Button1'
end);
end);
TTask.Run(
procedure
var
LIndex: Integer;
LStopWatch: TStopWatch;
LTasks: TArray<ITask>;
LConnection: TUniConnection;
begin
LConnection := TUniConnection.Create(nil);
LConnection.ProviderName := 'MySQL';
LConnection.Server := '192.168.0.171';
LConnection.Database := 'sakila';
LConnection.Username := 'tutorial';
LConnection.Password := 'Tutorial.123!';
LConnection.LoginPrompt := False;
LConnection.PoolingOptions.MaxPoolSize := 10;
LConnection.PoolingOptions.MinPoolSize := 10;
LConnection.PoolingOptions.ConnectionLifetime := 5000; // 60 seconds
LConnection.PoolingOptions.Validate := True;
LConnection.Pooling := True;
// LConnection.Open;
// LConnection.Close; // Some caching should happen !!!
// 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 actor';
for i := 0 to 10 do
begin
LQuery.Open;
LQuery.Last;
LQuery.Close;
end;
finally
FreeAndNil(LQuery);
end;
finally
LLConnection.Close;
FreeAndNil(LLConnection);
end;
end)];
end;
TTask.WaitForAll(LTasks);
LConnection.Close;
FreeAndNil(LConnection);
LStopWatch.Stop;
TThread.Synchronize(nil,
procedure
begin
Memo1.Lines.Add('MYSQL - '+LPooling + LStopWatch.Elapsed.ToString());
//Button1.Enabled := True;
//Button1.Caption := 'Button1'
end);
end);