Slow issue with filepro on Windows 2003 / XP clients - Just Started

[Nancy Palmquist]

Henry Melancon wrote:
> Thanks Nancy.  My speed is not unbearable, but it should be running
> faster than what it is.
>
>   
Henry,

Got the solution to our issue.  Remember I said we had a file of 
checks/charges with over 3 million records.  We set up a quick process 
with a @key table to add 8 checks/charges to the file on each hit and 
time it.  Opened a couple of sessions and started doing tests.

Generally took 4 sec to add 8 records if only one person was doing it.  
If I added a second person at the same time it dropped to 45-60 secs do 
do the same thing.

We started removing indexes.  Got to Index D and it was wonderful at 
less than 1 sec for one or two people, just what we wanted.

Looked at the indexes and put back one that we had deleted and still fast. 

It turns out that index A, C, and D are built with the first key blank 
in a number of cases.  For example, index A is check number.  The file 
has both checks and charges and the charges do not have numbers, they 
have check numbers that are blank. 

Index C is built on Payor - that is the person that the check or charge 
is sent to.  Well that is usually known but we get some billing were the 
payor info is missing and the file is tracking the fact of the charge 
the payor is not always critical.  We want to be sure to bill the 
customer the fee, even if the source is unknown.

Index D is built on a flag that indicates if the check was recorded in 
error and is a reprint.  Sometimes they put the wrong checks in the 
printer and when they void them, they can indicate that the check number 
can be reused since the first check was not really printed.  The field 
is only one character but on all but 2 checks in the file, it is blank.  
I can do some reprogramming and probably loose this index but that is 
not urgent if we get the file working.

We archived 1.4 million records, then I shrank the file to just the 1.5 
million that remains and it works beautifully.

So maybe that is your solution also.

I always try to avoid indexes with blank keys - try to make them unique 
in some way by adding secondary sorts.  But it was just not possible in 
this case.  Had to have the indexes as they are on the most part to get 
stuff to work.

Good luck.

Nancy

>  
> Henry W. Melancon
> Documentation Supervisor
> Quality Control Department
> Gulf Island, L.L.C.
> 583 Thompson Road
> Houma, Louisiana   70359
> (985)872-2100 Ext. 1380
> (985)917-3380 Direct Line
> (985)855-3311 Cell
> (985)868-8527 Fax Line
> hemelancon at gifinc.com
>  
>
> -----Original Message-----
> From: Nancy Palmquist [mailto:nlp at vss3.com] 
> Sent: Tuesday, January 05, 2010 3:23 PM
> To: Henry Melancon
> Subject: Re: Slow issue with filepro on Windows 2003 / XP clients - Just
> Started
>
> Henry Melancon wrote:
>   
>> Nancy,
>>
>> I have a similar setup and speed problems too.  My IT Department has
>>     
> not
>   
>> been much help.  Let me know how you solve this one if you do not post
>> it to the FilePro List.  I cannot email the list only receive.  My IT
>> cannot figure that one out either.
>>   
>>     
> I will keep you posted.  We think there is some issue with the 
> particular file.  More testing tonight after users leave.
> Nancy
>   
>>  
>> Henry W. Melancon
>> Documentation Supervisor
>> Quality Control Department
>> Gulf Island, L.L.C.
>> 583 Thompson Road
>> Houma, Louisiana   70359
>> (985)872-2100 Ext. 1380
>> (985)917-3380 Direct Line
>> (985)855-3311 Cell
>> (985)868-8527 Fax Line
>> hemelancon at gifinc.com
>>  
>> -----Original Message-----
>> From: filepro-list-bounces+hemelancon=gifinc.com at lists.celestial.com
>>
>>     
> [mailto:filepro-list-bounces+hemelancon=gifinc.com at lists.celestial.com]
>   
>> On Behalf Of Nancy Palmquist
>> Sent: Tuesday, January 05, 2010 2:35 PM
>> To: filePro List; FilePro Support - # 14467
>> Subject: Slow issue with filepro on Windows 2003 / XP clients - Just
>> Started
>>
>> Guys and Gals,
>>
>> Filepro 5.0, Windows 2003 server, XP Clients
>>
>> ****  NOTHING HAS CHANGED in the indexes or programming related to 
>> database for months *****
>> Customer does not know of any change on the OS or Network.  We tried
>>     
> it 
>   
>> from a computer with the virus stuff off, just to be sure that was not
>>     
>
>   
>> the issue.
>>
>> Looking for a reason that filepro should start to take 4 to 30 seconds
>>     
>
>   
>> to add a record to a file.  This behavior started yesterday. 
>>
>> The file has 3.0 million checks/charges, and has .5 million free
>> records.
>>
>> First thing we did was run freechn on the file.
>>
>> The indexes have been rebuilt  twice today and finally rebuilt after 
>> being removed first.
>> (File has 5 absolutely required indexes, one index that is optional.
>>     
> We
>   
>> removed the optional one with no improvement.)
>>
>> The server has been rebooted.
>>
>> Processes that add records to this file, seem to get slower and slower
>>     
>
>   
>> the more people are working with the check file.
>>
>> One person adds a record to this file in about 8 sec.  Two people and 
>> both people seem to take as much as 30 sec to add a record to the same
>> file.
>>
>> It goes downhill from there.
>>
>> Other processes that add records to other files do not seem to have
>>     
> any 
>   
>> issue.
>>
>> We have done everything we can think of to try to find out what is 
>> dragging it down.
>>
>> I generate a log file and exactly when a record is added to the check 
>> file is when I have jumps in 5 to 25 seconds in time.
>>
>> A routine that opens a file to a free record, loads some data and
>>     
> writes
>   
>> the record.  A total of 4 lines of code.
>>
>> Any one have any suggestion, we are desperate to get the users working
>>     
>
>   
>> again, at full speed.
>>
>> Nancy
>>
>>   
>>     
>
>
>   


-- 
Nancy Palmquist 		MOS & filePro Training Available
Virtual Software Systems	Web Based Training and Consulting	
PHONE: (412) 835-9417		   Web site:  http://www.vss3.com