Unfortunately this particular search is not indexable, because you are looking for a substring within a field. If you could change the search expression to only have a wildcard at the end (as in "companyname starts with...") , then it would work fine. For example:
sCustomer = UPPER(FormField("wCustomer"))+'%"' SELECT companyname, companyno, industry FROM custdata;
WHERE companyname LIKE UPPER(FormField("wCustomer"));
INTO CURSOR rescust |
In this case all you need to do is create an index on companyname. Note that if your data is mixed case, then you will have to SET COLLATE TO GENERAL before creating your index.
I suggest that you try your queries in a regular VFP command window before inserting your optimized code in a FoxWeb script. For details on optimizing VFP searches please refer to the following chapters in the VFP documentation:
- Using Rushmore to Speed Data Access
- SYS(3054) - Rushmore Optimization Level
FoxWeb Support Team
support@foxweb.com email |
Sent by Fox JW on 06/17/2002 03:54:46 AM:
I used the following select statement :
sCustomer = '"%'+Upper(FormField("wCustomer"))+'%"'
set order to tag companyname
Select companyname, companyno, industry from custdata;
where companyname like &sCustomer;
into cursor rescust
Is there anything that I have missed out to speed up the search process?
Sent by FoxWeb Support on 06/17/2002 12:11:26 AM:
FoxWeb uses the native VFP engine, so you should be looking at the same techniques that would speed regular VFP searches. Actually a 500,000 record table is not that big. If you create your index tags properly based on the searches you need to do, you should be getting results in a fraction of a second.
For information on optimizing VFP searches please refer to the VFP documentation. If you get stuck, you can post your select statements here for additional help.
FoxWeb Support Team
support@foxweb.com email |
Sent by Fox JW on 06/15/2002 04:45:58 AM:
I am using FoxWeb 1.2x running on NT with IIS4 and VFP6.
I have created an SQL to search from a table with 500,000+ records. The table is stored in the same NT server where FoxWeb is installed. Unfortunately, FoxWeb returned "timout" error for every keyword search.
Is there any other method that can make FoxWeb handle huge data records faster?
TIA