filePro speed, and indexes (GRX)

[Chris Sellitto]

[....]
> > 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.
> 
> 
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> http://mailman.celestial.com/mailman/listinfo/filepro-list
> 

Thanks for the input John.  As far as the filling in of the field, I
actually did not use a date but an X and the record number concatenated
("X"{@RN) together.  The field was an already existing field of
10,allup, so rather than make a change to the database structure, we
just left it.  The actual information that we are putting in it now, is
just a sequential number.

Again, thanks for your advice.

Christopher Sellitto
VP Computer Operations
Guaranteed Returns