Join Problem

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
 
J

Jeff Boyce

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
 
J

John Spencer

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
 
B

bill.peters

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
 
J

John Spencer

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top