Clipper NTX and Index file size ???? 
Author Message
 Clipper NTX and Index file size ????

Can someone explain this?

I have an database, with 3,782,631 records, 6 fields.   The size is 155 Meg.

There are two indexes....  These are the keys:
  index1: syscode+ps_account+ps_div+DTOS(eff_date), Key length: 16, size:
111.5 Meg
  index2: DTOS(eff_date)+syscode+ps_account+ps_div, Key length: 16, size:
108.8 Meg

I tested the integrity of both Indexes are they are good.

I have another file with new records to import (175,152 more records.)

When I open the main file and indexes, then import the new records, these
are the new file sizes.
File:    162,269,330
Index1:    120,883,200
Index2:    123,051,008

When I tested creating NEW indexes from scratch.   I got totally different
file sizes on the indexes (both larger than the DBF file).
Index1:    209,734,656
Index2:    184,645,632

I get the same results using Clipper 5.2 and Clipper 5.3.

Can anyone shed some light?   What is causing the file size differences?
Why would a fresh creation of the index cause the file sizes to be a lot
larger?



Mon, 26 Sep 2005 04:26:37 GMT  
 Clipper NTX and Index file size ????
Hi!

Quote:

> There are two indexes....  
> Index1:    120,883,200
> Index2:    123,051,008
> When I tested creating NEW indexes from scratch.   I got totally different
> file sizes on the indexes (both larger than the DBF file).
> Index1:    209,734,656
> Index2:    184,645,632

There are 2 small indexes. How you have generated them?

Via INDEX ON?  No, this command generates the big indexes.

Via what? :-)

Best regards.



Mon, 26 Sep 2005 13:57:04 GMT  
 Clipper NTX and Index file size ????

...
Quote:
> Via INDEX ON?  No, this command generates the big indexes.

...

How exactly does INDEX ON generate larger than normal indexes?  It simply
preprocesses to dbCreateIndex().  What would you suggest to use in its
place?

--
Ray Marron



Mon, 26 Sep 2005 22:12:00 GMT  
 Clipper NTX and Index file size ????
after append a lot of records index is Most fragmented
after indexed both files index is not fragmented


Quote:
> Can someone explain this?

> I have an database, with 3,782,631 records, 6 fields.   The size is 155
Meg.

> There are two indexes....  These are the keys:
>   index1: syscode+ps_account+ps_div+DTOS(eff_date), Key length: 16, size:
> 111.5 Meg
>   index2: DTOS(eff_date)+syscode+ps_account+ps_div, Key length: 16, size:
> 108.8 Meg

> I tested the integrity of both Indexes are they are good.

> I have another file with new records to import (175,152 more records.)

> When I open the main file and indexes, then import the new records, these
> are the new file sizes.
> File:    162,269,330
> Index1:    120,883,200
> Index2:    123,051,008

> When I tested creating NEW indexes from scratch.   I got totally different
> file sizes on the indexes (both larger than the DBF file).
> Index1:    209,734,656
> Index2:    184,645,632

> I get the same results using Clipper 5.2 and Clipper 5.3.

> Can anyone shed some light?   What is causing the file size differences?
> Why would a fresh creation of the index cause the file sizes to be a lot
> larger?



Tue, 27 Sep 2005 18:54:34 GMT  
 Clipper NTX and Index file size ????
Hi!

Quote:

>> Via INDEX ON?  No, this command generates the big indexes.
> How exactly does INDEX ON generate larger than normal indexes?

Ask John Ward!!! :-)

He wrote "When I tested creating NEW indexes from scratch.   I got
totally different file sizes on the indexes (both larger than the DBF
file)."

I want, that John has shown TWO CODE FRAGMENTS, which give the big and
small index.

Best regards.



Tue, 27 Sep 2005 20:32:02 GMT  
 Clipper NTX and Index file size ????
OK,  let me clear up the confusion.

The smaller indexes were recreated probably around 6 months ago using INDEX
ON.   Since then, new records have been added daily.   The smaller indexes
are what is in production.   I have ran an index checking utility which
checks to make sure every record in a database has an matching key in the
index, and everything passes.

What I do not understand is at this point in the game, how come when I try
and create a NEW set of indexes from Scratch, the size of the indexes are
much larger.  Have I reached some magic number of records that result in
index bloat when I try to create them?

I have tried creating them from scratch using clipper 5.1 and the INDEX ON
fails.   I have tried creating them using INDEX ON using Clipper 5.2 and 5.3
using INDEX ON and the files are much different (greater).

This just does not make sense.


Quote:
> Hi!




Quote:

> > There are two indexes....
> > Index1:    120,883,200
> > Index2:    123,051,008

> > When I tested creating NEW indexes from scratch.   I got totally
different
> > file sizes on the indexes (both larger than the DBF file).
> > Index1:    209,734,656
> > Index2:    184,645,632

> There are 2 small indexes. How you have generated them?

> Via INDEX ON?  No, this command generates the big indexes.

> Via what? :-)

> Best regards.



Tue, 27 Sep 2005 22:23:27 GMT  
 Clipper NTX and Index file size ????
John

Might depend on whether on not you've packed the DBF and if the production app uses
    FOR ! DELETED()
