🚀 go-pugleaf

Hi,

I have MS Access database (it's a dictionary), which generally
consists of three tables, which I have shown below. I want to
transform these three tables into single one and I already made
a utility for that. Unfortunately, my routine is too slow for 400.000
rows. Just to mention that I have nothing against the design with
three tables, I just want to make some speed tests.

Can someone help me with some advices of how to accomlish
the transformation.

[TableRelations]
Struct:
   Language1_ID (number)
   Language2_ID (number)

Sample:
   1000 5000
   1000 5001
   1000 5002
   1001 6000
   1002 6001

[TableLanguage1]
Struct:
   Language1_ID (number)
   Language1_Word (Text)
   Language1_Bool (Yes/No)

Sample:
   1000 word1 Yes
   1001 word2 Yes
   1002 word3 Yes

[TableLanguage2]
Struct:
   Language2_ID (number)
   Language2_Word (Text)
   Language2_Bool (Yes/No)

Sample:
   5000 word4 No
   5001 word5 No
   5002 word6 No
   6000 word7 No
   6001 word8 No

Essencially, that means:

Translation: Language1->Language2:
   word1 = word4,word5,word6
   word2 = word7
   word3 = word8

Translation: Language2->Language1:
   word4 = word1
   word5 = word1
   word6 = word1
   word7 = word2
   word8 = word3

I want to make one table to look like this:

[TableOnlyOne]
Struct:
   Word (Text)
   Selected (Yes/No)
   Meaning (Text)

Sample:
    word1 Yes word4;word5;word6
    word2 Yes word7
    word3 Yes word8
    word4 No  word1
    word5 No  word1
    word6 No  word1
    word7 No  word2
    word8 No  word3

This is my routine /I changed some variable names without testing
so some simple anomalies are possible, anyway the routine worked
in the original/:

The problem is that after 50000 records it works 1 row/sec, and I
have 400000. Do you have any advice?

void __fastcall TForm1::Button1Click(TObject *Sender)
{
   int BrLang1, BrLang2;
   AnsiString RecLang1, RecLang2;
   Variant Lang1LookupResults, Lang2LookupResults;

   Variant Lang1_locvalues[2], Lang2_locvalues[2];

   Lang1_locvalues[1] = Variant( 1 );
   Lang2_locvalues[1] = Variant( 0 );

   TLocateOptions SearchOptions;
   SearchOptions.Clear();
   SearchOptions = SearchOptions << loCaseInsensitive;

   // Controls Disabled Here

   ADOTableRelations->First();
   while ( !ADOTableRelations->Eof )
   {
      // Take the Relations ID's
      BrLang1 = ADOTableRelations->FieldValues["ID_Lang1"];
      BrLang2 = ADOTableRelations->FieldValues["ID_Lang2"];

      // Find Language1 word
      Lang1LookupResults = ADOTableLang1->Lookup( "ID_Lang1", BrLang1, 
"Language1_Word" );
      RecLang1 = Lang1LookupResults;

      // Find Language2 word
      Lang2LookupResults = ADOTableLang2->Lookup( "ID_Lang2", BrLang2, 
"Language2_Word" );
      RecLang2 = Lang2LookupResults;

      // Append to new row or add to existing
      Lang1_locvalues[0] = Variant( RecLang1 );
      if ( ADOTableOnlyOne->Locate( "Word;Selected", 
VarArrayOf(Lang1_locvalues, 1), SearchOptions ) == false )
      {
         ADOTableOnlyOne->Insert();
         ADOTableOnlyOne->FieldValues["Word"] = RecLang1;
         ADOTableOnlyOne->FieldValues["Selected"] = 1;
         ADOTableOnlyOne->FieldValues["Meaning"] = RecLang2;
         ADOTableOnlyOne->Post();
       }
      else
      {
         ADOTableOnlyOne->Edit();
         AnsiString Current = ADOTableOnlyOne->FieldValues["Meaning"];
         ADOTableOnlyOne->FieldValues["Meaning"] = Current + ";" + RecLang2;
         ADOTableOnlyOne->Post();
      }

      // Append to new row or add to existing
      Lang2_locvalues[0] = Variant( RecLang2 );
      if ( ADOTableOnlyOne->Locate( "Word;Selected", 
VarArrayOf(Lang2_locvalues, 1), SearchOptions ) == false )
      {
         ADOTableOnlyOne->Insert();
         ADOTableOnlyOne->FieldValues["Word"] = RecLang2;
         ADOTableOnlyOne->FieldValues["Selected"] = 0;
         ADOTableOnlyOne->FieldValues["Meaning"] = RecLang1;
         ADOTableOnlyOne->Post();
      }
      else
      {
         ADOTableOnlyOne->Edit();
         AnsiString Current = ADOTableOnlyOne->FieldValues["Meaning"];
         ADOTableOnlyOne->FieldValues["Meaning"] = Current + ";" + RecLang1;
         ADOTableOnlyOne->Post();
      }

      // Progress bars, etc ...

      ADOTableRelations->Next();
   }

   // Controls Enabled Here
}


-- 
Best regards,
Vladimir Stefanovic