Faster way then append from SDF

[Marc Vanzegbroeck]

Hi,

In an application, I read a text-file with more then 1300000 lines, in a database with one field.
After reading it, I go thrue the file, and depending of the information on each line, I extract information then I need into another table.
It's working fine, but the process take a while.

I found out, that only read the file with 'APPE FROM .... SDF take more than 30sec.
I the a faster way to read the information, and check the content of each line?

Thanks

[hmpaquito]

Hi,

Don´t write, only read.

Use FileEval() function from https://github.com/ibarrar/clipper/blob/master/CLIPPER5/SOURCE/SAMPLE/FILEIO.PRG

Regards

[Marc Vanzegbroeck]

Thank you, I wiil try it

[Jack]

Hi,
Did you try this ?

oTxt:=TTxtFile():New("yourtxtfile.txt")
wnbl:=oTxt:recCount() && number of line
if wnbl>0
for wbcl = 1 to wnbl
aadd(vtxt,oTxt:ReadLine())
oTxt:skip()
next
endif
oTxt:end()
*

Try it .
Philippe from Belgium

[Marc Vanzegbroeck]

Philippe,

Thank you for the suggestion, but unfortunately, it's slower.
It took 64sec to read it. The 'append from' was 35sec.

Hi,
Did you try this ?

oTxt:=TTxtFile():New("yourtxtfile.txt")
wnbl:=oTxt:recCount() && number of line
if wnbl>0
for wbcl = 1 to wnbl
aadd(vtxt,oTxt:ReadLine())
oTxt:skip()
next
endif
oTxt:end()
*

Try it .
Philippe from Belgium

[nageswaragunupudi]

Will you please try this also?

Code: Select all  Expand view

#include "fivewin.ch"

#define BUFSIZE   64000
#define EOLCHR    CRLF
#define EOLLEN    2

static cBuf    := ""
static nStart  := 1
static lEof    := .f.

//----------------------------------------------------------------------------//

function Main()

   local cFile, hFile, cLine
   local alines   := {}  // for testing
   local nLine    := 0   // for testing
   local nSecs

   cFile    := "c:\fwh\source\classes\xbrowse.prg"  // your file here
   hFile    := FOpen( cFile, 64 )

   ? "Start"

   nSecs    := SECONDS()
   do while ( cLine := ReadLine( hFile ) ) != nil
      //
      // do your work here with cLine
      //
      nLine++                            // for testing
      AAdd( aLines, { nLine, cLine } )   // for testing
   enddo
   FClose( hFile )

   ? "Seconds:", SECONDS() - nSecs

   XBROWSER aLines SHOW RECID // Testing
   ? "Done"

return nil

//----------------------------------------------------------------------------//

static function ReadLine( hFile )

   local cLine, nRead, nEolAt

#ifdef __XHARBOUR__
   nEolAt      :=    At( EOLCHR, cBuf, nStart )
#else
   nEolAt      := HB_At( EOLCHR, cBuf, nStart )
#endif
   if nEolAt == 0
      if !lEof
         cBuf     := SubStr( cBuf, nStart ) + ReadBuf( hFile, @lEof )
         nStart   := 1

#ifdef __XHARBOUR__
         nEolAt   :=    At( EOLCHR, cBuf, nStart )
#else
         nEolAt   := HB_At( EOLCHR, cBuf, nStart )
#endif
      endif
   endif
   if nEolAt > 0
      cLine    := SubStr( cBuf, nStart, nEolAt - nStart )
      nStart   := nEolAt + EOLLEN
   else
      if nStart <= Len( cBuf )
         cLine    := SubStr( cBuf, nStart )
      endif
      cBuf  := ""
   endif

return cLine

//----------------------------------------------------------------------------//

static function ReadBuf( hFile, lEof )

   local cBuf  := SPACE( BUFSIZE )
   local nRead

   nRead    := FRead( hFile, @cBuf, BUFSIZE )
   if nRead < BUFSIZE
      lEof  := .t.
      cBuf  := Left( cBuf, nRead )
   endif

return cBuf

//----------------------------------------------------------------------------//
 

[James Bott]

Maybe you could create a conditional index containing only the records you need. Then you don't even have to move the records to another file. Time required to get the records you want, zero.

Just thinking outside the box.

[Marc Vanzegbroeck]

Roa,

Your example is very fast Only 2sec

I tested the loop without the line

Code: Select all  Expand view

AAdd( aLines, { nLine, cLine } )

With this line it take 138sec .

