Copy dataset to another object (very, very, very slow)

[taazz]

if there aren't any controls attached on the query and a simple iteration through a few thousand records (how many are returned?) takes 6 minutes then something is wrong on your database and query. More info is required of the database and query.

[ttomas]

Can you check

Code: [Select]

      SQL.Add('select count(*) from ' + strStoredProcName + '(' + QuotedStr(strStartDate) + ',' + QuotedStr(strEndDate) + ')');
      // Open the dataset
      Open;                    // Takes about XX secs to complete
This is select count(*) not select *
If Open take long time then your problem is in Firebird side, not optimized sql select
When you execute sql statement you must look at execution time and fetch time.
Execution (Prepare) time when sql server prepare, optimize and try to find best index match for query.
Fetch time, time to find next record
Total time=ExecTime+(num_records*FetchTime)

[JD]

if there aren't any controls attached on the query and a simple iteration through a few thousand records (how many are returned?) takes 6 minutes then something is wrong on your database and query. More info is required of the database and query.

Nothing is wrong with the database & I affirm that the query takes about 10 seconds to be complete. It is the copying of query results to something else that takes time. Once the copying is done, it speeds up again. I have timed every part of the process & I am certain of what I'm saying.

The query object is a TZQuery. It is exactly what you get when you place it on a form for the first time; nothing added or removed.

[Leledumbo]

Digging deeper, it seems like TCollection has notification feature, which could be slow. Moreover, TCollection and TCollectionItem use mutual reference (perhaps for faster two way access). You can try using different data structures instead. Internally, TCollection uses TFPList which doesn't have notification feature and is extremely fast, you can guarantee O(1) insertion by first setting its Count property.

[BigChimp]

@Leledumbo: ok, but just running the query, going forward until eof without copying already explains most of the slow performance.
See http://forum.lazarus.freepascal.org/index.php/topic,23057.msg137142.html#msg137142

Edit: perhaps you need to prepare the query in some way after assigning the sql and before opening it? No zeos user at all so this may be an erroneous suggestion....

[taazz]

You have picked my interest can you provide a sample application that reproduces the problem? If yes I'll certainly give it a good look. Sorry but I can't think of anything else you can check.

[Leledumbo]

@BigChimp: Right, the cursor seems to be the bottleneck then. I have no idea since .Next should only moves the cursor forward, the data is already there, not fetched anymore AFAIK.

[JD]

@BigChimp: Right, the cursor seems to be the bottleneck then. I have no idea since .Next should only moves the cursor forward, the data is already there, not fetched anymore AFAIK.

That's what I'm thinking. The cursor!!! I'm bouncing a few ideas in my head so I'll see what results I get. Maybe using SQLdb or a TZStoredProc instead perhaps??

[JD]

You have picked my interest can you provide a sample application that reproduces the problem? If yes I'll certainly give it a good look. Sorry but I can't think of anything else you can check.

Thanks for your offer & interest. That however will be difficult since it is a client server application using Indy 10 transport layer and the stored procedure that feeds the query gets its data from about 10 tables and 6 stored procedures linked by indexes & foreign keys. Running the parent stored procedure on its own is blazing fast, so that's not where the problem is. Thanks again.

[JD]

For those that are interested, the complete code segment is shown below. Is is part of a much larger CASE statement.

Code: [Select]

        cmdGetBeneficiaryVisits:
          begin
            // Set the stored procedure name
            strStoredProcName := 'VISITES';
            // The beginning and end dates in SQL date format
            strStartDate := SQLDate(StrToDate(LProtocol.BeginDate));
            strEndDate := SQLDate(StrToDate(LProtocol.EndDate));
            //
            // Create the collection
            LBeneficiaryVisits := TVisites.Create;

            //
            try
              try
                //
                with qryRead do
                begin
                  //
                  SQL.Clear;
                  SQL.Add('select * from ' + strStoredProcName + '(' + QuotedStr(strStartDate) + ',' + QuotedStr(strEndDate) + ')');
                  // Open the dataset
                  Open;
                  //

                  while not EOF do
                  begin
                    //
                    with LBeneficiaryVisits.Add do
                    begin
                      Prenom := Fields.Fields[0].AsString;
                      Sexe := Fields.Fields[1].AsString;
                      // Skip null date fields
                      if not Fields.Fields[2].IsNull then
                        DateNaissance := Fields.Fields[2].AsDateTime;
                      SigneDistinctif := Fields.Fields[3].AsString;
                      Pays := Fields.Fields[4].AsString;
                      Mediateur := Fields.Fields[5].AsString;
                      SituationFamiliale := Fields.Fields[6].AsString;
                      Dependants := Fields.Fields[7].AsInteger;
                      EnfantsScolarises := Fields.Fields[8].AsInteger;
                      EnfantsNonScolarises := Fields.Fields[9].AsInteger;
                      EntreeEnFrance := Fields.Fields[10].AsInteger;
                      VilleCodePostale := Fields.Fields[11].AsString;
                      SituationAdministrative := Fields.Fields[12].AsString;
                      AutorisationTravailOuiNon := Fields.Fields[13].AsString;
                      Logement := Fields.Fields[14].AsString;
                      LienAvecBondy := Fields.Fields[15].Value;
                      CouvertureMedicale := Fields.Fields[16].AsString;
                      NomMedecinTraitant := Fields.Fields[17].AsString;
                      Pathologie := Fields.Fields[18].AsString;
                      PathologieSousTraitementOuiNon := Fields.Fields[19].AsString;
                      SuiviPar := Fields.Fields[20].AsString;
                      SuiviSociale := Fields.Fields[21].AsString;
                      // Skip null date fields
                      if not Fields.Fields[22].IsNull then
                        PremiereVisite := Fields.Fields[22].AsDateTime;
                      MotifPremiereVisite := Fields.Fields[23].AsString;
                      OrientePar := Fields.Fields[24].AsString;
                      Partenaire := Fields.Fields[25].AsString;
                      Ressources := Fields.Fields[26].AsString;
                      Charges := Fields.Fields[27].AsString;
                      TotaleEntretiens := Fields.Fields[28].AsInteger;
                      NombreRepas := Fields.Fields[29].AsInteger;
                      PresentSansMange := Fields.Fields[30].AsInteger;
                      TotaleFrequentation := Fields.Fields[31].AsInteger;
                      //
                    end;
                    Next;
                  end;
                  // close the dataset
                  Close;
                end;
                // copy the collection to a memory stream in preparation for sending to clients
                SaveCollectionToStream(LBeneficiaryVisits, LStream);
              except
                // close the open dataset
                // an open dataset may be the reason why errors hang the server & force a server restart
                if qryRead.Active then
                  qryRead.Close;
              end;
            finally
              // Release the objects created earlier
              FreeAndNil(LBeneficiaryVisits);
            end;
          end;


