Query multiple table relationships subquery?

[anna_717717]

Hi All,

Hope someone can help me with this. I got a series of tables that i would
like to join with a query. They detail the parameters for types of parts.
Ideally i would like the relationships to be defined and then choosen by the
user via a form and then applied. The relationships govern how the parts can
fit together. I would then apply a filter to the results to show the best
match for a customer.


I've got 8 tables and have started with 5 tables to see if i can join them
as below:

SELECT table1.ID, table2.ID, table3.ID, table4.ID, table5.ID
FROM table1, table2, table3, table4, table5
WHERE (((table2.ID) In (SELECT ID
FROM table2 AS Dupe1
WHERE Dupe1.[column3] = table1.[column5]))
AND ((table3.ID) In (Select ID
FROM table3 AS Dupe2
WHERE Dupe2.[column2]=table1.[column4]
AND Dupe2.[column3]=table1.[column6]))
AND ((table5.ID) In (SELECT ID
FROM table5 AS Dupe3
WHERE Dupe3.[column2] = table1.[column7] OR Dupe3.[column2] is
null)) AND ((table5.ID) In (SELECT ID
FROM table5 AS Dupe4
WHERE Dupe4.[colunm3]<table1.[column8])))
ORDER BY Table1.ID, table2.ID;

The query seems to return the right values (about 5.5million records!) but
is VERY slow. Is this the right approach for relating the records? Is it
possible to speed it up?

Thanks

 

[John W. Vinson]

Hi All,

Hope someone can help me with this. I got a series of tables that i would
like to join with a query. They detail the parameters for types of parts.
Ideally i would like the relationships to be defined and then choosen by the
user via a form and then applied. The relationships govern how the parts can
fit together. I would then apply a filter to the results to show the best
match for a customer.


I've got 8 tables and have started with 5 tables to see if i can join them
as below:

SELECT table1.ID, table2.ID, table3.ID, table4.ID, table5.ID
FROM table1, table2, table3, table4, table5
WHERE (((table2.ID) In (SELECT ID
FROM table2 AS Dupe1
WHERE Dupe1.[column3] = table1.[column5]))
AND ((table3.ID) In (Select ID
FROM table3 AS Dupe2
WHERE Dupe2.[column2]=table1.[column4]
AND Dupe2.[column3]=table1.[column6]))
AND ((table5.ID) In (SELECT ID
FROM table5 AS Dupe3
WHERE Dupe3.[column2] = table1.[column7] OR Dupe3.[column2] is
null)) AND ((table5.ID) In (SELECT ID
FROM table5 AS Dupe4
WHERE Dupe4.[colunm3]<table1.[column8])))
ORDER BY Table1.ID, table2.ID;

The query seems to return the right values (about 5.5million records!) but
is VERY slow. Is this the right approach for relating the records? Is it
possible to speed it up?

It isn't the right approach, and it should be possible to speed it up. Putting
appropriate Indexes on Column2 and Column3 would make a big difference; you
should also use the JOIN clause rather than your current Cartesian join.
What's making it slow is that your FROM clause is giving you *every possible
combination* of the records in the five tables - if each table contained 1000
records you'd be getting 1,000,000,000,000,000 rows!!!

I'm not sure I understand what you're trying to accomplish with all the
subqueries. Could you JOIN Table2.Column3 to Table1.Column5, and
Table3.Column2 to Table1.Column4, etcetera? With proper indexing a JOIN will
be vastly faster than all the subqueries.