What is a better way to create an access database of file names using C++ Builder?

Question

Final question for the night. And apologies for the complete noobness of this.

I have, with help from stackoverflow and google achieved the following...

void __fastcall TForm1::Button1Click(TObject *Sender)
{
 ADOCommand1->CommandText = "drop table pictures purge";
 ADOCommand1->Execute();
 ADOCommand1->CommandText = "create table pictures(id autoincrement, filename TEXT(255), notes text(255), category text(20), rank int, filedate datetime,constraint table1_PK primary key(id));";
 ADOCommand1->Execute();
 AddFiles(Edit1->Text);
}
//---------------------------------------------------------------------------

int AddFiles(AnsiString path, int count)
{
 TSearchRec sr;
 int f,count2=0;
 f = FindFirst(path+"\\*.*", faAnyFile, sr);
 while( !f )
 {
  if(sr.Attr & faDirectory)
  {
   if(sr.Name != "."   &&   sr.Name != "..")
   {
    AnsiString subpath;
    subpath.sprintf("%s%s%s", path, "\\", sr.Name);
    count = AddFiles(subpath,count);
   }
  }
  else
  {
   Form1->ADOCommand1->CommandText = "Insert into pictures (filename) values ('" + StringReplace(path + "\\" + sr.Name, "'", "''", TReplaceFlags()<<rfReplaceAll) + "')";
   Form1->ADOCommand1->Execute();
   ++count;
   ++count2;
   if (count2 > 100)
   {
    count2 = 0;
    Form1->Caption = "Added " + IntToStr(count)+ " Files.";
    Application->ProcessMessages();
   }

  }
  f = FindNext(sr);

 }
 FindClose(sr);
return count;
}

It is basically traversing a given directory and its subdirs, and adding each file (with path) to an MS Access database.

(I am the only user so I don't care much about security)

With a large number of files this code is horribly slow. Can anyone describe a better way to do what the code is doing, (hopefully without making your answer too complicated. I'm a noob to this remember)

Answer 1

The bottleneck in your code is mostly likely the ADO/Access database layer. The directory-level functions (FindFirst(), FindNext()) are relatively fast. You could verify whether the database is the bottleneck by replacing those calls with cout statements. My guess is that it will run much faster when outputting the results to the console.

In my experience, Access is not a high-performance database, and the ADO drivers are not as fast as native drivers. I'll bet if you replaced the database with a more serious database, you'd find improved performance.

Answer 2

First - there is no need drop/create the table. Create it once and then call "delete from pictures", when you want to reset it. Second - comment out "Form1->ADOCommand1->Execute();" and see how much faster it gets. This shows if the problem is in saving into database .

If you plan to develop some serious stuff in the future, then I recommend to look a round for better database solutions. (Firebird embedded, MySQL embedded, etc.).

Answer 3

For MySQL there are CBuilder components - very good to use.