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

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

Postby ukservice » Mon Jun 20, 2011 8:16 pm

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 ;)
FWH 11.11, Harbour 3.1 and Borland C++ 5.82
User avatar
ukservice
 
Posts: 417
Joined: Tue Feb 23, 2010 3:09 pm
Location: John

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

Postby ADutheil » Mon Jun 20, 2011 8:26 pm

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

André Dutheil
FWH 13.04 + HB 3.2 + MSVS 10
ADutheil
 
Posts: 368
Joined: Sun May 31, 2009 6:25 pm
Location: Salvador - Bahia - Brazil

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

Postby ukservice » Mon Jun 20, 2011 9:21 pm

Thank you, but it takes one more second!!!.
FWH 11.11, Harbour 3.1 and Borland C++ 5.82
User avatar
ukservice
 
Posts: 417
Joined: Tue Feb 23, 2010 3:09 pm
Location: John

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

Postby nageswaragunupudi » Tue Jun 21, 2011 7: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: 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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby James Bott » Tue Jun 21, 2011 2: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
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

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

Postby ukservice » Tue Jun 21, 2011 6: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
User avatar
ukservice
 
Posts: 417
Joined: Tue Feb 23, 2010 3:09 pm
Location: John

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

Postby Carles » Tue Jun 21, 2011 7: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
Skype -> https://join.skype.com/cnzQg3Kr1dnk
User avatar
Carles
 
Posts: 1143
Joined: Fri Feb 10, 2006 2:34 pm
Location: Barcelona

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

Postby James Bott » 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
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

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

Postby nageswaragunupudi » Wed Jun 22, 2011 3: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
User avatar
nageswaragunupudi
 
Posts: 10690
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby MarcoBoschi » Wed Jun 22, 2011 7: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: 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
User avatar
MarcoBoschi
 
Posts: 1066
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy

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

Postby Maurizio » Wed Jun 22, 2011 2:13 pm

Marco ,
have you tried with INDEX TEMPORARY and work in the DBF ? are very faster .
Maurizio
www.nipeservice.com
User avatar
Maurizio
 
Posts: 826
Joined: Mon Oct 10, 2005 1:29 pm

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

Postby Gale FORd » Wed Jun 22, 2011 9: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.
Gale FORd
 
Posts: 663
Joined: Mon Dec 05, 2005 11:22 pm
Location: Houston

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

Postby ukservice » Thu Jun 23, 2011 9: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
User avatar
ukservice
 
Posts: 417
Joined: Tue Feb 23, 2010 3:09 pm
Location: John


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 68 guests