Processing large database - acceleation

[Auge_Ohr]

New application must be faster then dos CA Clipper applicaton.

Windows Application are always (much) slower than DOS ... much more Code !
Xbase++ is (much) slower that Cl*pper ... only String / Array Operation are about same Speed but Operation with DBF take more Time.

benefit of 32bit Windows Application are using RAM > 64kb(!) up to 2GB

In new project I have many databases, in DBF, FPT (FoxPro 2.x format.) about 2000 files. Size of databases is from 0B to about 500-800MB.

you should think about new Database Design ...

Because this I programmed utility , what convert this DBF files to ASCII file, and packed and crypted, after converting size of files is about 1/10 source DBF !.
And then I am searching in this files .
Packer , crypter and search "machine" is programmed in C language, then it is very fast, about 7 times then search in source DBF files.

i do not think "packing" speed up ... you have to "unpack" it too.
Size of DBF does not make a Difference while GOTOP is same Speed as GOBOTTOM with DBF ( same with SEEK() ).

if you have a C DLL you can rewrite it to use it with Xbase++ C API.

[Piotr D]

Victorio,
as Chris says, the most time consume record lockin and unlocking. When this function is run only once in week/month,
try to open first database in EXCLUSIVE mode and don't use RLock() function.

Piotr

[Victorio]

Piotr D, ok, I change this. It is no problem, I can open database exclusive for time of import.

Jimmy: about 19years ago,, I tested it, and time search data in TXT file is better as search in DBF , however i must before search decoding file. C utility was very speed.
In txt file I am searching "some text string" in some structure, because I cannot use indexing in DBF.
For searching in FPT it is problem, because there is variable type of text strings.
Four your information this program process special database , what logging everything , what user work with several other aplications.
This is history containing 20years changes.

[Auge_Ohr]

I tested it, and time search data in TXT file is better as search in DBF , however i must before search decoding file. C utility was very speed.

as i say : rewrite it for Xbase++

In txt file I am searching "some text string" in some structure, because I cannot use indexing in DBF.

why ?
Xbase++ does have OrdWildSeek("*some text string*")

For searching in FPT it is problem, because there is variable type of text strings.

you can Index Memo ( Text ) Field with "fixed" Lengh

Code: Select all

INDEX ON SUBSTR(TRIM(FIELD->MEMO)+SPACE(240),1,240) TO MyIndex

with CDX up to 240 Sign ( FOXPRO2X) or 120 with Collation table ( PDR 4846 )

you can use a UDF to "strip" Memo as Indexkey()
you can use some FTS ( Fast Text Search ) for Memo ( Text ). Alaska does have a APS Sample

//
// (C) Alaska Software Inc. 2005
// Experimental implementation of a approximate
// search pattern-matcher for DBF tables or
// any other bulk information.
//
// The pattern matcher is able to match a token
// in very large dbf-tables with a relative good
// performance.
//
#pragma library("aps10.lib")

Four your information this program process special database , what logging everything , what user work with several other aplications.
This is history containing 20years changes.

so do you need Data which is older than 1 Year for a actual Report ?
are you sure your Database design is update ?

btw. : for Reports i use List & Label

[Victorio]

Jimmy, thanks for important informations, I will look functions and sample source.

About data, databases is in range of 20 years, structure of database was x-times changed then I must before processing test every dbf file , how structure he has, and then processing. For example one field KPV using to year 2001 was used for type of organization, but after year 2001 is used for type of cadastral parcel.

OK, I must "sorting" many information in my "head" and generate some test application.

Thank you very much.

[Victorio]

About optimalization processing database :
I modified source :
1. set exclusive on, and remove rlock() from algoritm
2. modify set order, set only if order changed
3. modify go rec, set only if record number changed
4. put replace with... to one command. (in source I has several rows with replace with..)

this changes give me about 30% better time
yes, most time using is rlock(), other is no effect.

Now I can only modify system of processing, maybe relations, or I do not know, If can help some control of processor time, because this is on 100% if process running.
I can also examine change processing no in DBF but in array, maybe it will be faster than disk access.
So as Jimmy wrote, Xbase run always on one core.

[Piotr D]

Victorio,
when I listen your code, you can made these changes:
- you walk 3 time during database in SELECT 1. You can made yours tests and replace during only one DO WHILE !EOF()..ENDDO
- you pack database. Don't open this with index. IMO faster is USE without indexes and COPY to tmpName FOR !DELETED() and after these INDEX ON (if more indexes, you can USE empty tmpFile, make all indexes and APPEND FROM file FOR !DELETED()

Piotr

[Victorio]

Piotr: yes it is true, 3 times... thanks for notice, this can get me some percentage of time processing.

[Auge_Ohr]

About optimalization processing database :

on Top of you Code you have

Code: Select all

DO WHILE !EOF()
   ...
   if cpac=pomcpac .and. cpae=pomcpae .and. cpue=pomcpue

make a Index and use a SCOPE