Update Indexes

[Walter]

Every now and then I have to Update my indexes in a set of tables, because some child records does not display with the linked parent record.
Where will be the best place to update the indexes automatically (when the form is loaded or what?)

And , do I have to do that in all the forms that are created from a set ??

Thanks

[JerryBrightbill]

RE: Update Indexes

Walter

The best solution is to determine why the indexes are getting corrupted. This is not a normal situation. If you have referential integrity turns on, try turning it off. This can sometimes cause the problem. Carefulling check the linking expression. It should be something very simple and not a field that might be edited in the child or record. Try to avoid expressions in the link. Filtered links can sometimes cause problems if there is a possibility of the filter not being evaluated.

There are very few cases where the child index may become unstable. Linking the same table 1 to many more than once with a filter on each link is one such case. I have an app that does this and I just add a maintenance option to a menu to allow the user to manually update the indexes. It happens rarely, but you do need a method to correct the problem.

Jerry

[Walter]

RE: Update Indexes

Hello Jerry,

This is a straight forward linkage, not editable by the user, referential integrity is turned off as well, and no filter exp. The link fields are numeric, same size.

But every now and then some child fields are not showing when I navigate the parent form.
Only after updating the index, they are back again.

Do you think a manual routine is the best way for the user
to update the indexes??

Thanks
Walter

[JerryBrightbill]

RE: Update Indexes

Walter

It is suggested that you don't use a numeric field as a link. You could convert the file to character and this should be much more reliable.

Normally set indexes are very reliable. Corruption of the index as you are seeing is very rare, especially if the link is a simple field expression.

Jerry

[CALocklin]

RE: Update Indexes

I will use the Use HTML option for this. I hope it works as expected.

I really wish I could agree with Jerry. I have an application in use that seems to need index updates about twice a day.

None of the indexes are based on numbers (e.g., master_nof is a character field) and there are no filters. Only one index is even "compound" in that it combines a character field and the CDATE() of a date field.

I have gone over every script to be sure all tables are closed before the script closes even though I'm told it shouldn't be necessary. I also don't recall any queries being used in the application.

The only other things that I can think of that might be affecting this are: (a) it's running on XP work stations with a Linux server and (b) the main input set includes 3 child tables and one grandchild. My next step will be to force the user to input data on a form based on one table at a time (actually, the child data is already handled that way) but this will also cause them more work and, based on past experience, may cause me more problems getting the 'view' form to update correctly.

I hate to sound negative but this is reality. It's what I'm living with every day.

In fairness, I must add that a number of other applications are running flawlessly but I have two that have been driving my crazy for months. Also, none of the applications have problems with 1 or 2 users. Problems only start when users begin to exceed 2 and seem to get progressively worse as the number of users increases. (Problems are rare with 3 users.) Re-booting and/or re-indexing has always solved the issues - unfortunately, it's only temporary.

It's probably also worth noting that these are probably my two most complex applications so it could be either (a) I've done something basically wrong somewhere or (b) all the complexity of checks and cross-checks are forcing tables to open and close too often - but isn't checking and cross-checking part of the value of a database application?

Set Definition:

Ord_Head
--> Client
--> Ord_Head_memo
==> Ord_Items
--> Ord_Item_memo


Ord_Head Indexes:


Agnt_Name_
AGNT_NAMEF
Ascending
All


Bill_Ph_
BILL_PHF
Ascending
All


Client_No_
CLIENT_NOF
Ascending
All


Master_No_
MASTER_NOF
Ascending
All


Prop_Addr_
PROP_ADDRF
Ascending
All


Rltr_Name_
RLTR_NAMEF
Ascending
All


Sign_Ph_
SIGN_PHF
Ascending
All




Ord_Item Indexes:


Comp_On_
COMP_ONF
Ascending
All


Master_No_
MASTER_NOF
Ascending
All


Mast_Date_
MASTER_NOF+CDATE(ORD_DATEF)
Ascending
All


