Clipper and index file size limits (ntx) 2

[RichardHall]

I have a client with a database file containing 2.5 million records using win98. When I index the file on a 16 char field, the index file created (ntx) is about 65 meg. No errors are reported during indexing but when I bring up the file using DBU and the associated index there are no records displayed when browsing. Have I reached some kind of limitation? Is there a way to increase this limitation?
Any help will be much appreaciated.
Thx
Rich Hall

 

[TonHu]

You have reached a (few) nasty bug(s) in the DBFNTX driver. It will muck up your indexes, and there is not much more to do then reduce the database size (recordcount), or switch databasedriver.
If possible switch to the FoxPro compound indexes, with the file-extension .cdx I've used The Six Driver (tm) to create them, but the last incarnation of the DBFCDX (Clipper 5.2e) is quite stable. Cl5.3 should be as good after all the patches, I don't use that 'stuff' (5.3).
Advantages of the FoxPro indexes are that they are compressed, all in one indexfile (.cdx, not with .idx) so they use less filehandles, faster and (much) more reliable.
I have no figures to prove this, just plain hard experience in the field, on a few databases over 100 MB each in 1 app.

HTH
TonHu

 

[djhugo]

I basically agree with TonHu. Although Clipper in theory has no limitations on file size or no. of records, it (especially DBU) does start giving problems once you reach a certain (unknown?) no. of records combined with a certain index key length. So if your index key is very short, you might get away with more records before you run into problems.
A possible 'quick and dirty' solution might therefore be to shorten the length of the index string, eg

 INDEX ON SUBSTR(fieldname,1,8)

instead of

 INDEX ON fieldname

(where fieldlength = 16 at this stage).
NB! Here I'm of course assuming that your index field is such that it can be truncated without affecting data/index integrity! And let's be honest - there aren't many such fields - even something 'simple' such as surname might cause problems.

DJ Hugo

 

[TonHu]

If your database design is correct then this should be impossible, but I agree it probably gives you some time before the next crash will occur. IMHO switching to another databasedriver is better, not in the least for performance reasons. If your database is this big, performance will be an issue, and every little bit helps s-)

HTH
TonHu

 

[clip4ever]

Interesting suggestion to use FoxPro CDX compound index.
The question is how can we do that ?
Can you TonHu please explain ?
It might solve performance of our system using Clipper 5.2c.
Thanks
Clip4ever

 

[TonHu]

Step 1. Upgrade to Clipper 5.2e ASAP! (Forget 5.3)
Step 2. If you forgot to do step 1, then upgrade to Clipper 5.2e
Step 3. After perforing step 1 or step 2, please check the workings of your program with the big database and the DBFNTX indexes. Fat chance most of your troubles wil be gone........
Step 4. If step 3 fails: Read the NortonGuide (included with your Clipper installation, subdir NG) Called 'Clipper 5.2 > Drivers Guide' It has a lot more details then I like to type in here
In short:
- Put somewhere in your code :

REQUEST DBFCDX

- Before opening any database or indexfiles, make the new driver the default by calling

RDDSetDefault('DBFCDX')

- When indexing, make sure to create compound indexes (.cdx) by adding the clause

... INTO (indexfilename)

to your index statement(s), else it wil create single indexes (.idx), just like DBFNTX does. These .idx indexes are already more efficient, but .cdx is even better. You should add all permanent indexes to 1 compound indexfile.
- When USE-ing the database/indexes, specify the compound indexfile only.

USE (DataBase) INDEX (CompoundIndex)

- When changing indexorder specify the separate index names
- If you already have a function like IndexExt() instead of '.ntx' then most of the above should be a POC (piece of cake) ;-) (This function I had to make myself, as early RDD's didn't have a similar function OrdBagExt())
- Modify your link-script adding

LIB DBFCDX

