Slow Retrivial data

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for MySQL in Delphi and C++Builder
Post Reply
jerduval
Posts: 23
Joined: Mon 13 Sep 2010 08:33
Location: Colombes, France

Slow Retrivial data

Post by jerduval » 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 :

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;

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

Re: Slow Retrivial data

Post by ViktorV » Fri 04 Dec 2020 15:21

To solve the issue, please try to set the FetchAll option to False.
See more details about this option in the Readme.html file at: %DBXMySQL%\Readme.html where %DBXMySQL% is the path to the installed dbExpress driver for MySQL.

jerduval
Posts: 23
Joined: Mon 13 Sep 2010 08:33
Location: Colombes, France

Re: Slow Retrivial data

Post by jerduval » Fri 04 Dec 2020 15:49

Hi Viktor, thank you for replay.
We tried to put fetchall option to false, you're right, the open is much faster but every "next" action on the query is very slow and, finally, the whole loop is still very slow.
When closing connection between 2 query isn't slowly than that.
Please can you try to reproduice problem, we can send you example project if you want ?
Regards,

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

Re: Slow Retrivial data

Post by ViktorV » Tue 08 Dec 2020 11:30

When the FetchAll option is set to True, all records satisfying the condition will be fetched after invoking the Open method. Retrieving data from large datasets like yours may take a lot of time. When FetchAll is False, the Open method fetches only the records requested by a data-aware component or program. Changing the value of the FetchAll doesn't affect the performance of data retrieval in your example because you're using the while not Query.Eof loop, which retrieves all records.
You may send us a complete sample project, including the scripts for creating and populating database objects, through the contact form https://devart.com/company/contactform.html

jerduval
Posts: 23
Joined: Mon 13 Sep 2010 08:33
Location: Colombes, France

Re: Slow Retrivial data

Post by jerduval » Wed 09 Dec 2020 07:52

Sample project is send, thank you.
Regards

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

Re: Slow Retrivial data

Post by ViktorV » Wed 27 Jan 2021 15:24

Thank you for the information. We have reproduced and fixed the issue. This fix will be included in the next build of dbExpress driver for MySQL.

Post Reply