I know that this question has been asked before (and I have read some of the responses) but… "what causes the indexing linked lists to break?". I know that some of the possible culprits are: locking not happening correctly, using background indexing instead of a dedicated indexer, or having the "update indexes before filter" flag set to True.

Here is our client's sitiuation: two entirely separate copies of the same database, Production and Development, running on separate AREV.EXEs and separate code and data. The Production system uses a dedicated indexer, the Development system uses background indexing. Both systems have the "update indexes before filter" flag set to True for all users.

We have a process that imports (from an ASCII file) and writes (to AREV files) an extremely large number of records… so large that it runs for about 3 days even on a Pentium! So far, this process has only been tested in Development. On a mid-size data set (ie: running about 24 hours) everything works out fine without error. However, if we try to run for the entire 72 hours, we get a "linked list broken" error. To handle the backlog of indexing produced (all the files being written to are indexed), I had another workstation sitting idle in Development with background indexing turned on, effectively acting as a dedicated indexer (dedicated except that the workstation the process was running on still had it background turned on as well, but this should never start up if a process was running continuously on it, right?). The process running doesn't do any R/LIST statements, so the "update indexes before filter" flag shouldn't have made any difference. There are no locking problems that I know of and the c

orrect network drivers are being used. However, one possible clue is that the client is (in spite of our advice) NOT running the Novell NLM, yet we are running under Windows 95. So far, this has not caused any GFE's that we know of.

So… any ideas about what could be causing this "linked list broken" error? Are there any flaws in any of my assumptions above?

Peter Fisera

Synergy Computer Consulting

WOW! You must be running one load of records. My poor P200 just imported (not Arev) 1.013 million records in 15 minutes. On a pre-sized, non-indexed file, you ought to be able to do about 25% of that into an LH file (assuming a reasonable id distribution.)

Even that low rate suggests that you may well overflow the capabilities of a slow indexing machine. Have you considered partioning the workload over several machines? Say for example, have 3 machines importing every 3rd ascii record - and having each one do it's own index.flush() every 32,000 records or so? Or another set of machines updating one part of the total index load?

What about having the flush occur more frequently, say after every 100 writes? I have serious doubts about the ability of the secondary indexing system to handle 32,000 index updates, even though it has improved greatly from AREV 1.16 to 3.12. From the scenario description, I would guess that something in the machinery got behind, and left a boatload of updates to be done.

This highlights the strength/speed of hashing record access, but the weakness of storing index updates. They should be done by the same assembler code that performs the write, and at the same time. The cost/benefit of the overhead of doing it at the RBasic level in an MFS just doesn't work out.

Kenny

Thanks gentlemen, for confirming what I already suspected. However, getting the import process to do its own index flushing will slow it down even more, and there are some business reasons why we can't let it get even slower than it is. So how does this sound for a compromise: have one or two other workstations churning away in backgrounding indexing mode (but with very short delay before indexing and time between index checks) and also change the process itself to stop every 1000 records or so and index.flush, just to make sure that the other two workstations aren't swamped.

Just so you know, the reason this thing is so slow is that every record in the imported ASCII file translates to anywhere from 5 to 100 records in AREV, in a master file and three different subfiles, with relational indexing between master and sub files, and also many btree and xref indexes on both the master and sub files. So indexing overhead with each record written is HUGE. In addition, this runs on a very large and busy Novell network, and under Windows 95 with (as I mentioned before) no AREV NLM.

Peter Fisera

Synergy Computer Consulting

Have you considered:

1) Remove Btree, Xref indexes (consider relationals as well)

2) Perform update

3) Re-establish indexes.

The advantage of this technique is that a lot of overhead reshuffling leaves of the Btree is completely avoided because the Btree is established in its final state the first go round. Additionally, indexing overhead for the update process is eliminated.

Yes, I had considered that, but only as a last-ditch effort. The reason being that the data files are pretty darned big already and there are lots of indexes involved. Reestablishing all the indexes could be a 24-hour or more process. However, as you said, the flipside is that the actual data files writes would be that much quicker because of it… so it may save time after all (since the import is taking 72 hours). I wish we had some time to run some simulations here, because the math is complicated. The frustrating part is that all this effort is required on something that is a one-shot data conversion and only has to happen once (and we only have time to test it a couple more times before that)… it just has to happen as quickly as possible.

Any other suggestions from anyone?

I don't have any real suggestons to making the indexing go fater. One indexer will work just as well as 2, in fact with 2 you might end up with a locking contention. Just have it sitting in a perpetual index update loop 1 and 1 on the times.

To increase speed, before you start the process, you might want to remake the data and index files so they have tons of empty space. Be sure to set that sizelock to 1 so it doesn't start shrinking on you.

I'm actually with Victor (and was going to suggest it before I read his response) that you should remove the indexes first and add them back later.

You'll also make a big difference in using the NLM. The speed increase you'll get with be huge, not to mention the stability under Windows 95.

[email protected]

Sprezzatura, Inc.

View this thread on the forum...