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

Posts: 417

Joined: Tue Feb 23, 2010 03:09 PM

which is the fastest way to load contets of a DBF into an Ar
Posted: Mon Jun 20, 2011 08:16 PM

Hi,

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

Something faster than this:

SELECT ("CUSTOMER")
DbGoTop()

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

enddo

Thank you

FWH 11.11, Harbour 3.1 and Borland C++ 5.82

ADutheil

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Mon Jun 20, 2011 08:26 PM

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

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

ukservice

Posts: 417

Joined: Tue Feb 23, 2010 03:09 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Mon Jun 20, 2011 09:21 PM

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

FWH 11.11, Harbour 3.1 and Borland C++ 5.82

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Tue Jun 21, 2011 07:38 AM

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 (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Tue Jun 21, 2011 02:05 PM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

ukservice

Posts: 417

Joined: Tue Feb 23, 2010 03:09 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Tue Jun 21, 2011 06:35 PM

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.

FWH 11.11, Harbour 3.1 and Borland C++ 5.82

Carles

Posts: 1283

Joined: Fri Feb 10, 2006 02:34 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Tue Jun 21, 2011 07:33 PM

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 ?

Salutacions, saludos, regards

"...programar es fácil, hacer programas es difícil..."

UT Page -> https://carles9000.github.io/
Forum UT -> https://discord.gg/bq8a9yGMWh
HIX -> https://github.com/carles9000/hix

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Tue Jun 21, 2011 11:13 PM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Wed Jun 22, 2011 03:59 AM

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.

Regards

G. N. Rao.

Hyderabad, India

MarcoBoschi

Posts: 1091

Joined: Thu Nov 17, 2005 11:08 AM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Wed Jun 22, 2011 07:10 AM

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 (fw): Select all Collapse

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

Marco Boschi
info@marcoboschi.it

Maurizio

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Wed Jun 22, 2011 02:13 PM

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

www.nipeservice.com

Maurizio
www.nipeservice.com

Gale FORd

Posts: 663

Joined: Mon Dec 05, 2005 11:22 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Wed Jun 22, 2011 09:19 PM

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

Posts: 417

Joined: Tue Feb 23, 2010 03:09 PM

Re: which is the fastest way to load contets of a DBF into an Ar
Posted: Thu Jun 23, 2011 09:00 AM

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.

FWH 11.11, Harbour 3.1 and Borland C++ 5.82

FiveTech Support Forums

Continue the discussion