Hi Markus,
>I checked your suggestion and indeed having less variable assignments nearly cuts the CSV export time in half. I just left out STRTRAN() because the original STUFF() is a bit faster (only 0.03 seconds less at 500,000 records, but anyway ;-)).
You did not mention if the data found in the example allows switching over to checking for the first blank.
If the stuff/at combo is faster than strtran(), depending on the possible values of vorname this should
give you another few millisecs.
SELECT Table2
SCAN
lcStrasse = RTRIM( Strasse )
FPUTS( m.lnFHandle, ;
STUFF( RTrim( Name ), AT( " ", m.lcName ), 1, ", " ) +", "+ ;
STUFF( m.lcStrasse, RAT( " ", m.lcStrasse ), 1, ", " ) +", "+ ;
STUFF( RTRIM( Ort ), AT( " ", Ort ), 1, ", " ) +", "+ ;
RTRIM( eMail ) )
ENDSCAN
>OTOH this is only about 0.1 seconds faster than my original version but way worse to read. Part two of the contest (remove duplicates and create a table with a different structure) is still more than 17 seconds. The commands I used are INDEX ON...UNIQUE, COPY TO and SELECT...INTO TABLE.
Since you already used the index type unique, which would have been my first pick for a hidden joker, large gains may be difficult<g>. But for another small speedup skip indexing to cdx and instead use an old compressed idx. Small is beautiful and so on <bg>.
One thing to try out is to rearrange the fields used to create the unique index putting Hausnummer and PLZ first - greatest selectivity in the fewest bytes. Since it disturbs consecutive memory access there are negative effects as well - but should be checked.
The question is IMHO to find a way to skip writing table1a while still utilizing the unique index.
regards
thomas