in the index expression.

--
HTH
Steve Quinn



Wed, 28 Sep 2005 02:11:17 GMT  
 Clipper NTX and Index file size ????
Stephen,

Not using any FOR or WHERE to create the index.

What also troubles me (if you read first post)... Why are the newly created
indexes LARGER that the DBF file?   The DBF file has two additional numeric
fields of length 12.

John


Quote:
> John

> Might depend on whether on not you've packed the DBF and if the production
app uses
>     FOR ! DELETED()
> in the index expression.



Wed, 28 Sep 2005 03:36:22 GMT  
 Clipper NTX and Index file size ????

Quote:

> The smaller indexes were recreated probably around 6 months ago using INDEX
> ON.   Since then, new records have been added daily.   The smaller indexes
> are what is in production.   I have ran an index checking utility which
> checks to make sure every record in a database has an matching key in the
> index, and everything passes.

This makes sense, (Clipper) does not attempt to (balance) the Index on
updates, the RDD will only add (new) pages as needed so the existing
Index file sizes are smaller,

DBFNTX, on creating (new) index files will attempt to (balance) the
Index (B-tree), as needed : AFAIK Index files containing a large
number of (Duplicate) keys will suffer "bloat" due to the number of
(blank) Index pages added in order to (balance) the B-tree, in
conjunction w/ the RDD not filling each Index Page with the (maximum)
number of entries (to allow for subsequent updates).

If your Index check rtn validates the (larger) Index it just means the
RDD allowed a lot of extra space in the Index - it should still be ok
to use.

I hope the info helps.

Regards,
Bob

Quote:

> What I do not understand is at this point in the game, how come when I try
> and create a NEW set of indexes from Scratch, the size of the indexes are
> much larger.  Have I reached some magic number of records that result in
> index bloat when I try to create them?

> I have tried creating them from scratch using clipper 5.1 and the INDEX ON
> fails.   I have tried creating them using INDEX ON using Clipper 5.2 and 5.3
> using INDEX ON and the files are much different (greater).

> This just does not make sense.



> > Hi!



> > > There are two indexes....
> > > Index1:    120,883,200
> > > Index2:    123,051,008

> > > When I tested creating NEW indexes from scratch.   I got totally
>  different
> > > file sizes on the indexes (both larger than the DBF file).
> > > Index1:    209,734,656
> > > Index2:    184,645,632

> > There are 2 small indexes. How you have generated them?

> > Via INDEX ON?  No, this command generates the big indexes.

> > Via what? :-)

> > Best regards.



Wed, 28 Sep 2005 04:38:49 GMT  
 Clipper NTX and Index file size ????
Hi,

I wish to show some code, that maybe will explain things up.
You just need a dbf file called test with, say, 100000 records or more, and
3 fields on it (FIELDA, FIELDB, FIELDC).
You can use any file provided that the fields are there, or modify this
fragment accordingly to your field names.

*
function main()
*------------
field FIELDA, FIELDB, FIELDC // 4, 5 AND 8 CHARS EACH FOR THIS TEST, could
be different

** DBFNTX - CLIPPER 5.2E - RTLINK

use TEST new VIA 'DBFNTX'
cls
? 'JUST INDEX ON'
? 'please wait...'
index on FIELDA+FIELDB+FIELDC to TEST1

? 'NOW WITH THE EVAL CLAUSE...'
?
index on FIELDA+FIELDB+FIELDC to TEST2 eval rosca() every 250

? 'OK, PLEASE LOOK THE FILE SIZES OF EACH INDEX...'
? 'SURPRISE!?'
return nil

*
function rosca()
*-------------
?? '.'
return .T.

HTH
--
Saluten
Claudio
Buenos Aires - Argentina
--
"Virus check complete. All viruses are working normally on this computer."



| OK,  let me clear up the confusion.
|
| The smaller indexes were recreated probably around 6 months ago using
INDEX
| ON.   Since then, new records have been added daily.   The smaller indexes
| are what is in production.   I have ran an index checking utility which
| checks to make sure every record in a database has an matching key in the
| index, and everything passes.
|
| What I do not understand is at this point in the game, how come when I try
| and create a NEW set of indexes from Scratch, the size of the indexes are
| much larger.  Have I reached some magic number of records that result in
| index bloat when I try to create them?
|
| I have tried creating them from scratch using clipper 5.1 and the INDEX ON
| fails.   I have tried creating them using INDEX ON using Clipper 5.2 and
5.3
| using INDEX ON and the files are much different (greater).
|
| This just does not make sense.
|


| > Hi!
| >


| >
| > > There are two indexes....
| > > Index1:    120,883,200
| > > Index2:    123,051,008
| >
| > > When I tested creating NEW indexes from scratch.   I got totally
| different
| > > file sizes on the indexes (both larger than the DBF file).
| > > Index1:    209,734,656
| > > Index2:    184,645,632
| >
| > There are 2 small indexes. How you have generated them?
| >
| > Via INDEX ON?  No, this command generates the big indexes.
| >
| > Via what? :-)
| >
| > Best regards.
|
|



