Performance & low memory - Opinion

[BlanchardP]

Hello,

To optimize database queries to get a good balance of speed and low memory usage, this is what I have come up with going through your help and demos. Of all the request, I would say that 90% of them are quick and return small amounts of data, while 10% return large amounts of data.

I have one central routine to get database connections. It hopefully is setup to implement pooling :

function TfdCore.GetDatabaseConnection(const sDatabaseName: RawByteString): TMyConnection;
begin
Result:= TMyConnection.Create(self);

Result.Database:= fdUTF8ToString(sDatabaseName);
Result.Password:= 'password';
Result.Pooling:= true;
Result.PoolingOptions.MaxPoolSize:= 16;
Result.PoolingOptions.MinPoolSize:= 4;
Result.Port:= 12345;
Result.Server:= '127.0.0.1';
Result.Username:= 'username';
end;

I mostly use TMyQuery. This is the way I use them :

daSQLQuery:= TMyQuery.Create(self);

try
daSQLQuery.Connection:= GetDatabaseConnection(databaseName);
daSQLQuery:= TMyQuery.Create(self);
daSQLQuery.FetchAll:= false;
daSQLQuery.FetchRows:= 16;
daSQLQuery.Options.FlatBuffers:= true;
daSQLQuery.Options.StrictUpdate:= false;
daSQLQuery.ReadOnly:= true;
daSQLQuery.UniDirectional:= true;

daSQLQuery.Connection:= GetDatabaseConnection(zRemoteCommand.getDatabaseName);
daSQLQuery.SQL.Text:= fdUTF8ToString(sSQL);
daSQLQuery.Execute;
daSQLQuery.First;

if (not(daSQLQuery.Eof)) then
...

finally
daSQLQuery.Close;
daSQLQuery.Connection.Close;
daSQLQuery.Connection.Disconnect;

FreeAndNil(daSQLQuery);
end;

Is this the good way to use pooling? Is this the best way to get very fast/low memory queries?

Any addition, modification or opinion would be very much appreciated.

Thank you.


Pierre

[RNOVAK]

The pooling is rigth.

Dont forget: if you dont need anymore one connection, do disconect, so connection returns to pool (when pooling is true) and you can to reuse it later.

Your TMyQuery.Create has a problem, maybe is only your copy of piece of code...
In your code you created more than one TQuery object:

"I mostly use TMyQuery. This is the way I use them :

daSQLQuery:= TMyQuery.Create(self); << (dont need this)

try
daSQLQuery.Connection:= GetDatabaseConnection(databaseName);
daSQLQuery:= TMyQuery.Create(self); << (this line must be before "daSQLQuery.Connection:= GetDatabaseConnection(databaseName);")
daSQLQuery.FetchAll:= false;
...
"

You got 2 connections
daSQLQuery.Connection:= GetDatabaseConnection(databaseName);
daSQLQuery.Connection:= GetDatabaseConnection(zRemoteCommand.getDatabaseName);
but freed only one in
daSQLQuery.Connection.Disconnect;

I think you copied lines from diferent piece of logic codes because the logic must be:
daSQLQuery.Connection:= GetDatabaseConnection(databaseName);
.. you use the connection
.. so you do disconnect to return it to the pool
other connection:
daSQLQuery.Connection:= GetDatabaseConnection(zRemoteCommand.getDatabaseName);
.. you use the connection
.. so you do disconnect to return it to the pool
...
this sequence is right:
try
daSQLQuery:= TMyQuery.Create(self);
daSQLQuery.Connection:= GetDatabaseConnection(databaseName);
daSQLQuery.FetchAll:= false;
daSQLQuery.FetchRows:= 16;
daSQLQuery.Options.FlatBuffers:= true;
daSQLQuery.Options.StrictUpdate:= false;
daSQLQuery.ReadOnly:= true;
daSQLQuery.UniDirectional:= true;
if (not(daSQLQuery.Eof)) then
...

finally
daSQLQuery.Close;
daSQLQuery.Connection.Close;
daSQLQuery.Connection.Disconnect;
FreeAndNil(daSQLQuery);
end;

...other try
try
daSQLQuery:= TMyQuery.Create(self);
daSQLQuery.Connection:= GetDatabaseConnection(zRemoteCommand.getDatabaseName);
daSQLQuery.SQL.Text:= fdUTF8ToString(sSQL);
daSQLQuery.Execute;
daSQLQuery.First;

if (not(daSQLQuery.Eof)) then
...

finally
daSQLQuery.Close;
daSQLQuery.Connection.Close;
daSQLQuery.Connection.Disconnect;

FreeAndNil(daSQLQuery);
end;

Dont forget to see max_connectios of myini file from MySQL too. Must support your necessary connections.

Roberto

[DemetrionQ]

Hello.

In addition to RNOVAK's recommendations, I must say that calling the GetDatabaseConnection function creates a new TMyConnection object, and there will be TMyConnection objects accumulated during your application running. Perhaps, the most optimal solution is to free the TMyConnection object after finishing work with it, or to use one TMyConnection object for all TMyQueries, if your application is not multi-thread.