which is the fastest way to load contets of a DBF into an Ar

[ukservice]

Hi,

Which is the fastest way to load the contents of a DBF into an Array.

Something faster than this:

Code: Select all  Expand view  RUN

SELECT ("CUSTOMER")
DbGoTop()

do while !eof()
   
     aadd(myarray, CUSTOMER->NAME)
     dbskip()

enddo

Thank you

[ADutheil]

Did you try dbEval( { || aadd(myarray, CUSTOMER->NAME) } )?

[ukservice]

Thank you, but it takes one more second!!!.

[nageswaragunupudi]

The above code works quite fine on small and medium databases. More time is spent on data access than on code execution. Data access speeds can vary for the same code at different times depending on various factors, including whether some parts of the data requested are already in the cached memory. However here are some tips for speed optimization if we are dealing with large databases and arrays.

1. Preallocating memory for the total array size:

Code: Select all  Expand view  RUN

aData := {}
ASizeAlloc( aData, CUSTOMER->( LASTREC() ) )
 

Preallocation of memory saves the time usually taken by successive AAdd() operations for requesting allocation of additional memory for the growing array.

2. Avoid switching of Alias:

Instead of AAdd( aData, CUSTOMER-NAME ), better to use AAdd( aData, FIELD->NAME ). Note: CUSTOMER alias ia already selected. This saves time taken for switching alias each time.

3. Use with ReadOnly and without indexes.

Using a DBF with READONLY and without opening indexes improves the through put speeds.

Finally, speed benefits of all these optimizations can be perceived only on larger databaess and even then these savings are minor when compared with the total data access timings, which as I said depend on various other factors.

[James Bott]

All good stuff that Rao pointed out.

Here are some more ideas.

When the data is static, or relatively so, consider loading the data into a static array at the start of the app or of a routine that will be using the data multiple times. Thus the data is only loaded once.

Anytime the computer appears to have hung, or gone into limbo, users will be frustrated. If you can provide some visual feedback that makes the computer appear to be working when it is doing something like loading data, then the users will more easily accept the delay. Note that visual feedback will actually slow down the process due to the increased CPU work, but the users will accept this more easily than no visual feedback.

Regards,
James

[ukservice]

Thank you for all your ideas.

Unfortunately, data is not static, so I can´t load at startup.

I thought it was a C level alternative.

[Carles]

Hi,

Maybe you need to change the process. If system is very slow i assume is a result to load a big table and my question for u is... It's necessary ?

[James Bott]

As Carles says, "Is it necessary?"

Can you just access the database directly using an index and/or filters?

How big is the array? How long is it taking to load it?

I don't think C could help much, because most of the time is spent reading the disk which is hardware dependent.

Regards,
James

[nageswaragunupudi]

I don't think C could help much, because most of the time is spent reading the disk which is hardware dependent.

As I mentioned earlier, most of the times it is the data access speeds that decide the total time taken. Optimizing the code execution speeds does not yield perceptible time savings.
In any case for this kind of code, accessing DBF, even C level subfunction does not greatly reduce time. We can think of C Subcode for highly comutational itensive routines.

[MarcoBoschi]

Hi,
in my server there is a running process (named search32.exe) that load three big tables into three different arrays.
When a user from application have to perform a wild search ( in all character type fields ) eg search
"COMPUTER PADOVA" program searches all customer that have these two words in any fields.
In server process I have to load three arrays

aClifor := load_dbf( "clifor" )
aMag_ri := load_dbf( "mag_ri" )
aBf_r := load_dbf( "bf_r" )

The searches are very fast but the problem is another:
When a Record is modified I write in a log file these two informations: table_name, record_number

Search32 process is polling looking in log file every new request: there are two kinf of requests: search request and update record request.

This is the source code of agg_rec ( for updating array from dbf )

Code: Select all  Expand view  RUN

FUNCTION AGG_REC( cFile, aFile, nRecord )
LOCAL cDelete
LOCAL aRec := {}
LOCAL nFld, nNew
LOCAL nNuovi
LOCAL aNew := {}
LOCAL nStart := LEN( aFile )
LOCAL i
// se ci sono nuovi record prima crea i nuovi elementi dell'array vuoti
// if there are new records at first create new empty elements

IF nRecord > LEN( aFile )

   nNuovi := nRecord - LEN( aFile )

   FOR nNew := 1 TO nNuovi
       aRec := {}
       FOR nFld := 1 TO FCount()
           IF fieldtype( nFld ) = "C" .OR. fieldtype( nFld ) = "M"
              AADD( aRec , "" )
           ENDIF
       NEXT i

       AADD( aFile , aRec )
       AADD( aNew , nStart + nNew )

   NEXT nNew
ELSE
   AADD( aNew , nRecord )
ENDIF

SELECT &cFile

FOR nNew := 1 TO LEN( aNew )

    i := aNew[ nNew ]
    GOTO aNew[ nNew ]

    aFile[ i , U_DELETE ] := cDelete

    aFile[ i , U_DELETE ] := " "
    aRec := {}

    AADD( aRec , IIF( DELETED() , "D", " " ) )
    FOR nFld := 1 TO FCount()

        IF fieldtype( nFld ) = "C" .OR. fieldtype( nFld ) = "M"
           AADD( aRec , UPPER( fieldget( nFld ) ) )
        ENDIF

    NEXT i

    FOR nFld := 1 TO LEN( aFile[ i ] )
        IF aFile[ i , nFld ] <> aRec[ nFld ]
           aFile[ i , nFld ] := aRec[ nFld ]
        ENDIF
    NEXT nFld

NEXT nNew

RETURN NIL

With this solution:
- users can perform wild search in customer table (50.000 record and 50 mb of size) in 3/8 seconds
- when a record is updated in 1 second memory server array is updated too in one seconds
- in less than one minute each table is loaded into his own array in memory.
In my opinion this solution solves the worst feature of the programs that use dbf tables in a file/server architecture

[Maurizio]

Marco ,
have you tried with INDEX TEMPORARY and work in the DBF ? are very faster .
Maurizio
www.nipeservice.com

[Gale FORd]

Are you using xHarbour.com professional or enterprise? If so, you can use FTS (Fast Text Search) to index and search all or part of data file while sharing with other users.

[ukservice]

Thank you very much.

I am using Harbour and I won´t change to xHarbour.

I need the Array in order to use the Autoget feature.