filePro speed, and indexes (GRX)

[John Esak]

> -----Original Message-----
> From: filepro-list-bounces+john=valar.com at lists.celestial.com
> [mailto:filepro-list-bounces+john=valar.com at lists.celestial.com]On
> Behalf Of Chris Sellitto
> Sent: Thursday, March 29, 2007 7:27 AM
> To: filepro-list at lists.celestial.com
> Subject: filePro speed, and indexes (GRX)
>
>
> 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


No, what is known... at least by me, is that indexes with large numbers of
blanks and then just few "filled" fields used to be a cause for concern over
Deleted Key Not Found errors. Most of this, actually I believe ALL, of this
has been fixed, but what you are describing could be a hangover or even an
original part of the indexing routine. Incidentally, the DKNF problems with
large files and large numbers of blanks were more apparent with 1 character
indexes. Say for example you had a status field that was normally blank, but
on a certain few records it was changed to a "U" representing (U)nused or
something like that.  The original problems had to do with trying to handle
the massive amounts of "duplicate" records in a more efficient manner than
what would just be the *easy* way. :-)  Anyway, to maintain the superfast
speed of filePro indexes when working with lots of duplicates (in your case
BLANKS) this process needed lots of attention.  What I'm suggesting is now
that all those problems are fixed, perhaps, this slowness thing is a
hangover result and maybe FP Tech can do some more profiling to put the
speed back to normal when this situation occurs... which I can tell you
happens quite a bit. Even, with non-blank fields like 500,000 records all
with a "C"losed status and just a few new ones with "O"pen status, or lots
of other scenarios.

I can tell you that I have not seen the slowness you describe (and by the
way, I like your work-around... it's obvious, but I don't know if I would
have thought of it), but even so, you should submit this situation to FP
Tech... and if possible provide that exact file before you fixed it with
the filler dates.

John Esak

P.S. You mention the field is 10,allup but you are filling it with a date.
This certainly changes the indexing aspect of things and FP Tech will know
more about that when they investigate this problem, but why just out of
curiosity, aren't you using a 10,mdyy/ type edit? I think this would change
the situation a lot, but I'm not sure if it would alter the slowness thing.