Req_Date_
REQ_DATEF
Ascending
All


Sign_Type_
SIGN_TYPEF
Ascending
All


Work_Ord_
WORK_ORDF
Ascending
All


Ord_Date_
ORD_DATEF
Ascending
All

[drgarytraub]

RE: Update Indexes

Cal,

What you have said is reassuring to me in that I have been wracking myt brain to try to figure out why I get these index problems as well. I would say they occur only on the LAN and when 2 or 3 users are entering data. At those times, the secretaries sometimes will do a Ctrl-Alt-Delete (which I have told them not to do anymore so I can see the problem), but still I end up having to reindex.

Gary

[Elke]

RE: Update Indexes

Cal,

I too am having the same problems you described. Did you ever find out what was causing all the problems with the indexes?

Any help would be appreciated.

[rtbtat]

RE: Update Indexes

I am hardly a technical guru when it comes to computer related problems, but it�s been my experience that indexes become corrupt for a number of reasons that seem mysterious, yet there�s almost always a logical explanation. I tend to agree with Jerry, I don�t think there some inherent flaw in Alpha that results in index corruption. But then again, as stated above, I am not an expert in this area.

We can look at three general areas, application design, how people use of Alpha Five, and events that occur independently of Alpha Five.

I cannot remember all of the alpha based possibilities, but if memory serves correct, here is a partial list that was previously discussed in this forum.

� Using filters in indexes
� Xbasic scripts that leave tables open in the background
� Restructuring that changes the physical order of fields in a table
� Using numeric fields as the linking fields in sets
� Failure of developers to compact the database after making numerous changes
� And maybe someone can correct me on this, wasn�t there an issue using index names with more than 10 characters?

Although this isn�t supposed to happen, I suspect indexes can be corrupted when people fail to close their Alpha application properly [when a number of tables are still open] before closing the Alpha Five program.

Outside of Alpha Five, I would say the biggest and most likely cause of index corruption is a failure to shut down Windows properly. People do that all of the time either deliberately, or when they are forced to reboot their computers because of problems with other applications on their system. Very often, Alpha is still running when they reboot. I think this one causes more problems than people realize.

I would also suspect possible power surges and/or network related issues. I think Cal hinted at that with his complex application.

Robert T

[Bob Whitaker]

RE: Update Indexes

Sometimes reading this board makes the blood run cold as you spot a dire warning you haven't seen before i.e. dont use number fields to link tables in a set. I, in my ignorance, have done this for some time in the belief that an integer is very precise whereas a character can be miskeyed etc. I have to say we rarely get problems relating to the linking indexes but we do get corruption of our main table index usually in a multi user environment and it mainfests itself by truncating the index name. I am mainly referring to apps running under version 4.5 or less, we still dont have enough V5 apps out there yet to know if the problem is less evident although my general impression is of greater stability under V5. Can the alpha techies give us any definitive guidelines on the use of indexes.

Bob Whitaker

[JohnZaleski]

RE: Update Indexes

In a busy multi-user environment,entering child records ( in a form based on a set ) can easily produce index corruption. If you find it is necessary to do browse refreshes or other to properly display child records, you are a candidate for the situation I am describing.

My own experience have shown this to be irrefutable.This corruption will be more apt to occur during very busy periods - the lack of consistency makes it difficult to remedy.

I have seen this and completely fixed it by breaking off the child record from the set. Then by taking a different approach to enter the "no longer child records" the indexing problems immediately and completely dissappeared.

By the way - the problem had nothing to do with any other index suggestions made in this thread

[matthias kaelin]

RE: Update Indexes

Index-Corruption is often a problem when using DBF-Files in multiuser environments. This is specifically true with W2000/XP.

The simplest way to resolve most of these problems in W2000/XP is to disable oplocks on behalf of the LAN Manager server by manipulating the registry key.

My own experience shows no performance loss but significant less problems, when oplock is disabled.

Check (www.superbase.com/services_tech_support_oplocks.htm)


