replace problem in large DBF

[Victorio]

Hi,
I have standard DBF with CDX VFP compatible index but created with Xbase++.

Now I found one problem, wher in large database time dramatically slow down from about 16000 processed record.

For example I have DBF with 400000 records opened with index.
One from index key is also key for example named ABC

use mytable index mytable EXCLUSIVE ALIAS MYTAB
go top
replace ABC with space(0)

when DBF have less than about 15000 - 16000 records, works fine, But when processind more, time slow down to many hours.

I tryed also this

go top
do while !eof()
replace ABC with space(0)
skip
enddo
and also here problem

I found some info that problem can be if replacing content of field used in index key and this I do.

I found some solution to open DBF without index replace ABC for all records and then open DBF with index and use REINDEX.
But thic can not use everywhere.

Is some solution for it ? THX

[sdenjupol148]

Victorio,

I found some solution to open DBF without index replace ABC for all records and then open DBF with index and use REINDEX.
But thic can not use everywhere

Assuming that you use index tags, instead of opening the DBF without the index, open it with the index and set your index tag to zero (0) .
<Alias>->(OrdSetFocus(0))

This will keep the index open but will not re-position the key.
When you're finished processing simply reset your OrdSetFocus() to what ever index tag you need.
You will not need to re-index.

Bobby

[bwolfsohn]

make sure you have str(recno(),10) in the index. otherwise, you will be having massive performance hits from non-unique index keys.

[Auge_Ohr]

Network or Local

how does you DBESYS look like
was DBF create with Xbase++

[Victorio]

Bobby : I tryed set order to 0 and also OrdSetFocus (0) but without effect

bwolfsohn : make sure you have str(recno(),10) in the index. otherwise, you will be having massive performance hits from non-unique index keys.
how do you mean ? I need include this key to my index ?

jimmy : here is my Dbesys
DBF I created with Visual Foxpro, because when create with Dbcreate() I had some problems with header in table, which is not compatible with VFP - byte 30/31 , I do not remember what problem was but now I have template DBF and word DBF created simply with filecopy().
List of structure table is attached (from headerinfo utility)
Can be some wrong in Dbesys, but true is that problem begin when processing more than 16000 records, when divide process to blocks for example 1000 records, first 16000 processed quick, but when start 16000 stopped or very very long time to process.
This look to some variable/array limits in function replace, or in updating index keys.

Code: Select all

****************************
PROCEDURE DbeSys()
****************************
_LoadDbes()

RETURN

****************************************
STATIC FUNCTION _LoadDbes()
****************************************
LOCAL i, aDbeList := DbeList(), cDbeList := ''

SET COLLATION TO GERMAN        // v CDX sa objaví text GERMAN                                // TOTO SKÚŠAM kvôli RLC5D kódovaniu a porovnávaniu reťazcov
                                                                         // lebo ak je SYSTEM,tak je porovnanie case insensitive

IF Valtype(aDbeList) = 'A'
  FOR i := 1 TO Len(aDbeList)
    cDbeList += aDbeList[i,1] + ','
  NEXT
ENDIF

aDbeList := cDbeList

  IF !('DBFDBE'$aDbeList) .AND. !DbeLoad( "DBFDBE",.T.)
     DC_WinAlert( "Database-Engine DBFDBE not loaded" )
  ENDIF
  IF !('NTXDBE'$aDbeList) .AND. !DbeLoad( "NTXDBE",.T.)
     DC_WinAlert( "Database-Engine NTXDBE not loaded"  )
  ENDIF
  IF !('DBFNTX'$aDbeList) .AND. !DbeBuild( "DBFNTX", "DBFDBE", "NTXDBE" )
     DC_WinAlert( "DBFNTX Database-Engine, Could not build engine" )
  ENDIF
  IF !('CDXDBE'$aDbeList) .AND. !DbeLoad( "CDXDBE",.T.)
     DC_WinAlert( "Database-Engine CDXDBE not loaded" )
  ENDIF
  IF !('DBFCDX'$aDbeList) .AND. !DbeBuild( "DBFCDX", "DBFDBE", "CDXDBE" )
     DC_WinAlert( "DBFCDX Database-Engine, Could not build engine" )
  ENDIF
  IF !('FOXDBE'$aDbeList) .AND. !DbeLoad( "FOXDBE",.T.)
     DC_WinAlert( "Database-Engine FOXDBE not loaded" )
  ENDIF
  IF !('FOXCDX'$aDbeList) .AND. !DbeBuild( "FOXCDX", "FOXDBE", "CDXDBE" )
     DC_WinAlert( "FOXCDX Database-Engine, Could not build engine" )
  ENDIF
  IF !('DELDBE'$aDbeList) .AND. !DbeLoad( "DELDBE",.T.)
     DC_WinAlert( "Database-Engine DELDBE not loaded" )
  ENDIF