- You can use multiple databasedrivers (RDD's) in a single program, upto 1 per database, but it wil cost you memory.
If you are using a protected mode linker like ExoSpace or Blinker 3.x or newer this is hardly a problem, as the RDD's are protected-mode safe, but in real-mode situations, 2 RDD's in a single app is already pushing it to the limits :-(
There is the VIA clause in the USE command, specifying the RDD to be used, so you can omit the RDDSetDefault() call.
If you have a nice dictionary driven system, or a central 'OPEN_DATABASE' function, then you probably only have to change a 'few' lines of code to make this work. If your USE statements are scattered around your code then I can only wish you a lot of luck ;-)

I implemented this in my own, proprietairy and non-available, DataDictionary system, and it took me a 'few weeks' to get things fully independent from the underlaying database, but then it really payed off, cause I can change the active RDD in a whole system in 1 single conversion, and a re-link of the EXE, with the correct RDD added. Testing takes most of the time

Please don't forget I'm *not* currently developing anything in Clipper these days (It's Cobol time...), so my clipper statements are a bit rusty, but I have a respectable codebase available.

If you need more info during implementation, then just ask in this forum. There are lots of guru's here ;-)

HTH
TonHu

 

[clip4ever]

Thank you TonHu. I will act on your recommandations. You never know, this might be the key to re-vitalizing this critical application.

Clip4ever

 

[clip4ever]

OK...
I got Clipper 5.2e working.
The FoxPro DBFCDX RDD is giving me trouble. I probably did not understand the changes weel.

STEP 1:
I have a database with some table indexed on several key combinations:

Use dbTable
Index on x + Y + Z to db1
Index on L + M + N to db2
Index on P + Q + R to db3

Step 2:
With DBFNTX I usually need the index order to be:
Set INDEX to db3, db1, db2
Or sometimes like:
SET INDEX to db2, db3, db2

Step 3.
Using DBFCDX I created the indexes as:

Index on x + Y + Z TAG db1 to dbTable
Index on L + M + N TAG db2 to dbTable
Index on P + Q + R TAG db3 to dbTable

Step 4:
What shall I do to accomplish in DBFCDX what I used to use in Step 2 above. Is there anything other than creating a CDX for each combination ?

I appreciate some help since I ran out of ideas.

Clip4ever

 

[TonHu]

Why on earth are you changing the order of index-usage????

Using a simple

SET ORDER TO 1 (or 2, or 3...)

you can change the active index used for seeking/skipping the table, and that is the 'preferred' (read: normal) way anyway, so...

I never wanted to change my older Clipper code to use SET TAG for changing the indexorder, so I kept and keep on using SET ORDER.
Using the function IndexOrd() I can save the current Index order, and later set it back with

SET ORDER TO (was_ord)

s-)

HTH
TonHu

 

[clip4ever]

Back to my issue, the condition I'm trying to accommodate in CDX is that at cdx creation time I have the order of indexes as TAG1, TAG2, TAG3. However, at application startup I want the controling order of indexes to be (tag1, tag2, tag3) or (tag3, tag2, tag1) depending on a condition,
so in the code when I say "SET ORDER TO 1" it will mean either TAG1 or TAG3 is the controling index, depending on the same condition. Similarly, "SET ORDER TO 3" means TAG3 or TAG1 depending on the same condition.

So the question is how do I change the order of the TAGS (equivalant to indexes) in the CDX to have later "SET ORDER TO 1" tomean different things depending on the condition ? Is it by creating 2 CDXs or what ?

Am I off base again to deserve a hammer on the head (I like this icon) or may be I did not understand the CDX concepts ?

Clip4ever

 

[TonHu]

I'm not quite sure you understand the issue of multiple indexes on one dbf. ;-) (no offence, fun intended)

Ok, the way I, myself, have solved this issue in the past is to use a variable for the index, so depending on some condition I give ixVar a value, 1 or 3 whatever, and then have the statement

 set order to (ixVar)

determine what index tag I have currently active.
I decided long time ago, that to be able to maintain working sources and programs, I would have to be rather clear on the indexes used on any database, and especially that the index tag-order _MUST_ be the same in every instance of sourcecode referencing that dbf. This way, if I

set order to 1

, I can always be sure that a

seek UPPER(LASTNAME)

wil work on that particular index-tag! This 'only' has to do with code-maintainability, as you already mentioned, it's an older app, so many years have probably past since the original code was written, and maybe it wasn't even written by yourself! (no offence, I work in a software department where developers come and go, and applications have a lifespan of 20+ years...)

If this is a bit tough to read, I'm sorry, I just pounded away s-)

Need any special funny icons, just click on the Emoticons/Smileys link at the bottom of the reply message box...

HTH
TonHu