Why does this query take forever?

[Ike]

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.complete d,chronology.completeddate
,chronology.completedtime,activities_id.activity,c hronology.activities_activ
ity,chronology.activities_attachment,chronology.ac tivities_available_to_all,
chronology.upcards_firstnamelastname,upcard_id.id, chronology.feedbackrequire
d,chronology.landondate,chronology.hasspecifictime ,chronology.datetoperform,
chronology.timetoperform,chronology.duration,chron ology.weekends,chronology.
prefix,statusactivitieisid.id,associateresponsible .username,activities_usern
ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,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=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.id
WHERE chronology.upcard_id = 18"

[Robert Klemme]

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:xb*******************@newsread2.news.atl.eart hlink.net...

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.complete d,chronology.completedda
te ,chronology.completedtime,activities_id.activity,c hronology.activities_act
iv ity,chronology.activities_attachment,chronology.ac tivities_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,chron ology.weekends,chronolog
y. prefix,statusactivitieisid.id,associateresponsible .username,activities_use
rn ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,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=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.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

[Robert Klemme]

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:xb*******************@newsread2.news.atl.eart hlink.net...

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.complete d,chronology.completedda
te ,chronology.completedtime,activities_id.activity,c hronology.activities_act
iv ity,chronology.activities_attachment,chronology.ac tivities_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,chron ology.weekends,chronolog
y. prefix,statusactivitieisid.id,associateresponsible .username,activities_use
rn ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,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=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.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

[Ike]

Hmmm, all the id's do have indexes. Peculiarly, when I remove distinct....it
returns nothing? Ike

"Robert Klemme" <bo******@gmx.net> wrote in message
news:bp*************@ID-52924.news.uni-berlin.de...

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:xb*******************@newsread2.news.atl.eart hlink.net...

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.complete d,chronology.completedda
te

,chronology.completedtime,activities_id.activity,c hronology.activities_act
iv

ity,chronology.activities_attachment,chronology.ac tivities_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,chron ology.weekends,chronolog
y.

prefix,statusactivitieisid.id,associateresponsible .username,activities_use
rn

ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,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=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.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

[Ike]

Hmmm, all the id's do have indexes. Peculiarly, when I remove distinct....it
returns nothing? Ike

"Robert Klemme" <bo******@gmx.net> wrote in message
news:bp*************@ID-52924.news.uni-berlin.de...

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:xb*******************@newsread2.news.atl.eart hlink.net...

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.complete d,chronology.completedda
te

,chronology.completedtime,activities_id.activity,c hronology.activities_act
iv

ity,chronology.activities_attachment,chronology.ac tivities_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,chron ology.weekends,chronolog
y.

prefix,statusactivitieisid.id,associateresponsible .username,activities_use
rn

ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,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=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.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

[Robert Klemme]

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:ho*******************@newsread2.news.atl.eart hlink.net...

Hmmm, all the id's do have indexes.
Darn. If it's MS SQL Server you could throw it into the query analyzer
and look at the execution plan to get more hints about db optimization.
There's even an index optimizing wizard...
Peculiarly, when I remove distinct....it
returns nothing? Ike

That's irritating. If anything it should return *more* without DISTINCT -
not less.

robert

[Robert Klemme]

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:ho*******************@newsread2.news.atl.eart hlink.net...

Hmmm, all the id's do have indexes.
Darn. If it's MS SQL Server you could throw it into the query analyzer
and look at the execution plan to get more hints about db optimization.
There's even an index optimizing wizard...
Peculiarly, when I remove distinct....it
returns nothing? Ike

That's irritating. If anything it should return *more* without DISTINCT -
not less.

robert

[Ike]

LOL, I know....I was fearing a cartesian join! -Ike

"Robert Klemme" <bo******@gmx.net> wrote in message
news:bq*************@ID-52924.news.uni-berlin.de...

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:ho*******************@newsread2.news.atl.eart hlink.net...

Hmmm, all the id's do have indexes.

Darn. If it's MS SQL Server you could throw it into the query analyzer
and look at the execution plan to get more hints about db optimization.
There's even an index optimizing wizard...

Peculiarly, when I remove distinct....it
returns nothing? Ike

That's irritating. If anything it should return *more* without DISTINCT -
not less.

robert

[Ike]

LOL, I know....I was fearing a cartesian join! -Ike

"Robert Klemme" <bo******@gmx.net> wrote in message
news:bq*************@ID-52924.news.uni-berlin.de...

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:ho*******************@newsread2.news.atl.eart hlink.net...

Hmmm, all the id's do have indexes.

Darn. If it's MS SQL Server you could throw it into the query analyzer
and look at the execution plan to get more hints about db optimization.
There's even an index optimizing wizard...

Peculiarly, when I remove distinct....it
returns nothing? Ike

That's irritating. If anything it should return *more* without DISTINCT -
not less.

robert

[Moran Ben-David]

which database server are you using (sql server, oracle, db2)?

"Ike" <rx*@hotmail.com> wrote in message
news:xb*******************@newsread2.news.atl.eart hlink.net...

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.complete d,chronology.completeddate ,chronology.completedtime,activities_id.activity,c hronology.activities_activ ity,chronology.activities_attachment,chronology.ac tivities_available_to_all, chronology.upcards_firstnamelastname,upcard_id.id, chronology.feedbackrequire d,chronology.landondate,chronology.hasspecifictime ,chronology.datetoperform, chronology.timetoperform,chronology.duration,chron ology.weekends,chronology. prefix,statusactivitieisid.id,associateresponsible .username,activities_usern ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,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=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.id
WHERE chronology.upcard_id = 18"

[Moran Ben-David]

which database server are you using (sql server, oracle, db2)?

"Ike" <rx*@hotmail.com> wrote in message
news:xb*******************@newsread2.news.atl.eart hlink.net...

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.complete d,chronology.completeddate ,chronology.completedtime,activities_id.activity,c hronology.activities_activ ity,chronology.activities_attachment,chronology.ac tivities_available_to_all, chronology.upcards_firstnamelastname,upcard_id.id, chronology.feedbackrequire d,chronology.landondate,chronology.hasspecifictime ,chronology.datetoperform, chronology.timetoperform,chronology.duration,chron ology.weekends,chronology. prefix,statusactivitieisid.id,associateresponsible .username,activities_usern ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,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=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.id
WHERE chronology.upcard_id = 18"