.DBF index design (performance question)

[SNusa]

Two ways of doing the same thing, which one is faster/better/preferred for .DBF's in terms of performance considerations?

Option A:
Create one single index (based on two fields in the table) ie: set the index order expression to: ALLTRIM(KEYFOB) + "/" + ALLTRIM(INVOICE)

Option B:
First create an additional calculated field in table (based on two these two fields above)
(And then assign the index order expression to this one single calculated field.....

In option A, the index contains the expression.
In option B, the calculated table field contains the expression.

Is it more efficient to make an index out of two separate fields, or first combine these fields using a calculated field?

Also, I'm wondering how either of these two scenarios would apply with regards to using Alpha DAO active linked SQL tables (and also the implications of "LQO Optimization?"
(In the two methods above, all data and field types are type text/character.)

[Ted Giles]

Re: .DBF index design (performance question)

Personally, I'd go for option B.
Reason. Indices are ok, but work best when they don't have to go through another process like an expression.
The data in the Calculated Field will be a fixed value, and you can test that value.
Where possible, an index with as few characters as possible, so can you reduce the extraneous stuff like "/", which are a pita!

[SNusa]

Re: .DBF index design (performance question)

Personally, I'd go for option B.
Reason. Indices are ok, but work best when they don't have to go through another process like an expression.
The data in the Calculated Field will be a fixed value, and you can test that value.
Where possible, an index with as few characters as possible, so can you reduce the extraneous stuff like "/", which are a pita!

I was wondering about that (and new the first part about the "extra process performance hit."
But what about the calculated field? Doesn't that extra calculated field become re-evaluated "perpetually?"
Or does it only change when the fields (from which it is calculated) are actually created and then thereafter only if/when they get changed?
And if it is perpetually "re-evaluated" (whenever the data is "referenced") but there is no change, does this mean the index does not get updated?

I remember reading somewhere in these forums that calculated fields can cause problems when certain calculations (like date() and now() are used) ~ which in this instance they obviously are not. Nevertheless, this got me to wondering exactly when these fields are "evaluated."

So I guess the another related question is: Exactly when do calculated fields (in tables) actually get updated? Is it only when a field is initially populated. (And if not, does each evaluation result in index updates if no changes in values are made to the calculated field?)

Note: I'm pretty certain that using "default field values" won't work here, because the data is imported, not "entered" by a user.

[Ted Giles]

Re: .DBF index design (performance question)

So I guess the another related question is: Exactly when do calculated fields (in tables) actually get updated? Is it only when a field is initially populated. (And if not, does each evaluation result in index updates if no changes in values are made to the calculated field?)

I believe that the calculated field in a Table (not on the screen) gets updated when the components change. No change, no update, so it's a one time operation unless one of the fields is updated.
You can try this by creating a calculated field in a table, do NOT run the Rules Update - and start entering data. Only the new data will be used in the calculated field.
Indexes will be updated on change of value in a field, so either way, the overhead is the same.

[pboomwork]

Re: .DBF index design (performance question)

Alpha Five has a undocumented function it uses for building compound index's quickly,

*concat keys(Field1,Field2,Field3....)

You can see it used in some of the indexs Alpha5 builds for linking sets etc.
It will build a key that will properly sort numeric and text values but do so very quickly.
Using this function indexs based on multiple fields can be built with little additional overhead.

After some work with the function I was able to figure out the following documentation.

'*concat_keys only work on field values it does not work on variables.
'The seperator character is only added to fields under the defined field
' length after the a rtrim is performed on the value.
'Leading spaces are retained trialing spaces are trimmed to make keys smaller
' when trialing spaces are trimmed the seperator character is added.
'The seperator character is a ASCII value of 2. It can be generated with
'the expression chr(2)

'Numeric values are treated differently again. The trialing seperator character
'is always added. A letter prefix is added that denotes number of digits.
'In this way numerica value are sorted correctly but leading zeros are not required.
'
' 1 - 0
' 12 - a12
' 204 - b204
' 1078 - c1078
' 40997 - d40997
' and so on....

'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'! *concat_keys only work on field values it does not work on variables !
'! we are recreating key that Matches one created by it !
'! the seperator character is only added to fields under the defined field length !
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Sample index defination
'Table: a-patients
'Index: _PIDsitecd
'Expression: *CONCAT_KEYS(PATIENTID,SITERETRIEVEDFROM,CODE)

'Lookups Keys for these indexs can be easily built if
'the source values are fields of the same length


'Lookup Keys can be built using variables
'using the following expression
'it is assumed each variable has all trialing spaces removed
'in this sample the DstSite is orginated as number that will always
'be shorted then the field that that orginated the number
'therefore seperator will always be added in index

'Where ID and DstCode is character string of up to 8 characters long
'Site is a single digit numeric value

SP = CHR(2)
Key = ID + IIF(len(ID)<8,SP,"") + alltrim(DstSite) + SP + DstCode + IIF(len(DstCode) < 8,SP,"")

[SNusa]

Re: .DBF index design (performance question)

This is good to know! (Wish I had learned this earlier/prior to having delved in as deeply to my "lifelong project" as I already have...... LOL)
Good thing is, this change is fairly simple as the way I use combined indexes (combined field method is what I chose) only effects hand written code (and a few calculated fields)....

@Paul - Just a thought here relating to you index naming conventions...

You're using Index: _PIDsitecd
From all that I've read here over the years, you're better off naming indexes with an underscore at the end of the name, not the beginning! ~ I use 2 underscores at the end so my created indexes stick out! Reason being is that Alpha has been known to truncate indexes. Someone please correct me, but I believe that 10 characters is the max before you have to worry about this happening. (Maybe 8 even????)

TIP: I've also adopted the habit of using the field name as the index name (without the vowels unless it's the first letter) to shorten it down/belo to the 10 character limit.
(I always shoot for 8 just to be "safe.")

On that note: Anybody know whether indexes are still getting truncated? And if so is it at 10 chars, or 8?

[Tom Cone Jr]

Re: .DBF index design (performance question)

Robert,

I think Alpha uses the FoxPro 2.6 format for DBF, FPT and CDX files. Indextags are limited to 10 chars in width in the Foxpro system. Alpha supports longer indextag names by storing part of the tag name in the dictionaries supporting the DBF file to which the CDX is attached. Truncation occurs when the DBF and CDX files get separated /disconnected from their dictionaries. The same issue will occur with fieldnames in the DBF files themselves. Long fieldnames in the DBF are stored in the supporting dictionaries.

If you use long fieldnames and long indextag names Alpha must do a bit of extra work each time data entry occurs. The supporting dictionaries must be searched for the correct "long" fieldname or indextag names in order to complete data entry to the DBF and update the CDX. This happens quickly of course, but can be eliminated entirely by using short field and tag names.

[SNusa]

Re: .DBF index design (performance question)

RE: *Concat_Keys()

Is this function being used to define an index definition within the table itself?
I'm hoping it's suitable for generating an index that combines two fields (both character fields) where all values in each respective field are equal in length.

If so I"m thinking about defining the indexes so that I can retrieve data using the following methods/functions:

vc_Value = lookupC() ~ retrieve value in table
vn_Results = <obj>.fetch_find() ~ to if/see how many entries in query exist

Wow, just realized I can do this with a set too? (Open a set, and use set.fetch_find())..... Generally speaking, Is opening sets like that even a good idea? (If so, only for reading and not writing data?)

[SNusa]

Re: .DBF index design (performance question)

Robert,

I think Alpha uses the FoxPro 2.6 format for DBF, FPT and CDX files. Indextags are limited to 10 chars in width in the Foxpro system. Alpha supports longer indextag names by storing part of the tag name in the dictionaries supporting the DBF file to which the CDX is attached. Truncation occurs when the DBF and CDX files get separated /disconnected from their dictionaries. The same issue will occur with fieldnames in the DBF files themselves. Long fieldnames in the DBF are stored in the supporting dictionaries.

If you use long fieldnames and long indextag names Alpha must do a bit of extra work each time data entry occurs. The supporting dictionaries must be searched for the correct "long" fieldname or indextag names in order to complete data entry to the DBF and update the CDX. This happens quickly of course, but can be eliminated entirely by using short field and tag names.

The "magic number" is 10. Thanks Tom.

[DaveM]

Re: .DBF index design (performance question)

Talking about LQO:
Who started that? I was of the thought it was started with clipper87 and borrowed by FoxPro prior to ms acquisition of FoxPro and the whole thing started with another database type that is not used anywhere now to my knowledge. I do not remember the other database type, but is was super quick. It was limited as to record count though.

Got it: BTREE or Btrieve

[SNusa]

Re: .DBF index design (performance question)

Talking about LQO:
Who started that? I was of the thought it was started with clipper87 and borrowed by FoxPro prior to ms acquisition of FoxPro and the whole thing started with another database type that is not used anywhere now to my knowledge. I do not remember the other database type, but is was super quick. It was limited as to record count though.

Got it: BTREE or Btrieve

Btrieve was pretty darn reliable. (At least from my perspective as a software user.) I never worked with it on the coding side.
But I spent the better part of 20 years using an accounting program that was Btrieve based.

My "lifelong winter/seasonal project:" (All the difficult stuff is behind me as of this evening!!! LOTS of trickery and xbasic on this monster form!)
This beast was a "mathematical nightmare... So much data to process and a very complicated form doing many things...
All hand coded xBasic, and virtually everything (including button text) is dynamically driven via data tables.
Have to clean up and "lock down" the form.... Create a few simple forms for editing the setup tables too. (and finish the last two tabs for import/admin etc.)
And replace a few "hard coded spots" with user defined variables....
~ Relatively speaking, not much to it!

Capture2.PNGCapture3.PNGCapture4.PNG

[MoGrace]

Re: .DBF index design (performance question)

I use a Btrieve database - sorry to say. It is however very reliable and very fast, just limited in what it can do. And finding anyone who can develop in it - impossible!

[DaveM]

Re: .DBF index design (performance question)

The company I worked for was using it before I got there. We still had to use it for a while. It was built with ms version of basic as the front. The only complaint ever was that it was difficult to work on and it was limited by size.
We were moving to Visual basic and ms access tables with the new desktop app to replace the old and adding crystal report writer(my job along with sales and installation).
I built over 1200 reports for the company and averaged 3 sales and installs per week.

The translation from the Btrieve to access for existing customers was huge and very difficult.

[MoGrace]

Re: .DBF index design (performance question)

Translation is possible? I am happy to be converting text files from its reports into importable data...

[DaveM]

Re: .DBF index design (performance question)

Robin,
The programmer in charge of that at the time, wrote some code that would be run at the client's site that would import the betrieve into the access tables. I know it was difficult to build, but it was not in my area of work. He passed away in 2003. I remember that he used a 2 step process like, to text and then to access.