Why does this query take forever?  
Author Message
Ike





PostPosted: 2003-11-25 21:56:00 Top

java-programmer, 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"


 
Robert Klemme





PostPosted: 2003-11-25 22:54:00 Top

java-programmer >> Why does this query take forever?
"Ike" <email***@***.com> schrieb im Newsbeitrag
news:xbJwb.21433$email***@***.com...
> 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

 
Ike





PostPosted: 2003-11-26 0:26:00 Top

java-programmer >> Why does this query take forever? Hmmm, all the id's do have indexes. Peculiarly, when I remove distinct....it
returns nothing? Ike

"Robert Klemme" <email***@***.com> wrote in message
news:bpvqeo$1ss8l8$email***@***.com...
>
> "Ike" <email***@***.com> schrieb im Newsbeitrag
> news:xbJwb.21433$email***@***.com...
> > 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
>


 
 
Robert Klemme





PostPosted: 2003-11-26 0:40:00 Top

java-programmer >> Why does this query take forever?
"Ike" <email***@***.com> schrieb im Newsbeitrag
news:hoLwb.21551$email***@***.com...
> 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





PostPosted: 2003-11-26 0:49:00 Top

java-programmer >> Why does this query take forever? LOL, I know....I was fearing a cartesian join! -Ike

"Robert Klemme" <email***@***.com> wrote in message
news:bq00lp$1sc9jl$email***@***.com...
>
> "Ike" <email***@***.com> schrieb im Newsbeitrag
> news:hoLwb.21551$email***@***.com...
> > 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





PostPosted: 2003-11-27 2:40:00 Top

java-programmer >> Why does this query take forever? which database server are you using (sql server, oracle, db2)?

"Ike" <email***@***.com> wrote in message
news:xbJwb.21433$email***@***.com...
> 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"
>
>