Speed problems with string contains search.

[LindaRichard]

My question is more of a "what is VFP doing?"

For example, I have been performing a search of a character
field, named sampleno. This field is indexed and a common
search would be to find a sampleno that contains "ABC".
The database is located on a server and this search is
run from a local machine in this office. The search would
return a view of 5 records within a few seconds.

We now have started accessing this database from a machine
located in another office and have encountered serious speed
issues. This search that took only a few seconds within
our office, now requires up to 15 minutes to complete.

We have performed many test to improve the performance
between offices and have noticed a particular behaviour
in VFP. When this SQL command is executed a tremendous
amount of information is transfered from the server to
to local computer eventhough a view of only 5 records
is returned. Does anyone have any idea of what is going
on? This seems to be the reason for the performance issue.
When you ran in house the amount of info transfered did
not have much impact because the internal network could
handle it. It is a real mystery as to what VFP is
transfering between the server and local machine during
the execution of this SQL.

Thanks for your help
Linda

 

[Mike Lewis]

Linda,

The behaviour you are seeing is exactly what I would expect.

The root of the problem is in your statement: ... and a common search would be to find a sampleno that contains "ABC" The problem is the word "contains". That suggests that you are searching for ABC as a substring within a character string. You are presumably using the $ operator, or AT(), or something similar.

If that is correct, the problem is that you are running a non-optimisable query. It doesn't matter how you set up your indexes, VFP will have to look at every record before it can find (or fail to find) the few that you need for your view. Since the record selection takes place on the client machine, every recofd in the table will have to come across the network in order to finish the job.

Presumably, the only reason you didn't see this problem when you were running locally is that the local network is quite fast -- especially compared to the link from the remote office.

If you could change the requirement so that you were looking for strings that begin with ABC, and if you set up an index on the relevant field, you would find it very much faster. Presumably you can't do that.

The only other possibilities are (i) write a DCOM component that runs the record selection on the file server, generating a temporary table that you can then download across the network; (ii) switch to a client/server database such as SQL Server -- a very expensive and time-consuming alternative. (iii) Explore the possibility of running the job in the main office and finding a way of sending the results to the remote office.

Sorry I couldn't give you any better news.

Mike




Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[LindaRichard]

Hi Mike,

Thank's for your answer. This is not the first time
that you have answered one of my questions and I
very much appreciate your responses.

Your right, not the answer that I was hoping for, but
now I can concentrate I finding a solution. I will
go with a local copy of the database which get's
periodically synchronized with the server database.

Thanks again

Linda

 

[GriffMG]

Linda,

There are a couple of other alternative - one of which would be to use a 'remote control' program like pcAnywhere, or the free system from AT&T called VNC.

HTH

Regards

Griff
Keep [Smile]ing