[ttomas]

Can you execute
select count(*) from  + strStoredProcName +
you can do in any firebird tool
and post your time here

[taazz]

Actually since you have effectively ruled out any reason for the code to be the bottleneck, the only think you can supply is a database with bogus data and a simple application with the code you posted here in a buttonclick event for as to take a look.

[Mike.Cornflake]

To help identify the problem, you could replace your main block with...

Code: [Select]

LBeneficiaryVisits := TVisites.Create;

//
try
  try
    //
    with qryRead do
    begin
      //
      //SQL.Clear;
      //SQL.Add('select * from ' + strStoredProcName + '(' + QuotedStr(strStartDate) + ',' + QuotedStr(strEndDate) + ')');
      // Open the dataset
      //Open;
      //

      For i := 0 to 10000 do // Or whatever you think the record count is...
      //while not EOF do
      begin
        //
        with LBeneficiaryVisits.Add do
        begin
          Prenom := 'Hello World'';
          Sexe := 'Hello World';
          // Skip null date fields
          //if not Fields.Fields[2].IsNull then
            DateNaissance := Now;
          SigneDistinctif := 'Hello World';
          Pays := 'Hello World';
          Mediateur := 'Hello World';
          SituationFamiliale := 'Hello World';
          Dependants := Random(1000);
          EnfantsScolarises := Random(1000);
          EnfantsNonScolarises := Random(1000);
          EntreeEnFrance := Random(1000);
          VilleCodePostale := 'Hello World';
          SituationAdministrative := 'Hello World';
          AutorisationTravailOuiNon := 'Hello World';
          Logement := 'Hello World';
          LienAvecBondy := Random(1000);
          CouvertureMedicale := 'Hello World';
          NomMedecinTraitant := 'Hello World';
          Pathologie := 'Hello World';
          PathologieSousTraitementOuiNon := 'Hello World';
          SuiviPar := 'Hello World';
          SuiviSociale := 'Hello World';
          // Skip null date fields
          //if not Fields.Fields[22].IsNull then
            PremiereVisite := Now;
          MotifPremiereVisite := 'Hello World';
          OrientePar := 'Hello World';
          Partenaire := 'Hello World';
          Ressources := 'Hello World';
          Charges := 'Hello World';
          TotaleEntretiens := 'Hello World';
          NombreRepas := Random(1000);
          PresentSansMange := Random(1000);
          TotaleFrequentation := Random(1000);
          //
        end;
        //Next;
      end;
      // close the dataset
      //Close;
    end;
    // copy the collection to a memory stream in preparation for sending to clients
    SaveCollectionToStream(LBeneficiaryVisits, LStream);
  except
    // close the open dataset
    // an open dataset may be the reason why errors hang the server & force a server restart
    //if qryRead.Active then
     // qryRead.Close;
  end;
finally
  // Release the objects created earlier
  FreeAndNil(LBeneficiaryVisits);
end;
Sorry if I'm stating the obvious, but:

If it still takes 5 minutes, nothing to do with the cursor; it'll be populating the collection, or streaming it.
  - next step, comment out the streaming.  If it still takes 5 minutes, it's the collection population.
                                                                    If it's now fast, it's the streaming...

If this whole code block is fast, then indeed it is the database cursor somehow.  Can't see how myself, but we live in a wierd old world :-)

Good luck...

UPDATE:   Investigated a little further.  Crumbs, this was a shock...  Don't know if it's your problem, but you're actually doing a whole lot of looping you don't know about...
In DB.pas...

Code: [Select]

function TFields.FieldByNumber(FieldNo : Integer) : TField;

Var i : Longint;

begin
  Result:=Nil;
  For I:=0 to FFieldList.Count-1 do
    If FieldNo=TField(FFieldList[I]).FieldNo then
      begin
      Result:=TField(FFieldList[i]);
      Exit;
      end;
end;
To eliminate this

Code: [Select]

Var
  FieldDateNaissance : TField;

then before you start your while loop...

  FieldDateNaissance := Fields.Fields[2];

then within your while loop do the following instead...

  DateNaissance := FieldDateNaissance.AsDateTime;

That'll actually get rid of a fair amount of overhead.  5 minutes worth?  Doesn't feel right, but you never know...   And I still recommend my first approach to eliminate the collections...

[JD]

Thanks for ALL your suggestions. I'll try them & I'll report my findings.