filePro speed, and indexes (GRX)

[Chris Sellitto]

Hello All,

filePro 5.0.14DN9
Windows 2003 server
Windows XP workstations

I will try to make this brief.  I don't remember if this has ever been
discussed.  Hopefully if someone sees it, and never knew about it, it
will help them as it did me.  We recently had a situation where I was
making changes to an existing process that has been in use for the last
9 years with no problems.  Part of my new code called for creating a new
index on one of the files this process accessed.  The index was created
on an already existing field that had not been populated (it became
obsolete) in a very long time.  There are just over 700,000 records in
this file.  The process will add a new record to this file, at ESC
processing.  The problem that arose, was that it took a very long time
to complete a transaction, so that the user could continue and enter
their next record.  Normally, on average, it took about 2 seconds to
complete, and now it was taking about 15 seconds or more.

The Solution:
My colleague remembered hearing something about having a field in an
index that had a lot of blanks as being a possible cause for slowness.
Remember, we had not populated that field in a long time, so of the
700,000 some odd records over 600,000 had that field blank.  We decided
to populate the field (10, allup), from the current date back, with
dummy data ("X"{@RN) just to put something in the field (it is obviously
now being populated with real data).  After completing this task, the
speed of the process went back to normal.  Anyway, that is it in a
nutshell.  Most of you probably already knew this, but those of you who
did not, maybe it will help.

Thanks for letting me ramble.

Christopher Sellitto
VP Computer Operations
Guaranteed Returns