Multiple join conditions on indexed fields VERY slow...

[kakmouth]

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.

 

[mkrausnick]

Yes, joins with multiple conditions can be slow on large tables. However, here are some ideas:

- Try putting Barcode on the left side of the join conditions since that's where you're selecting data from.
Also, if one table contains many more records than the other, put the table with the smaller number of records on the left side of the join condition.

- If you can, create a new field in each table that combines po_num and style so you're only checking one field.

- Read up on how VFP's SQL SELECT retrieves data - it's not entirely straightforward. Maybe there will be a clue that might help you re-organize the data for faster query.

- If this is a query you're going to run a lot, consider creating an intersection table that contains only po_num, style, and the primary keys from the two tables (basically make the join permanent). Then you'd be processing many fewer bytes to get to the result set. Then you could retrieve the remaining data with a SCAN loop or a second select query.

- Goes without saying, but there's no such thing as too fast a processor or too much RAM. If you're running over a network, that's a killer too. Juice the box.

I run queries on tables with several million records daily, and they can take several minutes to complete sometimes.

Hope this helps.



Mike Krausnick
Dublin, California

 

[baltman]

I also find that by pre-selecting just the data fields (perhaps with some pre-filtering) you need for the join(s) into their own temporary tables/cursors can save a lot of time.

I've run 20+ million record queries blazing fast because there were only 3 fields. Of course milage will vary depending on complexity.

Brian