Copy dataset to another object (very, very, very slow) lazarus / 16-Databases ---------------------------------------------------------------------- JD · 2013-12-27 15:59 Hi there everyone, I have a query (I'm using Zeos components) that is run at the end of the year that has serious performance issues. The query (actually a stored procedure) is run on the server & the results saved to a collection that is sent to remote clients to be displayed on the client PCs in a grid which can then be exported to a spreadsheet for further analysis. On average the whole process takes about 5 minutes & 30 seconds to complete and I want to speed it up. The query returns about 1000 rows & 35 fields; 5 of these fields are complex calculated fields. I tried timing the process & I discovered that the query results are ready in about 11 seconds but copying to the collection takes an additional 5 minutes on average !!!  I have already tried the following methods with unsatisfactory results a) Using FieldByName to copy to collection (a little over 6 minutes delay) b) Using Fields.Field[0].AsXXX to copy to collection (5 minutes 20 secs delay) c) Copying the query to a stringlist instead of a collection (a little over 5 minutes delay) d) Copy the query to an Excel file directly (5 minutes delay) The section of the code is shown below Code: [Select]    with qryRead do     begin       SQL.Clear;       SQL.Add('select * from ' + strStoredProcName + '(' + QuotedStr(strStartDate) + ',' + QuotedStr(strEndDate) + ')');       // Open the dataset       Open;                    // Takes about 11 secs to complete       while not EOF do         // Takes about 5 minutes 30 seconds to complete !!!!!       begin         // Fill the LVisits collection from the query results         with LVisits.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;                                            ........ ........ ........           Total := Fields.Fields[25].AsInteger;           //         end;         Next;       end;       // close the dataset       Close;     end; Any ideas how I can solve this problem?? JD Lazarus 1.2RC1/FPC 2.6.2 on Vista ---------------------------------------------------------------------- Leledumbo · 2013-12-27 16:24 What's the type of LVisits? Is it a visual control or directly connected to a visual control (e.g.: when it changes, the visual control refreshes itself automatically)? Anyway, building 1000 complex (35 fields) objects does require time, especially since you're copying from a dataset. Each row in a dataset itself is already a created object. Field access by name is slower than with index because name searching can't be done in O(1). ---------------------------------------------------------------------- JD · 2013-12-27 16:35 LVisites is a TCollection. I know speed access by name is slower than using an index but is there anything I can do to optimize the process? JD ---------------------------------------------------------------------- Leledumbo · 2013-12-27 16:50 Don't copy to a collection, use the result directly. ---------------------------------------------------------------------- JD · 2013-12-27 18:24 Quote from: Leledumbo on December 27, 2013, 05:50:13 pmDon't copy to a collection, use the result directly. I have to send the result over TCP/IP to client machines. I do this by saving the result to a collection & then saving the collection to a stream. I then send the stream to the client PCs where it becomes a collection again. Zeos does not have the ability to save the dataset directly to a stream otherwise I would have used that method instead. I now realize that SQLdb does but I haven't tried copying the results from one dataset to another before sending the stream to clients. JD ---------------------------------------------------------------------- marcov · 2013-12-27 20:23 Maybe the stored procedure is the problem? Add indexes, avoid temp tables, and check when your transactions start and stop. ---------------------------------------------------------------------- kpeters58 · 2013-12-28 04:52 You have not told us about the nature of these 35 fields - are there any blobs involved? Or else what is the reason for using streams? What is the average record size of these one thousand records? Without that knowledge no one will be able to tell if the results are good, bad or average. If they were all strings (say less than 200 chars each or such) or numeric fields then clearly you should be able to be done in 30 seconds or less, given contemporary hardware and a decent connection - if there were to be tons of binary data then maybe 5 minutes isn't that horrible at all... ---------------------------------------------------------------------- JD · 2013-12-28 08:50 Quote from: marcov on December 27, 2013, 09:23:20 pmMaybe the stored procedure is the problem? Add indexes, avoid temp tables, and check when your transactions start and stop. By the way, it is a Firebird database. I don't use temp tables. I've tested the stored procedure on the database server and I've run it on the application server. This is the fastest part of the whole operation - 12 seconds or less. The delay comes when the pascal program has to copy the query results into the collection. Once the collection is filled, top speed resumes  and sending the results to the clients is done in seconds. ---------------------------------------------------------------------- JD · 2013-12-28 09:05 Quote from: kpeters58 on December 28, 2013, 05:52:22 amYou have not told us about the nature of these 35 fields - are there any blobs involved? Or else what is the reason for using streams? What is the average record size of these one thousand records? Without that knowledge no one will be able to tell if the results are good, bad or average. If they were all strings (say less than 200 chars each or such) or numeric fields then clearly you should be able to be done in 30 seconds or less, given contemporary hardware and a decent connection - if there were to be tons of binary data then maybe 5 minutes isn't that horrible at all... The dataset does not contain blobs. The TCollection class is as shown below. The class is a direct mapping of the dataset i.e the type of the fields in both cases are identical. The last three integer fields are calculated fields (this is done server side by the stored procedure). The LVisites object that is populated from the qryRead dataset is an instance of TVisitesAssociations i.e LVisites := TVisitesAssociations.Create Pardon the French words in the code below  Code: [Select]  { TBaseItemClass }   TBaseItemClass = class(TCollectionItem)   private     //function GetPropertyCount: integer; virtual; abstract;     //function GetPropertyString(AIndex: integer): string; virtual; abstract;   public     function GetPropertyCount: integer; virtual; abstract;     function GetPropertyString(AIndex: integer): string; virtual; abstract;     function GetPropertyName(AIndex: integer): string; virtual; abstract;     function GetPropertyIndex(AName: string): integer; virtual; abstract;     property PropertyCount: integer read GetPropertyCount;     property PropertyString[AIndex: integer]: string read GetPropertyString;     property PropertyName[AIndex: integer]: string read GetPropertyName;     property PropertyIndex[AName: string]: integer read GetPropertyIndex;   end;   { TVisitesAssociation }   TVisitesAssociation = class(TBaseItemClass)   private     { private declarations }     FPrenom: string;     FSexe: string;     FDateNaissance: TDateTime;     FSigneDistinctif: string;     FPays: string;     FMediateur: string;     FSituationFamiliale: string;     FDependants: integer;     FEnfantsScolarises: integer;     FEnfantsNonScolarises: integer;     FEntreeEnFrance: integer;     FVilleCodePostale: string;     FSituationAdministrative: string;     FAutorisationTravailOuiNon: string;     FLogement: string;     FLienAvecBondy: string;     FCouvertureMedicale: string;     FNomMedecinTraitant: string;     FPathologie: string;     FPathologieSousTraitementOuiNon: string;     FSuiviPar: string;     FSuiviSociale: string;     FPremiereVisite: TDateTime;     FMotifPremiereVisite: string;     FOrientePar: string;     FPartenaire: string;     FRessources: string;     FCharges: string;     FNombreRepas: integer;     FPresentSansMange: integer;     FTotaleFrequentation: integer;     FTotaleEntretiens: integer;   public     { public declarations }     function GetPropertyCount: integer; override;     function GetPropertyString(AIndex: integer): string; override;   published     { published declarations }     property Prenom: string read FPrenom write FPrenom;     property Sexe: string read FSexe write FSexe;     property DateNaissance: TDateTime read FDateNaissance write FDateNaissance;     property SigneDistinctif: string read FSigneDistinctif write FSigneDistinctif;     property Pays: string read FPays write FPays;     property Mediateur: string read FMediateur write FMediateur;     property SituationFamiliale: string read FSituationFamiliale write FSituationFamiliale;     property Dependants: integer read FDependants write FDependants;     property EnfantsScolarises: integer read FEnfantsScolarises write FEnfantsScolarises;     property EnfantsNonScolarises: integer read FEnfantsNonScolarises write FEnfantsNonScolarises;     property EntreeEnFrance: integer read FEntreeEnFrance write FEntreeEnFrance;     property VilleCodePostale: string read FVilleCodePostale write FVilleCodePostale;     property SituationAdministrative: string read FSituationAdministrative write FSituationAdministrative;     property AutorisationTravailOuiNon: string read FAutorisationTravailOuiNon write FAutorisationTravailOuiNon;     property Logement: string read FLogement write FLogement;     property LienAvecBondy: string read FLienAvecBondy write FLienAvecBondy;     property CouvertureMedicale: string read FCouvertureMedicale write FCouvertureMedicale;     property NomMedecinTraitant: string read FNomMedecinTraitant write FNomMedecinTraitant;     property Pathologie: string read FPathologie write FPathologie;     property PathologieSousTraitementOuiNon: string read FPathologieSousTraitementOuiNon write FPathologieSousTraitementOuiNon;     property SuiviPar: string read FSuiviPar write FSuiviPar;     property SuiviSociale: string read FSuiviSociale write FSuiviSociale;     property PremiereVisite: TDateTime read FPremiereVisite write FPremiereVisite;     property MotifPremiereVisite: string read FMotifPremiereVisite write FMotifPremiereVisite;     property OrientePar: string read FOrientePar write FOrientePar;     property Partenaire: string read FPartenaire write FPartenaire;     property Ressources: string read FRessources write FRessources;     property Charges: string read FCharges write FCharges;     property NombreRepas: integer read FNombreRepas write FNombreRepas;     property PresentSansMange: integer read FPresentSansMange write FPresentSansMange ;     property TotaleFrequentation: integer read FTotaleFrequentation write FTotaleFrequentation;     property TotaleEntretiens: integer read FTotaleEntretiens write FTotaleEntretiens;   end;   { TVisitesAssociations }   TVisitesAssociations = class(TCollection)   private     { private declarations }     function GetItem(AIndex: integer): TVisitesAssociation;   public     { public declarations }     constructor Create;     function Add: TVisitesAssociation;     property Items[AIndex: integer]: TVisitesAssociation read GetItem; default;   end; This same collection is used client side to fill a grid (TStringGrid or TKGrid). Streams are used to transfer the populated collection's data from server to clients over LAN, WAN or the Internet for further analysis in Excel pivot tables. The grid can be exported to an Excel file ---------------------------------------------------------------------- ttomas · 2013-12-28 09:53 Can you compare time for Code: [Select]      Open;                    // Takes about 11 secs to complete       while not EOF do         // Takes about X minutes XX seconds to complete !!!!!       begin         Next;       end; Opening the query can be fast but fetch all records can cost if your sql query is not optimized. ---------------------------------------------------------------------- tatamata · 2013-12-28 10:00 Maybe a stupid question, but did you consider exporting dataset into a .csv file? ---------------------------------------------------------------------- JD · 2013-12-28 12:31 Quote from: ttomas on December 28, 2013, 10:53:48 amCan you compare time for Code: [Select]      Open;                    // Takes about 11 secs to complete       while not EOF do         // Takes about X minutes XX seconds to complete !!!!!       begin         Next;       end; Opening the query can be fast but fetch all records can cost if your sql query is not optimized. Done. It took about 6 minutes & 10 seconds to transverse the dataset from the first record to the last record. Very odd, since no copying was being done!! Why would the query need optimization when the stored procedure that populates the query does the job in about 8 seconds? ---------------------------------------------------------------------- JD · 2013-12-28 12:32 Quote from: tatamata on December 28, 2013, 11:00:27 amMaybe a stupid question, but did you consider exporting dataset into a .csv file? Unsatisfactory. It took 6 minutes and 20 seconds!! ---------------------------------------------------------------------- taazz · 2013-12-28 12:49 how about Code: [Select]    DisableControls     try       while not EOF do         // Takes about X minutes XX seconds to complete !!!!!       begin         Next;       end;     finally       EnableControls;     end; ---------------------------------------------------------------------- JD · 2013-12-28 13:23 Quote from: taazz on December 28, 2013, 01:49:15 pmhow about Code: [Select]    DisableControls     try       while not EOF do         // Takes about X minutes XX seconds to complete !!!!!       begin         Next;       end;     finally       EnableControls;     end; Not necessary because no visual controls are being updated on the server. ---------------------------------------------------------------------- taazz · 2013-12-28 13:32 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 · 2013-12-28 14:27 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 · 2013-12-28 14:32 Quote from: taazz on December 28, 2013, 02:32:21 pmif 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 · 2013-12-28 15:02 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 · 2013-12-28 15:06 @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.msg13714 2.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 · 2013-12-28 15:10 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 · 2013-12-28 15:57 @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 · 2013-12-28 16:14 Quote from: Leledumbo on December 28, 2013, 04:57:48 pm@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 · 2013-12-28 16:22 Quote from: taazz on December 28, 2013, 04:10:24 pmYou 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 · 2013-12-28 16:35 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 · 2013-12-28 21:31 Can you execute select count(*) from  + strStoredProcName + you can do in any firebird tool and post your time here ---------------------------------------------------------------------- taazz · 2013-12-28 22:08 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 · 2013-12-29 00:42 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 · 2013-12-29 15:50 Thanks for ALL your suggestions. I'll try them & I'll report my findings.