Speeding up TDbf lazarus / 16-Databases ---------------------------------------------------------------------- SymbolicFrank · 2021-12-15 11:12 Copying a large table from a TDbf to an SQlite table takes 15 minutes. Duplicating that same table in SQlite takes 1 second. I do the DBF to SQL by copying the fields of each record from the first TDataSet to the second, like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DestTable.FieldByName(s).Value := SourceTable.FieldByName(s).Value; (In transactions of 1000 records.) Simply copying by index, like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DestTable.Fields[i].Value := SourceTable.Fields[i].Value; doesn't work, the component complains that there is no valid insert query and it cannot be created because there are no fields defined. Anyway, how can I speed this up? It is 1000 times slower than pure SQL. ---------------------------------------------------------------------- fpcoder · 2021-12-15 11:17 Sounds almost like commit is used for each record? ---------------------------------------------------------------------- SymbolicFrank · 2021-12-15 11:29 No, I do it in batches of 1000 records. With autocommit it takes twice as long, so 2000 times slower than pure SQL. ---------------------------------------------------------------------- SymbolicFrank · 2021-12-15 13:36 I tried first copying it to a TBufDataSet, with CopyFromDataset. But even while it's only 240 MB, I ran out of memory (16 GB)... ---------------------------------------------------------------------- Sieben · 2021-12-15 14:47 Any chance to get the data in csv format and use SQLite's .import facility (cf here)? ---------------------------------------------------------------------- SymbolicFrank · 2021-12-15 14:57 Quote from: Sieben on December 15, 2021, 03:47:25 pmAny chance to get the data in csv format and use SQLite's .import facility (cf here)? The main thing is export, so if it takes too long, I'll probably do that. ---------------------------------------------------------------------- wp · 2021-12-15 16:18 Quote from: SymbolicFrank on December 15, 2021, 12:12:53 pmCopying a large table from a TDbf to an SQlite table takes 15 minutes. Duplicating that same table in SQlite takes 1 second. It's hard to believe that it takes so long, even the 1 second for SQLite is too long. Are the source and destination tables attached to any visual controls? In such a case, the heavy repainting would explain the low speed. Disconnect visual controls from the tables during the export or put the export code in to a block with a Dataset.DisableControls/.EnableControls. ---------------------------------------------------------------------- SymbolicFrank · 2021-12-17 08:22 Quote from: wp on December 15, 2021, 05:18:20 pmQuote from: SymbolicFrank on December 15, 2021, 12:12:53 pmCopying a large table from a TDbf to an SQlite table takes 15 minutes. Duplicating that same table in SQlite takes 1 second. It's hard to believe that it takes so long, even the 1 second for SQLite is too long. Are the source and destination tables attached to any visual controls? In such a case, the heavy repainting would explain the low speed. Disconnect visual controls from the tables during the export or put the export code in to a block with a Dataset.DisableControls/.EnableControls. No, there is no visual control. I tried: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ThisQuery.CopyFromDataset(SourceTable); But that doesn't work, after adding the fields to the FieldDefs, the TCustomBufDataset.CreateDataset has no FieldDefs. Very strange. Anyway, it's a bit of a bummer. ---------------------------------------------------------------------- SymbolicFrank · 2021-12-17 08:52 Ok, I debugged it, and it breaks here: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TCollection.InsertItem(Item: TCollectionItem);begin  If Not(Item Is FitemClass) then    exit; At that point, it expects this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TCollectionitemclass($0000000100301398) But the Item is still completely empty and is not recognized as inherited from that class. ---------------------------------------------------------------------- SymbolicFrank · 2021-12-17 11:55 Ok, I decided to see if exporting the SQL table to DBF worked better, but that also crashes. After debugging, it seems a boolean "out" parameter is passed by value instead of address. So, weird issues. I'm reinstalling the newest trunk with fpcupeluxe and adding the pending fixes. But that makes 3 bugs in these things, that are seemingly used extensively, two of which are in the compiler. (There was another bug in TDbf, where a nil pointer was referenced during freeing, I'll have to fix that one again.) Let's hope my install was buggy. If they still persist, I'll fix them and add them to the bug tracker. ---------------------------------------------------------------------- wp · 2021-12-17 12:21 Quote from: SymbolicFrank on December 15, 2021, 12:12:53 pmCopying a large table from a TDbf to an SQlite table takes 15 minutes. Duplicating that same table in SQlite takes 1 second. I said above that these numbers are too long. Of course, it depends... In order to get a feeling for the real times I wrote a simple console program (see attachment) which creates a dbf file and copies the records to an sqlite3 database. For 100,000 records with three fields (string[20], integer, float), the program requires 11 seconds to create the dbf, and 2 seconds to do the conversion to sqlite3. ---------------------------------------------------------------------- SymbolicFrank · 2021-12-17 15:35 Quote from: wp on December 17, 2021, 01:21:20 pmQuote from: SymbolicFrank on December 15, 2021, 12:12:53 pmCopying a large table from a TDbf to an SQlite table takes 15 minutes. Duplicating that same table in SQlite takes 1 second. I said above that these numbers are too long. Of course, it depends... In order to get a feeling for the real times I wrote a simple console program (see attachment) which creates a dbf file and copies the records to an sqlite3 database. For 100,000 records with three fields (string[20], integer, float), the program requires 11 seconds to create the dbf, and 2 seconds to do the conversion to sqlite3. Thanks! That is certainly a lot faster. I changed it to use the same table I did and to copy all field types: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   function FieldToStr(ThisField: TField): string;  begin    Result := '';     if not Assigned(ThisField) then Exit;     Result := ThisField.FieldName + ' ' + FieldTypeNames[ThisField.DataType];    if ThisField.DataType = ftAutoInc then      Result := Result + ' Primary Key';  end; The second method is about the same as the one I used. I had to insert this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     if i div 1000 * 1000 = i then Transaction.CommitRetaining; to prevent running out of memory. But with an insert query and by index instead of by name is three minutes faster: 12 instead of 15. But parameters are indeed far superior: it only takes ~2 minutes Still quite some time, but only 7% of using datasets with autocommit. And instead of ~8 GB, the memory usage is only 10 MB! So, don't use datasets, but parameterized queries. Good to know. ---------------------------------------------------------------------- wp · 2021-12-17 15:39 What is different in your dataset compared to mine? More records and fields? How many?  Blob fields (memo)? ---------------------------------------------------------------------- af0815 · 2021-12-17 15:54 Is in the target table a autoincrement field ? If yes avoid this if you need speed. Better is to use a GUID or a Hash or a computed value. To manage autoinc, the db system must store the actual value somewere for every row. ---------------------------------------------------------------------- SymbolicFrank · 2021-12-17 16:26 This application is for importing all kinds of other databases into large Visual FoxPro databases (almost 900 files, DBF, FPT, CDX, one database for each customer). First, I import a (most often almost empty) subset of the database to SQL, then I read the source material (which can be almost anything) and insert it into the right fields/tables. And afterwards, I export them from SQL back to DBF. The table I used is 240.000 records, with 116 fields (2 blobs) each. 260 MB in total. Because an import can be from a few MB, up to multiple GB. The target database already exists and is not going to change (much). But I wanted SQL for the conversions, simply because with DBF files you can only make a selection on a single file by applying a filter. Which is quite slow: filtering a 30 MB TDbf table takes ~1 sec. And while XBase++ (a modern Clipper) is great for working with DBF files, everything else you have to write yourself. ---------------------------------------------------------------------- SymbolicFrank · 2021-12-20 11:08 One of the bugs was a floating point overflow in the latest version of SQlite. I reverted to an earlier version and now it works. TDbf.CopyFrom does work, as long as I have enough RAM and it's as slow as importing with DataSets, so I'll have to use a query there as well. Edit: it doesn't.