So the aadd() take a lot of time.
That's no problem, because I can direcly do the content check, and only at the information I need.


James,
The problem was not going through the database, but reading the text-file. Otherwhise the condition index was a good idea.

[Rick Lipkin]

Marc

Here is a snipit from a Find and replace file utility I wrote that opens any text file you like .... hope you get the gist on how to use ..

Rick Lipkin

Code: Select all  Expand view

Do While .not. Eof()
   
   cFILE := ALLTRIM( A->FileName )

   If ( nHANDLE := Fopen( cFILE )) > 0
   Else
      SAYING := "ERROR Reading file "+cFILE
      YESNO  := { "Skip", "Abort" }

      nOK := Alert(SAYING,YESNO, )

      DO CASE
      CASE nOK = 1      // skip
           SELECT 1
           DELETE
           SKIP
           oLbx:ReFresh()
           lAborted := .f.
           Exit
      CASE nOK = 2      // abort
           Select 1
           Zap
           oLbx:ReFresh()
           Return(.f.)
      OTHERWISE
           Select 1
           Zap
           oLbx:ReFresh()
           Return(.f.)
      ENDCASE
   ENDIF

   Text_Eof   := FSEEK( nHANDLE, 0, 2 )  // get eof value
   Bytes_Read := 0
   Line       := 0

   Fseek( nHANDLE, 0 )                // rewind to bof()

   For Bytes_read = 0 to Text_eof

       Bytes_begin := FSEEK( nHANDLE, 0, 1)
       cText       := _FreadLine( nHANDLE )        // this is what extracts the text

       cText := upper( cText )
       cOldText    := cText

    *   msginfo( "cText" )
    *   msginfo( cText )


       DO CASE
       CASE cTEXT = ">EOF<"  .and. Bytes_read = 0
            // do nothing and go on BOF //
       CASE cTEXT = ">EOF<"  .and. Bytes_read > 0
          Exit
       ENDCASE

       bytes_read := FSEEK( nHANDLE, 0, 1 )
       LINE++

       cText := cText+"   Line "+str(Line,5)
       oText:ReFresh()
       SysReFresh()

       cText := cOldText

       nFOUND := AT( cTextFind, cTEXT )

       If nFOUND > 0
      ...
      ...
      ...


//-------------------------------------
Static FUNC _FreadLine( nHANDLE, LINE_LEN )

LOCAL MAXLINE := 512, BUFFER, LINE_END, NUM_BYTES

IF VALTYPE( LINE_LEN ) <> "N"
   LINE_LEN := MAXLINE
ENDIF

BUFFER    := SPACE(LINE_LEN)
NUM_BYTES := FREAD( nHANDLE, @BUFFER, LINE_LEN )
LINE_END  := AT( CHR(13)+CHR(10), BUFFER )

IF LINE_END = 0           // no cr or must be eof() or bof()

    FSEEK( nHANDLE, 0 )    // go back to top
    RETURN( ">EOF<" )

ELSE

   FSEEK ( nHANDLE, (NUM_BYTES * -1) + LINE_END+1, 1 ) // move to next line
   RETURN( SUBSTR(BUFFER, 1, LINE_END-1) )

ENDIF

// eof freadline

 

[James Bott]

Marc,

The problem was not going through the database, but reading the text-file. Otherwhise the condition index was a good idea.

Well, I was thinking that you could eliminate the export/import and just use the original file with the index set.

I don't know exactly what you doing with the new file you are creating, but if you are just processing it, then you can just use the original file indexed.

oDBF:setIndex( cWhatever )

Less is more...

James

[nageswaragunupudi]

I don't know exactly what you doing with the new file you are creating, but if you are just processing it, then you can just use the original file indexed.

The original file is a text file. Not a dbf file.
Do you think we can create an index on the text file?

[nageswaragunupudi]

Mr. Marc

That's no problem, because I can direcly do the content check, and only at the information I need.

Yes, that is the idea.
Earlier you were spending around 30 seconds to transfer the data from the text file to a dbf file and then starting your process. Now, this 30 secs is reduced to 2 seconds. You can straight away start processing without waiting for the conversion.

Even these 2 seconds time can be reduced further:
1) The function was written for variable length records. Because you are using APPEND FROM .. SDF, I guess the records in the text file are all of a fixed length. If we modify the program to fixed length records of known record length, the reading would be even much faster.
2) Converting the functions from Harbour to C makes it even faster.

But the additional work may not worth just to save 1 second more.