>hi,
>
>i'm attempting to join 2 fairly large tables (200,000 recs each) on multiple fields via:
>
>select b.* from detail d join barcodes b on d.po_num = b.po_num and d.style = b.style where d.wo_num = 123456 into cursor csrBcodes
>
>d.wo_num, d.po_num, d.style, b.po_num & b.style are all indexed, yet this query takes more than 30 secs. if i limit the join condition to a single field - either po_num OR style - the query runs in less than a second??!
>
>any thoughts? are multiple condition joins just slow??
>
>thanks in advance.

A potential problem is that VFP has to get all matching key values for each index; therefore, sometimes it may work faster by deleting or disabling individual indexes.

For example, there may be MANY matches for each po_num, and for each style.

I assume that the combination po_num and style will uniquely identify the record in one of the tables. Is this correct?

Whether this is so or not, your query will probably be much faster if you change it to something like:

select b.* from detail d join barcodes b on d.po_num + d.style = b.po_num + b.style where d.wo_num = 123456 into cursor csrBcodes

(This is assuming po_num and style are character fields; otherwise do the corresponding conversions.)

There should be a corresponding index on po_num + style.

The reason this should work faster is because there will be much less matches for each combination, than there are for the individual fields.

HTH,

Hilmar.