Join Problem

  • Thread starter Thread starter bill.peters
  • Start date Start date
B

bill.peters

I have an access app. that links via ODBC to DB2 tables. My query takes
so long that it ends up timing out. I think I can speed it up by
optimizing the joins.

I'm trying to get all the records in table1 and all matching records in
table2, table 3, and table 4. When I set up the joins in the Design
View, then look at the SQL, it looks like it reads table1 at least
twice. Here is the code.

FROM (TABLE3 RIGHT JOIN (TABLE2 RIGHT JOIN TABLE1 ON TABLE2.A_SSN =
TABLE1.M_SSN) ON TABLE3.F_SSN = TABLE1.M_SSN) LEFT JOIN TABLE4 ON
TABLE1.M_SSN = TABLE4.R_SSN

It seems like I should be able to do something like:

FROM TABLE1
LEFT JOIN TABLE2 ON TABLE2.A_SSN = TABLE1.M_SSN
LEFT JOIN TABLE3 ON TABLE3.F_SSN = TABLE1.M_SSN
LEFT JOIN TABLE4 ON TABLE4.R_SSN = TABLE1.M_SSN

But that errors on me.

Any suggestions are appreciated. Thanks,

-Bill
 
Bill

If you are using an Access front-end to get at data in a DB2 back-end, maybe
you can focus on the DB2 back-end.

Is there anyway to get a "view" constructed in DB2 that does the heavy
lifting (joining) before you ever query? Taking this approach when using a
SQL-Server back-end really speeded up my queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Access requires parentheses when you have more then two tables in the FROM
clause and are joining. Also, I would put the ON clause in the same order
as the Joined tables. I'm not sure that it really make a difference, but it
won't hurt.

FROM ((TABLE1
LEFT JOIN TABLE2 ON TABLE1.M_SSN = TABLE2.A_SSN )
LEFT JOIN TABLE3 ON TABLE1.M_SSN = TABLE3.F_SSN )
LEFT JOIN TABLE4 ON TABLE1.M_SSN = TABLE4.R_SSN

That said, I think that you might take a look at Jeff Boyce's idea. Also
helpful would be to know if the SSN fields were indexed
 
The perns worked! The query takes about 30 to 45 seconds now. I
actually figured it out just before I got your message. I've been
working on it for 3 days now. I'm glad that workd because I didn't want
to deal with having the DBA create a new view.

Thanks
 
Three days. Boy! You are a lot more patient (persistent) than I. If I
can't figure it out in a couple of hours, I will post to these groups. I do
admit, it has been a long time since I've posted to the groups. Other than
a couple of hours ago.
 
Back
Top