>I have a problem with slow data extraction using SQL. I want to extract records from 'file2' that have codes that match those in 'file1'.
>
>* open a file to extract data from.
>use file2 in 0 alias file2
>
>* this is where the data will go.
>cMyextract = "extract.dbf"
>
>* create an array of key codes from another file (file1) - store in an array
>select distinct keycode from file1 into array myfilecodes
>
>if _tally <> 0
> select * from file2 into DBF &cMyextract where ascan(myfilecodes,keycode) > 0
> use
>endif
>
>I am no expert with SQL. Setting an index on the files doesnt seem to help.
>Especially slow on networks. THANKS!
Create an index on KeyCode in File1. If File2 is large, then an index on KeyCode on File2 will make the following command fully optimizable
In place of creating the array and running multiple SELECTs, issue the following command:
SELECT * FROM File2 ;
INTO TABLE (cMyExtract) ;
WHERE KeyCode IN (SELECT KeyCode FROM File1)
Don't bother with creating an array - with your code, there's no optimization taking place, and a relatively expensive ASCAN() is needed to run against each record. The use of the DISTINCT operator is also expensive.