We have a table one two different Accounts, one with about 1,000,000 and one with about 5,000,000 rows. There is one Btree Index on each table on an 'order_date' date field. The index was last rebuilt in May 2003 on the larger and yesterday for the smaller.

Our monitoring logs show that on Oct 10th, we could select the larger table in about a minute. Starting on Oct 12th, the time to select this larger table jumped to 40 minutes. The smaller table is likewise having a similar jump, but up to about x10 the elapsed time to select.

Our select statement is the same for both tables:

"SELECT table WITH datefield LE today"

Also now when we select the table, the 'Updating Btree for date_field….' message appears in on the Status Bar, but unlike an actual indexed based select, if we press 'Esc' the Select statment is interupptable via the "Do you want to cancel this process" message box.

BUT if we use this select statment:

"SELECT table WITH datefield EQ today"

This select uses the index and not interuptable!

Each of these tables is on a different v5.x Netware server, each server with its own v1.5 LH-NLM (ipx). We use Win2K as a client. We have not updated our Win2K clients since 9/1/04 (shame on us). Our Netware client is 4.90.0.0 SP1a.

Any ideas on what to look into further?

Where are the DOS sort files set to? If they're on the network drive I would definitely log into Novell and run a PURGE /ALL (or use the Client 32 PURGE utility). The Netware file directories tend to get clogged with deleted file entries and you see unexplained slowdowns like this until you run a purge. You may consider doing this on a monthly basis to clear out old spooler and other temp files.

What the dedicated indexor machine is showing?

It seems to me, that the index either was not rebuild or the indexor is unable to index at time.

Is there a big amount of "batch processing" preformed 'immediately' before select?

I cannot explain the difference between LE/EQ selects.

(I suppose, you remove records from the table after processing or at least change the stored date to "infinity")

Good catch. We solved this problem by setting the sort files location to the pc's local hard drive.

This setting is unchanged for years and the pc's that run our processes where all wiped and re-installed with Win2K in Aug to Sept and so 'leftover' sort files are non-existant at this time.

*What the dedicated indexor machine is showing?*

We don't use a dedicated indexer, and all pc's do not update indexes (except prior to a Select statement). We solved the indexing problems years ago by having specific programs Index.flush the specific table(s)after updating them.

*Is there a big amount of "batch processing" preformed 'immediately' before select?*

Good catch. I am adding additional logging information to several batch jobs to track Index.flush times more specifically.

*I cannot explain the difference between LE/EQ selects.*

Me either.

HOWEVER adding 'CASEINSENS' to the select fixes this problem, even though one table on one account has the index set to Case Insensitive and the other table on the other account has the index set to Case Sensensitive. Now both tables use the the index.

*(I suppose, you remove records from the table after processing or at least change the stored date to "infinity")*

YES - this is a 'future orders' table, and once these records become real orders, they are deleted from this table and written into another different 'orders' table. Also these 'future orders' are deleted if the customer cancels with us.

I would run the purge too. All those deleted file entries slow file writes done tremendously.

*What the dedicated indexor machine is showing?*

]We don't use a dedicated indexer, and all pc's do not update indexes ](except prior to a Select statement). We solved the indexing ]problems years ago by having specific programs Index.flush the ]specific table(s)after updating them.

It surely depends on application (on ratio of batch processing to direct inputs). But the deffered (idle) indexing is one of the biggest pros for hashed databases. If the indexor becomes busy too often (not our case), one can distribute the job among more indexors … you must only change the "attach strategy" to make sure they index different tables/volumes.

We do index.flush during batch processing (indexor is swithed off), but a lot of work was saved before it.

Do you remember reasons why you don't use dedicated indexor?

*Is there a big amount of "batch processing" preformed 'immediately' before select?*

]Good catch. I am adding additional logging information to several ]batch jobs to track Index.flush times more specifically.

This is what I neednot care of as I use the dedicated indexor.

Hippo

I wasn't at this company when the dedicated indexer was switched off, but the story is that there where just too many "problems".

Once the Index.flush(es) were added to each batch job, the index updating became part of batch task, and the "problems" went away.

Problems where record locking conflicts and index corruption.

They did only have one dedicated indexer to update all the tables, so it often had many, many pending index updates waiting.

We have discussed using several dedicated indexers per your suggestion.

Once I was in "too many problems" situaton with indexing …. I cut a big table without removing indexes. … after several days of problems I rebuild the indexes on the table manualy and from that time its OK.

… This is example, what you cannot left on dedicated indexor.

May be there can be other problems I just don't know about.

View this thread on the forum...