Hi Markus:

Sounds like a nice test. Today we are releasing the TableLayer together with an improved compiler. Reading your code, that indeed is very speedy, the compiler supports most of the required commands the two big missing are APPEND FROM (only APPEND BLANK) and SELECT SQL.

The APPEND FROM, could be the bottleneck, because it requires a very good and smart implementation to convert names, numbers and maybe dates to the table structure. I'm talking about the general case, I see that in your table schema you are using just chars to avoid that step. That is Clever!

Where can I read more about the contest and specially where can I get the data or sample data. I did a quick check and did not find anything, a link would be appreciated.

While we are just debuting a Preview of the TableLayer and we just build our first Table with 1300 fields (it supports even more fields), to have a big table like the one of the contest would be very useful to optimize the TableLayer and the Commands Implementations.

Now We have to build an artificial Big Table (more than 2GB) to continue measuring performance, well back to work.

Thanks,


>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
>