Foxpro otimization 1

[vino10]

Good Day guys

Guys how can i speed up my query selection in our system, it takes more than a minute to process a single query we ony have 300,000+ records. My query is just selecting names with soundex equivalent.

 

[Mike Gagnon]

vino10

I would have been better if you posted the code you are using so we can see where it could be improved.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[rgbean]

Do you have an index based on soundex values?

Rick

 

[Mike Lewis]

Vino10,

It is difficult to give advice without seeing your code and knowing more about the tables and indexes in question.

In general, the best way to speed up a query is to ensure that you have an index on all the fields being selected on. Check the Help file for Rushmore topics.

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

 

[vino10]

Sorry guys, Heres my code

Note: I have index on last name,first name
Thanks

SELECT ALLTRIM(control_no) as control_no,;
ALLTRIM(c_lname) as last_name, ALLTRIM(c_fname) as First_name,;
ALLTRIM(c_minit) as MI, dob_dt as Birthday,;
ALLTRIM(c_adr1) as address1,ALLTRIM(c_adr2) as address2,;
ALLTRIM(c_adr3) as address3,ALLTRIM(c_adr4) as address4,;
ALLTRIM(c_zipcd) as zipcode;
FROM seektbl INTO CURSOR x01;
WHERE 1=1;
AND slname = SOUNDEX(xlname);
AND sfname = SOUNDEX(xfname);
ORDER BY c_lname,c_fname

 

[theThirdElement]

Hello,

Just wondering, I think it's quite dangerous and pointless to use alltrim() in a select statement. Because the cursor is going to have certain field width anyway and this could cause some fields to be accidentally truncated by too much.

Ed

 

[Mike Lewis]

Vino10,

OK, things are much clearer now.

Essentially, you need the following indexes:

SOUNDEX(xlname)
SOUNDEX(xfname)
c_lname
c_fname

I'n not sure what the difference is between xlname and c_lname. Presumably these are separate fields? Also, I don't understand why you have 1=1 in your WHERE clause.

Finally, I'd agree with Ed about the ALLTRIMs. These will cause each field in the result cursor to have the same width as the actual field contents in the first row of the result cursor, which is probably not what you want.

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

 

[vino10]

Thanks guys i think i see the problem, I think its because of the missing index for the soundex(c_lname) and soundex(c_fname).

Thanks guys You give me a very helpful Ideas.

By the way,I use the Alltrim() just to Eliminate the spaces in my field. Thanks anyway for the suggestions

 

[dbMark]

Remember, the SQL is in essence just creating a table holding the results, regardless whether it is in memory or on disk. The reason they said not to use ALLTRIM() is that the first record's contents can determine the size of each field in the query. For example, if the first record has a field "abc " that is trimmed, the output field for all records will be only 3 characters in size and a following record containing "abcdef" would be cut in half. That's a real but subtle gotcha! (If it created an array as output, that might be a different matter, but I haven't tested that...)

 

[craigsboyd]

Also, make sure that the SOUNDEX() calls are on the left-hand side of the expressions in the where clause.

http://www.sweetpotatosoftware.com

 

[bobmpalmer]

If you create the cursor using CREATE cursor x01 (feildname fieldtype fieldsize, etc) first before your SELECT then you can use alltrim()

Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[vino10]

Thanks guys,

I think all of you was right,So instead of using alltrim() can i just use the == operator to get the exact value.