The best solution to process the huge text file data in Delphi 7

Question

I have text file like this:

"01","AAA","AAAAA" 
"02","BBB","BBBBB","BBBBBBBB" 
"03","CCC" 
"04","DDD","DDDDD"

I want to load this text file data into temp table in sybase db. So, I need to build a program to read line by line this text file until eof. If the text file size is small, the process to read line by line is fast. But if text file size is too big (can be more than 500M), the process read line by line is too slow. I think the read line by line method not suitable for huge text file. So, need to find other solution to load text file data into db instead of read text file line by line method. Any suggestion? Example code:

var
  myFile : TextFile;
  text   : string;

begin
  // Try to open the Test.txt file for writing to
  AssignFile(myFile, 'Test.txt');

  // Display the file contents
  while not Eof(myFile) do
  begin
    ReadLn(myFile, text);
    TempTable.append;
    TempTable.FieldByName('Field1').asstring=Copy(text,2,2);
    TempTable.FieldByName('Field2').asstring=Copy(text,7,3);
    TempTable.FieldByName('Field3').asstring=Copy(text,13,5);
    TempTable.FieldByName('Field4').asstring=Copy(text,21,8);
    TempTable.post;
  end;

  // Close the file for the last time
  CloseFile(myFile);
end;

Solution 1

Some general tips:

• Ensure your TempTable is in memory, or use a fast database engine - take a look at SQlite3 or other means (like FireBird embedded, NexusDB or ElevateDB) as possible database alternatives;
• If you do not use a TTable, but a true database, ensure you nest the insert within a Transaction;
• For a true database, check out if you can not use ArrayDML feature, which is much faster for inserting a lot of data as you want in a remote database (like Sybase) - such Array DML is handled for instance with FireDAC AFAIK;
• The FieldByName('...') method is known to be very slow: use locals TField variables instead;
• When using a TextFile, assign a bigger temporary buffer;
• If you are using newer Unicode versions of Delphi (2009+), using TextFile is not the best option.

So your code may be:

var
  myFile : TextFile;
  myFileBuffer: array[word] of byte;
  text   : string;
  Field1, Field2, Field3, Field4: TField;
begin

  // Set Field* local variables for speed within the main loop
  Field1 := TempTable.FieldByName('Field1');
  Field2 := TempTable.FieldByName('Field2');
  Field3 := TempTable.FieldByName('Field3');
  Field4 := TempTable.FieldByName('Field4');

  // Try to open the Test.txt file for writing to
  AssignFile(myFile, 'Test.txt');
  SetTextBuf(myFile, myFileBuffer); // use 64 KB read buffer

  // Display the file contents
  while not Eof(myFile) do
  begin
    ReadLn(myFile, text);
    TempTable.append;
    Field1.asInteger := StrToInt(Copy(text,2,2));
    Field2.asString := Copy(text,7,3);
    Field3.asString := Copy(text,13,5);
    Field4.asString := Copy(text,21,8);
    TempTable.post;
  end;

  // Close the file for the last time
  CloseFile(myFile);
end;

You can achieve very high speed with embedded engines, with almost no size limit, but your storage. See for instance how fast we can add content to a SQLite3 database in our ORM: about 130,000 / 150,000 rows per second in a database file, including all ORM marshalling. I also found out that SQLite3 generates much smaller database files than alternatives. If you want fast retrieval of any field, do not forget to define INDEXes in your database, if possible after the insertion of row data (for better speed). For SQLite3, there is already an ID/RowID integer primary key available, which maps your first data field, I suppose. This ID/RowID integer primary key is already indexed by SQLite3. By the way, our ORM now supports FireDAC / AnyDAC and its advanced Array DML feature.

Solution 2

Text files normally have a very small buffer. Look into using the SetTextBuf function to increase your performance.

var
  myFile : TextFile;
  text   : string;
  myFileBuffer: Array[1..32768] of byte;
begin
// Try to open the Test.txt file for writing to
  AssignFile(myFile, 'Test.txt');
  SetTextBuf(MyFile, myFileBuffer);
  Reset(MyFile);

// Display the file contents
  while not Eof(myFile) do
    begin
      ReadLn(myFile, text);
    end;

// Close the file for the last time
  CloseFile(myFile);
end;