Why does this query take forever?

For some reason, I have a rather large (to me) query, with numerous inner
joins, accessing a remote server, and it is taking about twenty times longer
than most queries to the same database.
The query itself is built programmatically within my application, and
example of which is below. I am hoping someone in the group may have some
insight into why this query is so slow, suggesting perhaps a better
structure for it, such that I can go back in and rewrite my code that
creates such queries.

Thanks in advance, Ike

"SELECT DISTINCT
chronology.id,status_id.status,chronology.completed,chronology.completeddate
,chronology.completedtime,activities_id.activity,chronology.activities_activ
ity,chronology.activities_attachment,chronology.activities_available_to_all,
chronology.upcards_firstnamelastname,upcard_id.id,chronology.feedbackrequire
d,chronology.landondate,chronology.hasspecifictime,chronology.datetoperform,
chronology.timetoperform,chronology.duration,chronology.weekends,chronology.
prefix,statusactivitieisid.id,associateresponsible.username,activities_usern
ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivities,associates
INNER JOIN status status_id on chronology.status_id=status_id.id
INNER JOIN activities activities_id on
chronology.activities_id=activities_id.id
INNER JOIN upcards upcard_id on chronology.upcard_id=upcard_id.id
INNER JOIN statusactivities statusactivitieisid on
chronology.statusactivitieisid=statusactivitieisid.id
INNER JOIN associates associateresponsible on
chronology.associateresponsible=associateresponsible.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_usernameid.id
WHERE chronology.upcard_id = 18"

"Ike" schrieb im Newsbeitrag

 > For some reason, I have a rather large (to me) query, with numerous
inner
 > joins, accessing a remote server, and it is taking about twenty times
longer
 > than most queries to the same database.
 > The query itself is built programmatically within my application, and
 > example of which is below. I am hoping someone in the group may have
some
 > insight into why this query is so slow, suggesting perhaps a better
 > structure for it, such that I can go back in and rewrite my code that
 > creates such queries.
 >
 > Thanks in advance, Ike
 >
 > "SELECT DISTINCT
 >
chronology.id,status_id.status,chronology.completed,chronology.completedda
te
 >
,chronology.completedtime,activities_id.activity,chronology.activities_act
iv
 >
ity,chronology.activities_attachment,chronology.activities_available_to_al
l,
 >
chronology.upcards_firstnamelastname,upcard_id.id,chronology.feedbackrequi
re
 >
d,chronology.landondate,chronology.hasspecifictime,chronology.datetoperfor
m,
 >
chronology.timetoperform,chronology.duration,chronology.weekends,chronolog
y.
 >
prefix,statusactivitieisid.id,associateresponsible.username,activities_use
rn
 > ameid.username,chronology.editFlag FROM
 > chronology,status,activities,upcards,statusactivities,associates
 > INNER JOIN status status_id on chronology.status_id=status_id.id
 > INNER JOIN activities activities_id on
 > chronology.activities_id=activities_id.id
 > INNER JOIN upcards upcard_id on chronology.upcard_id=upcard_id.id
 > INNER JOIN statusactivities statusactivitieisid on
 > chronology.statusactivitieisid=statusactivitieisid.id
 > INNER JOIN associates associateresponsible on
 > chronology.associateresponsible=associateresponsible.id
 > INNER JOIN associates activities_usernameid on
 > chronology.activities_usernameid=activities_usernameid.id
 > WHERE chronology.upcard_id = 18"

First I'd check whether all those joined tables have indexes on the id
field. If they don't, create them and check again.

If you don't have duplicates you can omit the "DISTINCT" saving the db a
lot of work.

robert