Matthias

[Elke]

RE: Update Indexes

Thank you all for sharing your experiences. I too was unaware of not indexing on a numeric field and will fix this evening but I don't really think that this is causing the problem. This database has been in use for 5 years and these issues did not arise until recently. I do think that it has something to do with entering child records (V5, did not happen in 4.5) and if this is the case I am throwing Alpha out the window. Cannot enter records in a child table, then what's the point?

I do not:
� Using filters in indexes
� Xbasic scripts that leave tables open in the background
� Restructuring that changes the physical order of fields in a table
� Failure of developers to compact the database after making numerous changes
� Use index names with more than 10 characters - Alpha does that all by itself when creating indexes.

[rtbtat]

RE: Update Indexes

Hello Elke:

You only listed a several design flaw possibilities that could result in corrupt files [indexes]. There are other possible and more likely causes that I mentioned in a previous message. Most of those are totally unrelated to Alpha Five and how the product is designed.

I can tell you that many people use A5 every day of the year, they enter tons of records into child tables, and their indexes remain intact and work just as they were designed.

I don't know if you're personally having problems with indexes, but don't judge Alpha Five by the small handful of people who post their problems. This occurs with every single application on the market, most people are very happy with the product while a small percentage experience problems.

That small percentage takes advantage of a forum such as this one to post their problems and rightfully so. But keep in mind they are but a small portion of the overall Alpha users market.

If you're having index related issues, you have to go down a list of possibilities and try to troubleshoot all of the possible causes.

Robert T

[Elke]

RE: Update Indexes

Robert,

I'm sure that many people do not have these issues but that doesn�t mean some obviously do, take a look at all the messages related to problems with indexes. I have been trying to find the cause for a week now, every night until 3AM, only to come in the next morning to have all of my employees unable to function due to the index problem. I'm sure that there is a very good reason why this is happening and it's likely that it is something that I have done, but not being able to figure it out or have more information when error messages appear is truly frustrating.

[CALocklin]

RE: Update Indexes

John,

This is getting a bit nit-picky but databases can often be nit-picky so I guess this question qualifies....

Have you ever seen an issue with entering data in a 1:1 child table? I suspect this is mostly a 1:M issue but thought it would be interesting to know if you have any further info on the subject.

'**************************

To answer Elke's original question:
No, I have not resolved all of the problems. Here are my observations for whatever they are worth:

- I have not seen any issues because of indexes on numbers in my apps BUT this type of index is rather rare for me because I adhere strongly to the idea that numbers are only used for data that will be used to add, subtract, multiply, or divide.
- Keeping the data ENTRY (not changes) out of the 1:M sets seems to reduce the number of index issues and, in most cases, it really isn't a big deal for users if set up properly - click a New Record button and open a form already in data entry mode. In fact, in most cases, I find it actually easier than a browse because anything over 4-6 fields can often be laid out in a more readable manner on a form. (I even add an OnKey function to open the data entry form by pressing F10 so keyboard users don't have to waste time constantly grabbing for the mouse.)
- Related to the above, I must admit that I have not changed those two problem apps to totally eliminate data entry in child tables. These are both relatively old apps built some time ago in A5v4.
- Other than John Zaleski, I notice a lack of people saying "I have 5 (or more) users and I don't have indexing problems. Here's how I do it...." Anyone else out there care to check in??
- I don't believe this is an Alpha issues as much as it is an issue that any file server database such as Alpha (or any .dbf based database) and others would be subject to. I think one way some of the other databases get around it is to use fewer indexes and more queries but, as we all know, the queries can take a lot longer; especially on a large table.
- Although I agree with most of Robert's comments, I'm convinced that the issues I'm seeing have nothing to do with crashes or shutting down A5 incorrectly. Too many problems have occurred after a couple hours of work with no other shut downs. Of course, shutting down incorrectly is likely to cause problems but I'm not sure it's a major factor in the case of multiple users entering data all at the same time.