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 2:45 PM
> To: Filepro-List at Lists. Celestial. Com
> Subject: RE: filePro speed, and indexes (GRX)
> 
> 
> [....]
> > > 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
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> http://mailman.celestial.com/mailman/listinfo/filepro-list