>Hi Everybody,
>
>I need to speed up a query that I use because it takes around 26 seconds to pull out 32 records from a table that has 26,896 records. Below is the query that I am using:
>
>*-- This Query takes 26 seconds for 32 records
>SELECT table1.*;
> FROM (tmp_name) table1 ;
> inner JOIN (cAliasName) table2 ON table1.partno == table2.partno .AND. table1.Suppid == table2.Suppid;
> INTO CURSOR tmp_cursor
>
>*-- This Query takes .3 seconds for 64 records
>SELECT table1.*;
> FROM (tmp_name) table1 ;
> inner JOIN (cAliasName) table2 ON table1.partno == table2.partno;
> INTO CURSOR tmp_cursor
>
>*-- This Query takes 1.06 seconds for 1296 records
>SELECT table1.*;
> FROM (tmp_name) table1 ;
> inner JOIN (cAliasName) table2 ON table1.Suppid == table2.Suppid;
> INTO CURSOR tmp_cursor
>
>The structure of table1 (tmp_name) is Date D, Suppid C(10), Partno C(17), e_0199 C(10), q_0199 I, etc.
>
>The structure of table2 (cAliasName) is Primekey I, Zipfield C(27), Suppid C(10), Partno C(17), etc.
>
>I have an index on partno, suppid and partno + suppid on the table1 table.
>
>Please note that table1 is in a Visual FoxPro 5.0a data structure and table2 is a free table created on the run. I have also tried creating indexs on partno, suppid and partno + suppid on the table2 table.
>
>Thanks,
>Joseph Schaff

Joe --

Thanks for the rundown on what you've tried. Hardware also could be an issue, unless this query is running noticeably slower than others.

I'd suggest using a "=" rather than "==" for your join condition. In earlier releases, the double equal sign was not optimizable, and this is probably still the case. Your join should work just fine with that change.

Jay