DbeSetDefault( "FOXCDX" )        // toto bolo
*DbeSetDefault( "DBFCDX" )        // toto iba som skúšal, ale bez rozdielu

DbeInfo(COMPONENT_DATA,  FOXDBE_LIFETIME,       20 )    && bielik

RETURN .t.

[Victorio]

here structure template table

Code: Select all

velkost sŁboru :       712
         1 -         48
         2 -         18
         3 -          6
         4 -          6
         5 -          0
         6 -          0
         7 -          0
         8 -          0
         9 -        200
        10 -          2
        11 -        216
        12 -          0
        13 -          0
        14 -          0
        15 -          0
        16 -          0
        17 -          0
        18 -          0
        19 -          0
        20 -          0
        21 -          0
        22 -          0
        23 -          0
        24 -          0
        25 -          0
        26 -          0
        27 -          0
        28 -          0
        29 -          0
        30 -        200
        31 -          0
        32 -          0
Headerinfo on pomdbf5

Code / Type      : 48  Visual Foxpro
Last update      : 18  6  6
Number of records: 0.00
Position 1st rec : 713
Header length    : 712
Record length    : 216
File size        : 712
Struct Index File: No
Memo file        ; No
File is a VFP DBC: No
Number of fields : 13.03
Codepage mark    : 200  ( Codepage 1250  Eastern European Windows )

Structure of pomdbf5
Field/name     type length offset  fieldflag      AutoIncr   AutoIncr 
                           in rec                 NextValue  StepValue
----------------------------------------------------------------------
   1 BLOK        N     7.0      1               
   2 PVZC        C     5        8               
   3 PVZR        C     4       13               
   4 DRSU        C     2       17               
   5 TZME        C     2       19               
   6 HOD1        C    20       21               
   7 HOD2        C    20       41               
   8 HOD3        C    20       61               
   9 HOD4        C    40       81               
  10 SPRAC       C     1      121               
  11 TEXTR       C    80      122               
  12 DATZM       C     8      202               
  13 CASZM       C     6      210               
----------------------------------------------------------------------
pomdbf5 is not associated with a DBC

Some checks...
Physical filesize ok                    : NO, TRUNCATED (1.00 bytes)
Last update date valid                  : Yes
Header record terminator (0x0D) found at: 449  OK
Record length according to fielddefinitions is 216.00 which is OK
There were no problems with the field definitions

[bwolfsohn]

bwolfsohn : make sure you have str(recno(),10) in the index. otherwise, you will be having massive performance hits from non-unique index keys.
how do you mean ? I need include this key to my index ?

Yes, here's a sample index

"upper(field->state+field->city)+str(recno(),10)"


ALWAYS add str(recno(),10) to any character index.

[Victorio]

Brian, thanks, this looks interesting, I must look if not problem when seek records in my program,

but at this moment I found other solution with small change algorithm. I use one field to sign if record processed, but after process it never used.
I change replace fieldx to " " i change to delete . (fieldx is exmple of name of field..)
Now works fine, speed, no impact to exist index.

But when I testing replace fieldx with " ", this looks to some problem when alaska xbase store changes in index fields/file to array, or I do not where, and when more than 16000 records, then starts problems, maybe this is limit to store in memory and after exceed it start save to disk. This is only my deduction.

If new algorithm will work without problems, this can be solved, but for me new knowledge than also with this can be big problems

[rdonnay]

ALWAYS add str(recno(),10) to any character index

That would have been my suggestion too.

[Victorio]

Ok, I will try it, thanks