Slow Retrivial data
Posted: Mon 30 Nov 2020 12:09
Hi, We have a proble to retrieve some data, it is very slow in this case :
- Open Connection
- Open Select Query From TABLE witch get a single row with 3Mo string value (xml formatting, no need to read this value, juste open the query)
- Close Query
- Open Select Query From TABLE witch get a about 20000 rows => This step takes about 10 minutes !
- Close Query
- Close Connection
If we close and reopen connection between 2 queries, the 2nd open is immediatly !
Connection :
DriverName := 'DevartMySQLDirect';
Params.Clear;
Params.Add('BlobSize=-1');
Params.Add('HostName=xxx:3306');
Params.Add('DataBase=Test');
Params.Add('User_Name=root');
Params.Add('Password=xxxx');
KeepConnection := true;
What's going wrong with that case ?
Code example :
- Open Connection
- Open Select Query From TABLE witch get a single row with 3Mo string value (xml formatting, no need to read this value, juste open the query)
- Close Query
- Open Select Query From TABLE witch get a about 20000 rows => This step takes about 10 minutes !
- Close Query
- Close Connection
If we close and reopen connection between 2 queries, the 2nd open is immediatly !
Connection :
DriverName := 'DevartMySQLDirect';
Params.Clear;
Params.Add('BlobSize=-1');
Params.Add('HostName=xxx:3306');
Params.Add('DataBase=Test');
Params.Add('User_Name=root');
Params.Add('Password=xxxx');
KeepConnection := true;
What's going wrong with that case ?
Code example :
Code: Select all
var
Query: TSQLQuery;
Cnx: TSQLConnection;
begin
Memo1.Lines.Clear;
Cnx := TSQLConnection.Create(nil);
try
Cnx.DriverName := 'DevartMySQLDirect';
Cnx.Params.Clear;
Cnx.Params.Add('BlobSize=-1');
Cnx.Params.Add('HostName=xxxx:3306');
Cnx.Params.Add('DataBase=Test');
Cnx.Params.Add('User_Name=root');
Cnx.Params.Add('Password=xxx');
Cnx.KeepConnection := true;
Cnx.Open;
try
Query := TSQLQuery.Create(nil);
try
Query.SQLConnection := Cnx;
Query.SQL.Text := 'select params_xml from PARAMS_SESSION_CALCUL where id_sessions_calcul = 118002';
Query.Open;
Query.Close;
finally
FreeAndNil(Query);
end;
// Cnx.Close;
// Cnx.Open;
Query := TSQLQuery.Create(nil);
try
Query.SQLConnection := Cnx;
Query.SQL.Add('select * from sim_taches_planning_prev where ps.ID_SIMULATION=118002');}
Query.Open; // Very slow, about 10 minutes
try
Query.First;
while not Query.Eof do
begin
Query.Next;
end;
finally
Query.Close;
end;
finally
FreeAndNil(Query);
end;
finally
Cnx.Close;
end;
finally
Cnx.Free;
end;