Wed, 28 Sep 2005 21:01:12 GMT  
 Clipper NTX and Index file size ????
Bob,

I think I understand what you are saying.   I makes the most sense out of
all of the replies.

Thanks!



Quote:

> > The smaller indexes were recreated probably around 6 months ago using
INDEX
> > ON.   Since then, new records have been added daily.   The smaller
indexes
> > are what is in production.   I have ran an index checking utility which
> > checks to make sure every record in a database has an matching key in
the
> > index, and everything passes.

> This makes sense, (Clipper) does not attempt to (balance) the Index on
> updates, the RDD will only add (new) pages as needed so the existing
> Index file sizes are smaller,

> DBFNTX, on creating (new) index files will attempt to (balance) the
> Index (B-tree), as needed : AFAIK Index files containing a large
> number of (Duplicate) keys will suffer "bloat" due to the number of
> (blank) Index pages added in order to (balance) the B-tree, in
> conjunction w/ the RDD not filling each Index Page with the (maximum)
> number of entries (to allow for subsequent updates).

> If your Index check rtn validates the (larger) Index it just means the
> RDD allowed a lot of extra space in the Index - it should still be ok
> to use.

> I hope the info helps.

> Regards,
> Bob



Thu, 29 Sep 2005 21:43:23 GMT  
 Clipper NTX and Index file size ????
test2 is almost two times biger .... ????????????????

If there is an explanation I would be glad to here it.

bye Jure


Quote:
> Hi,

> I wish to show some code, that maybe will explain things up.
> You just need a dbf file called test with, say, 100000 records or more,
and
> 3 fields on it (FIELDA, FIELDB, FIELDC).
> You can use any file provided that the fields are there, or modify this
> fragment accordingly to your field names.

> *
> function main()
> *------------
> field FIELDA, FIELDB, FIELDC // 4, 5 AND 8 CHARS EACH FOR THIS TEST, could
> be different

> ** DBFNTX - CLIPPER 5.2E - RTLINK

> use TEST new VIA 'DBFNTX'
> cls
> ? 'JUST INDEX ON'
> ? 'please wait...'
> index on FIELDA+FIELDB+FIELDC to TEST1

> ? 'NOW WITH THE EVAL CLAUSE...'
> ?
> index on FIELDA+FIELDB+FIELDC to TEST2 eval rosca() every 250

> ? 'OK, PLEASE LOOK THE FILE SIZES OF EACH INDEX...'
> ? 'SURPRISE!?'
> return nil

> *
> function rosca()
> *-------------
> ?? '.'
> return .T.

> HTH
> --
> Saluten
> Claudio
> Buenos Aires - Argentina
> --
> "Virus check complete. All viruses are working normally on this computer."



> | OK,  let me clear up the confusion.
> |
> | The smaller indexes were recreated probably around 6 months ago using
> INDEX
> | ON.   Since then, new records have been added daily.   The smaller
indexes
> | are what is in production.   I have ran an index checking utility which
> | checks to make sure every record in a database has an matching key in
the
> | index, and everything passes.
> |
> | What I do not understand is at this point in the game, how come when I
try
> | and create a NEW set of indexes from Scratch, the size of the indexes
are
> | much larger.  Have I reached some magic number of records that result in
> | index bloat when I try to create them?
> |
> | I have tried creating them from scratch using clipper 5.1 and the INDEX
ON
> | fails.   I have tried creating them using INDEX ON using Clipper 5.2 and
> 5.3
> | using INDEX ON and the files are much different (greater).
> |
> | This just does not make sense.
> |


> | > Hi!
> | >


> | >
> | > > There are two indexes....
> | > > Index1:    120,883,200
> | > > Index2:    123,051,008
> | >
> | > > When I tested creating NEW indexes from scratch.   I got totally
> | different
> | > > file sizes on the indexes (both larger than the DBF file).
> | > > Index1:    209,734,656
> | > > Index2:    184,645,632
> | >
> | > There are 2 small indexes. How you have generated them?
> | >
> | > Via INDEX ON?  No, this command generates the big indexes.
> | >
> | > Via what? :-)
> | >
> | > Best regards.
> |
> |



Sat, 01 Oct 2005 00:37:29 GMT  
 Clipper NTX and Index file size ????
Jure

Probably the memory image isn't dumped to disk until the EVAL occurs.

--
HTH
Steve Quinn



Sat, 01 Oct 2005 10:18:08 GMT  
 
 [ 15 post ] 

 Relevant Pages 

1. ODBC DRIVER FOR CLIPPER DBF/DBT THAT SUPPORTS NTX INDEX FILES

2. Clipper Index (NTX) file format

3. Clipper NTX's, file size differences....

4. Clipper NTX index alway corrupt

5. Corrupted Index (NTX), Clipper 52e

6. accessing CA-Clipper 5.2 indexes (*.ntx) with Delphi 2.0

7. Clipper 5.3 Index (NTX) Problem

8. Visual C - how to access DBF files with NTX index

9. Big file index (NTX)

10. Corruption Detected (ntx index files)

11. Max file size DBF/NTX?

12. ODBC driver for Clipper - handling ntx files

 

 
Powered by phpBB® Forum Software