I have a VFP9 Win App that uses the below query. How can I speed up this query?
The SYS(3054, 1) reports that "Rushmore optimization level is Full" for both tables. But the query takes about 5 seconds to execute. One table has about 600,000 records and the other has about 50,000. The number of rows returned from the query should be around 40.
Also, both tables have an index for "DELETED()" and each column being filtered has an index on that column.
Any suggestions?
Thanks,
Jerry
SELECT calls.*, ;
NVL(cust.custno, "Custno?") AS custno, ;
NVL(cpcust.id, "id?") AS id, ;
NVL(cust.phone, "Phone?") AS phone, ;
NVL(cust.name, "Name?") AS name, ;
NVL(cust.city, "City?") AS city, ;
NVL(cpcust.state, "state?") AS state, ;
NVL(cust.zip, "zip?") AS zip, ;
NVL(cust.terri, "??") AS terri, ;
NVL(cust.email, "email?") AS email ;
FROM calls LEFT OUTER JOIN cust ;
ON calls.custno = cust.custno ;
AND calls.id = cust.id ;
WHERE ;
BETWEEN(calls.date, {^2010/03/09}, {^2010/03/09}) ;
AND calls.user = 'USER NAME HERE' ;
ORDER BY calls.date DESCENDING, calls.start DESCENDING ;
INTO CURSOR mycursor