🚀 go-pugleaf

Maybe I should delete every processed record? That
will definitely slow down the process but  I expect that
progress will be paced and predictable.


-- 
Best regards,
Vladimir Stefanovic
"Vladimir Stefanovic" <antivari@po.sbb.co.yu> wrote in message 
news:4214e840@newsgroups.borland.com...
> 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
>