Hi Samuel,

the german .NET magazine "dotnetpro" invited .NET programmers to show that their database is the fastest. Deadline is 07/07/2007, about two months from now. Wouldn't it be a great chance to show the VFPCompiler for .NET's capabilities to the world and win the contest? ;-)

The task at hand is quite simple:

1. Import half a million records from a CSV text file.
2. Remove duplicates and write the records to a new table
with different data structure.
3. Export the new table to a new CSV text file.

Be aware that the required CSV structure is somewhat different from VFP's CSV structure.

I wrote a sample program in VFP that does what's required (see below). The commands I used are not the most obvious ones but remember, I tried to make it run as fast as possible.

I know that my sample program uses commands that are not yet supported by the VFPCompiler for .NET. On the other hand it could be run as a DBC's stored procedure using the vfp9t.dll data layer (if there was a way to call a DBC's stored procedure). Or it could use completely different commands that are easier to implement.

Best regards,

Markus Winhard

*** DotNetProContest.prg ***
*--------------------------------------------
* Constants.
*--------------------------------------------
#DEFINE _TIMING_ .T.
#DEFINE _FOLDER_ "C:\Daten\"

*--------------------------------------------
* Setup.
*--------------------------------------------
#IF _TIMING_
  CLEAR ALL
  LOCAL lnSec0, lnSec1
  lnSec0 = SECONDS()
#ENDIF
SET COLLATE TO "MACHINE"
SET TALK OFF
SET NOTIFY OFF
SET NOTIFY CURSOR OFF
LOCAL lnFHandle, lcName, lcStrasse, lcOrt, lnAt

*--------------------------------------------
* Create test data.
* Tested with 500,000 records like these in
* Vfp5/6/7/8/9 and done after 21 seconds. :-)
*--------------------------------------------
IF NOT FILE( _FOLDER_ + "Adressen.txt" )
  lnFHandle = FCREATE( _FOLDER_ + "Adressen.txt", 0 )
  *ASSERT NOT m.lnFHandle == -1
  lnResult = FPUTS( m.lnFHandle, ;
    "Michaela, Übbers, Weinstraße, 17, 04123, Leipzig, michaela@netlan.de" )
  FPUTS( m.lnFHandle, ;
    "Michaela, Saacher, Nesselgasse, 6, 53888, Bonn, ms@usaforever.com" )
  FPUTS( m.lnFHandle, ;
    "Dominik, Richard, Am Sportplatz, 9, 56812, Cochem, dr@webfantastic.de" )
  FPUTS( m.lnFHandle, ;
    "Michaela, Saacher, Nesselgasse, 6, 53888, Bonn, ms@usaforever.com" )
  FPUTS( m.lnFHandle, ;
    "Dominik, Richard, Am Sportplatz, 9, 56812, Cochem, dr@webfantastic.de" )
  FPUTS( m.lnFHandle, ;
    "Volker, Richard, Zum Dorfe, 19, 04123, Leipzig, volker@meinserver.de" )
  FPUTS( m.lnFHandle, ;
    "Claudia, Fischer, Brandtstraße, 12, 90555, Nürnberg, cf@gmx.de" )
  FPUTS( m.lnFHandle, ;
    "Volker, Richard, Zum Dorfe, 19, 04123, Leipzig, volker@meinserver.de" )
  FCLOSE( m.lnFHandle )
ENDIF

ERASE _FOLDER_ + "Table*.*"
CREATE TABLE _FOLDER_ + "Table1" FREE ( ;
  Vorname C(30), ;
  Name C(30), ;
  Strasse C(30), ;
  HausNr C(4), ;
  PLZ C(6), ;
  Ort C(30), ;
  eMail C(30) )

APPEND FROM _FOLDER_ + "Adressen.txt" DELIMITED
REPLACE ALL ;
  Vorname WITH LTRIM( Vorname ), ;
  Name WITH LTRIM( Name ), ;
  Strasse WITH LTRIM( Strasse ), ;
  HausNr WITH LTRIM( HausNr ), ;
  PLZ WITH LTRIM( PLZ ), ;
  Ort WITH LTRIM( Ort ), ;
  eMail WITH LTRIM( eMail )

INDEX ON Vorname + Name + Strasse + HausNr + PLZ + Ort + eMail TAG _unique UNIQUE
COPY TO _FOLDER_ + "Table1a"
USE IN Table1
ERASE Table1.cdx

SELECT RTRIM( Vorname ) +" "+ Name AS Name, ;
  RTRIM( Strasse ) +" "+ HausNr AS Strasse, ;
  PLZ + Ort AS Ort, ;
  eMail ;
  FROM _FOLDER_ + "Table1a" ;
  INTO TABLE _FOLDER_ + "Table2"
USE IN Table1a

ERASE _FOLDER_ + "Adressen2.*"
lnFHandle = FCREATE( _FOLDER_ + "Adressen2.txt", 0 )
*ASSERT NOT m.lnFHandle == -1
SELECT Table2
SCAN
  lcName = RTRIM( Name )
  lnAt = RAT( " ", m.lcName )
  lcName = STUFF( m.lcName, m.lnAt, 1, ", " )
  *
  lcStrasse = RTRIM( Strasse )
  lnAt = RAT( " ", m.lcStrasse )
  lcStrasse = STUFF( m.lcStrasse, m.lnAt, 1, ", " )
  *
  lcOrt = RTRIM( Ort )
  lnAt = AT( " ", m.lcOrt )
  lcOrt = STUFF( m.lcOrt, m.lnAt, 1, ", " )
  *
  FPUTS( m.lnFHandle, ;
    m.lcName +", "+ m.lcStrasse +", "+ m.lcOrt +", "+ RTRIM( eMail ) )
ENDSCAN
FCLOSE( m.lnFHandle )

#IF _TIMING_
  lnSec1 = SECONDS()
  ACTIVATE SCREEN
  ? m.lnSec1 - m.lnSec0
#ENDIF