Inconsistent problem in finding a record...

[Menglish]

I have a client who has been running the same procedure for about four years. Now, when searching for an invoice number, it cannot find it. I can exit VFP (9) and reload and it will find it. If I repeat this procedure it may or may not find it again. It is very inconsistent.

I checked and downloaded VFP 9.0 S2 but still have the same problem. I carried the applications to another computer and do not have these problems.

I dont't know if it XP, FoxPro or a hard-drive problem. Any ideas on how to determine where the problem is?

(They only use this computer for e-mail and Visual FoxPro)

Thanks,

Millard

 

[GriffMG]

The most obvious idea is a corrupt index.

Can you reindex the table?



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Menglish]

Thanks Griff. I am using a simple locate with this routine so the index file is not used. However, I did check on this and they re-index their files weekly for the other routines.

Millard

 

[craigber]

LOCATE may use an index because of Rushmore.

My guess is there is a setting difference in config.fpw that is affecting the search. Search the drive for all instances of Config.fpw and compare it to the same file found on a working computer.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[Dan Freeman]

Many settings could affect this: SET EXACT is one of them. Various other settings can affect the value returned by STR() if you don't use all 3 parameters.

What is the exact LOCATE command that is failing?

 

[Menglish]

Hi Dan,

I do not have the code in front of me but it is: locate for m.invnum$invnum.

As it is, this program is working fine on my personal computer and has been working okay for about four years. I am suspecting Windows XP operating system or a computer hard-drive problem. (I have not reinstalled their FoxPro either.)

Thanks for all these suggestions.

Millard

 

[cricket]

LOCATE FOR UPPER(m.invnum)$UPPER(invnum)

This will avoid upper case /lower case problems

 

[JRB-Bldr]

To even better minimize the chance for problems when I resort to a LOCATE I often use something like:

LOCATE FOR ALLTRIM(UPPER(m.invnum)) $ UPPER(invnum)

That ensures that possible trailing blanks will not be included as part of the LOCATE and thereby cause problems.

Good Luck
JRB-Bldr

 

[Stella740pl]

I would use
LOCATE FOR AT(m.invnum, invnum)>0 for a case-sensitive search
or
LOCATE FOR ATC(m.invnum, invnum)>0 for a case-insensitive search,
instead of Rushmore non-optimizable $ operator (at least it was not optimizable up to at least VFP6).

While this might improve the performance if the table has an index on invnum, this is not the reason for the problem.

I would look again into indexing and SET EXACT settings (and, possibly other SETtings).

Since it sometimes finds it after you restart the VFP, maybe some settings are set as default when VFP starts, but something in the program changes it.

Or, maybe, you are looking into a wrong table, not the one you expect.

Did you try to use Debugger to see what's going on? Check there, what table is selected; what the exact values of both, m.invnum and invnum; what SET EXACT is set to, etc.
Also, does it happen after the user goes into a particular program branch that you don't when you restart VFP?

 

[Stella740pl]

Hi Mike,

I vaguely remember that we have already had this same conversation some time ago. Possibly even with time tests. Maybe I will search for it later. I seem to remember that while AT() is not optimizable, it's already optimal , and is faster than $ (but wouldn't bet on it until I either find that thread or do some time tests).

Anyway, it seems like performance (while always good to improve) IS NOT the problem Millard is currently having.

 

[Stella740pl]

Mike, while I found a few older threads discussing commands being optimizable, non-optimizable, or already optimal, with both of us participating, I didn't find test results supporting this one.

So I ran a series of quick test using a table with nearly 9 million records.


AT() is always faster than $ alone;

For case-insensitive search,
ATC() is always slightly faster than $ combined with UPPER();

For case-sensitive search,
$ is always slightly faster than ATC().

But, again, this is not the reason for the problem.
To solve the original problem, I would check for all the things that were mentioned before me and for those that I mentioned in my 19-Nov-08 9:45 post.

 

[craigber]

Stella740pl...

I doubt this is an index/Rushmore problem as the problem doesn't surface on other computers. That leads me to look at environmental issues outside the code/data. That's why I suggested looking at CONFIG.FPW differences.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[Stella740pl]

Craig,

I never suggested that this is Rushmore problem; in fact, I said just the opposite - that this is NOT the problem - while nice to know and use.

But I do accept the possibility of this being an index problem - but only under assumption that the table in question is local to the problem computer.

What I did suggest is to check for SET EXACT and other SETtings, and also make sure that the search is performed on the correct table, etc.

I also suggested taking a note whether that happens after some particular sequence of actions, like going into a particular branch of the application, which may reset the settings (which is maybe why sometimes it does find it after restarting the VFP/app - settings reset to a default, table is SELECTed again, etc.).

CONFIG.FPW? That's a possibility. But it probably wouldn't explain the inconsistency of the results.



Mike,

Millard IS having a performance problem if he's using $ or AT or ATC to search for an invoice number.

Well, if you say so :-D. I do get your point.