You may want to read my FAQ on Rushmore Optimization.
In your case, to improve the join speed:
Make sure the tables DO NOT have an index on deleted().
It might help to drop one of the indices in the tables, for the combined condition. Remember, a partial R.Optimization is sometimes faster than a full R.O.
Perhaps a join that uses a composite index might be faster. How about:
select (field-list) from table2 where partno+suppid in;
(select partno+suppid from table1)
I am assuming that table1 has only a small amount of records. For R.O., you will need an index on partno+suppid.
HTH, Hilmar.
>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
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
