Sherie, For one I would evaluate the Right hand side of the "Where" before the SQL command and place it into a memory variable m.cTarget then issue:
Select * from List where <<..........>>=m.cTarget This will cut down the amount of processing that VFP will have to do on each of the selected records. At the moment I can't see any reason for the slowness between VFP9 and 7 but you may want to issue the Sys(3054,11) after the query to look at the optimization level. Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sherrie Binder Sent: 18 September 2007 17:22 To: [email protected] Subject: RE: VFPRO 7 -> VFPRO 9... SELECT is now slow Changing the TableValidate to 0 didn't make a difference. This is the index: INDEX ON ; PADR(SUBSTR(ZIP,1,5)+STRTRAN(TRIM(CHRTRAN(UPPER(ADDRESS)+UPPER(ADDRESS2),; "ABCDEFGHIJKLMNOPQRSTUVWXYZ,.;'#&[EMAIL PROTECTED]&*=+_:<>/\|()- ","")),'"',""),LEN(ADDRESS)) ; TAG numbers ADDITIVE The purpose is to find possible duplicates based in the 5 digit ZIP plus the numbers in the address field. This is the select: SELECT * FROM LIST ; WHERE PADR(SUBSTR(ZIP,1,5)+STRTRAN(TRIM(CHRTRAN(UPPER(ADDRESS)+UPPER(ADDRESS2),; "ABCDEFGHIJKLMNOPQRSTUVWXYZ,.;'#&[EMAIL PROTECTED]&*=+_:<>/\|()- ","")),'"',""),LEN(address))=; SUBSTR(m.zip,1,5)+STRTRAN(TRIM(CHRTRAN(UPPER(m.address)+UPPER(m.address2),; "ABCDEFGHIJKLMNOPQRSTUVWXYZ,.;'#&[EMAIL PROTECTED]&*=+_:<>/\|()- ","")),'"',''); INTO CURSOR cDupes Sherrie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Crozier Sent: Tuesday, September 18, 2007 10:58 AM To: [EMAIL PROTECTED] Subject: RE: VFPRO 7 -> VFPRO 9... SELECT is now slow Sherrie, What are/is the index expression and what is the actual SQL syntax you are using? Also have you got "Set Tablevalidate 0" to remove the header checking in VFP9 which is NOT a default setting Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sherrie Binder Sent: 18 September 2007 16:41 To: [email protected] Subject: VFPRO 7 -> VFPRO 9... SELECT is now slow I just moved an application from VFP 7 to VFP 9. In a select command, I have only one table involved with a WHERE statement. The WHERE statement includes a long expression which is exactly as it is in the index of the table. In VFPRO 7, the select takes .02 seconds. In VFPRO 9, the select takes 12.06 seconds. CPCURRENT() = 1252 CPDBF() = 1252 What am I missing? Thanks. Sherrie Binder Christian Record Services www.christianrecord.org *** Serving the Blind Since 1899 *** Confidentiality Notice: This message from Christian Record Services may contain confidential information. If you received it in error, please contact the sender by reply email and delete the message and any attachments. Sharing, printing, or using the contents is not allowed except by the intended recipient. Thank you. [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
