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 programmaticall y 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,s tatus_id.status ,chronology.com pleted,chronolo gy.completeddat e
,chronology.com pletedtime,acti vities_id.activ ity,chronology. activities_acti v
ity,chronology. activities_atta chment,chronolo gy.activities_a vailable_to_all ,
chronology.upca rds_firstnamela stname,upcard_i d.id,chronology .feedbackrequir e
d,chronology.la ndondate,chrono logy.hasspecifi ctime,chronolog y.datetoperform ,
chronology.time toperform,chron ology.duration, chronology.week ends,chronology .
prefix,statusac tivitieisid.id, associaterespon sible.username, activities_user n
ameid.username, chronology.edit Flag FROM
chronology,stat us,activities,u pcards,statusac tivities,associ ates
INNER JOIN status status_id on chronology.stat us_id=status_id .id
INNER JOIN activities activities_id on
chronology.acti vities_id=activ ities_id.id
INNER JOIN upcards upcard_id on chronology.upca rd_id=upcard_id .id
INNER JOIN statusactivitie s statusactivitie isid on
chronology.stat usactivitieisid =statusactiviti eisid.id
INNER JOIN associates associaterespon sible on
chronology.asso ciateresponsibl e=associateresp onsible.id
INNER JOIN associates activities_user nameid on
chronology.acti vities_username id=activities_u sernameid.id
WHERE chronology.upca rd_id = 18"

[Robert Klemme]

Re: Why does this query take forever?


"Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
news:xbJwb.2143 3$Wy4.10462@new sread2.news.atl .earthlink.net. ..[color=blue]
> For some reason, I have a rather large (to me) query, with numerous[/color]
inner[color=blue]
> joins, accessing a remote server, and it is taking about twenty times[/color]
longer[color=blue]
> than most queries to the same database.
> The query itself is built programmaticall y within my application, and
> example of which is below. I am hoping someone in the group may have[/color]
some[color=blue]
> 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
>[/color]
chronology.id,s tatus_id.status ,chronology.com pleted,chronolo gy.completedda
te[color=blue]
>[/color]
,chronology.com pletedtime,acti vities_id.activ ity,chronology. activities_act
iv[color=blue]
>[/color]
ity,chronology. activities_atta chment,chronolo gy.activities_a vailable_to_al
l,[color=blue]
>[/color]
chronology.upca rds_firstnamela stname,upcard_i d.id,chronology .feedbackrequi
re[color=blue]
>[/color]
d,chronology.la ndondate,chrono logy.hasspecifi ctime,chronolog y.datetoperfor
m,[color=blue]
>[/color]
chronology.time toperform,chron ology.duration, chronology.week ends,chronolog
y.[color=blue]
>[/color]
prefix,statusac tivitieisid.id, associaterespon sible.username, activities_use
rn[color=blue]
> ameid.username, chronology.edit Flag FROM
> chronology,stat us,activities,u pcards,statusac tivities,associ ates
> INNER JOIN status status_id on chronology.stat us_id=status_id .id
> INNER JOIN activities activities_id on
> chronology.acti vities_id=activ ities_id.id
> INNER JOIN upcards upcard_id on chronology.upca rd_id=upcard_id .id
> INNER JOIN statusactivitie s statusactivitie isid on
> chronology.stat usactivitieisid =statusactiviti eisid.id
> INNER JOIN associates associaterespon sible on
> chronology.asso ciateresponsibl e=associateresp onsible.id
> INNER JOIN associates activities_user nameid on
> chronology.acti vities_username id=activities_u sernameid.id
> WHERE chronology.upca rd_id = 18"[/color]

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]

Re: Why does this query take forever?


"Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
news:xbJwb.2143 3$Wy4.10462@new sread2.news.atl .earthlink.net. ..[color=blue]
> For some reason, I have a rather large (to me) query, with numerous[/color]
inner[color=blue]
> joins, accessing a remote server, and it is taking about twenty times[/color]
longer[color=blue]
> than most queries to the same database.
> The query itself is built programmaticall y within my application, and
> example of which is below. I am hoping someone in the group may have[/color]
some[color=blue]
> 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
>[/color]
chronology.id,s tatus_id.status ,chronology.com pleted,chronolo gy.completedda
te[color=blue]
>[/color]
,chronology.com pletedtime,acti vities_id.activ ity,chronology. activities_act
iv[color=blue]
>[/color]
ity,chronology. activities_atta chment,chronolo gy.activities_a vailable_to_al
l,[color=blue]
>[/color]
chronology.upca rds_firstnamela stname,upcard_i d.id,chronology .feedbackrequi
re[color=blue]
>[/color]
d,chronology.la ndondate,chrono logy.hasspecifi ctime,chronolog y.datetoperfor
m,[color=blue]
>[/color]
chronology.time toperform,chron ology.duration, chronology.week ends,chronolog
y.[color=blue]
>[/color]
prefix,statusac tivitieisid.id, associaterespon sible.username, activities_use
rn[color=blue]
> ameid.username, chronology.edit Flag FROM
> chronology,stat us,activities,u pcards,statusac tivities,associ ates
> INNER JOIN status status_id on chronology.stat us_id=status_id .id
> INNER JOIN activities activities_id on
> chronology.acti vities_id=activ ities_id.id
> INNER JOIN upcards upcard_id on chronology.upca rd_id=upcard_id .id
> INNER JOIN statusactivitie s statusactivitie isid on
> chronology.stat usactivitieisid =statusactiviti eisid.id
> INNER JOIN associates associaterespon sible on
> chronology.asso ciateresponsibl e=associateresp onsible.id
> INNER JOIN associates activities_user nameid on
> chronology.acti vities_username id=activities_u sernameid.id
> WHERE chronology.upca rd_id = 18"[/color]

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]

Re: 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" <bob.news@gmx.n et> wrote in message
news:bpvqeo$1ss 8l8$1@ID-52924.news.uni-berlin.de...[color=blue]
>
> "Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
> news:xbJwb.2143 3$Wy4.10462@new sread2.news.atl .earthlink.net. ..[color=green]
> > For some reason, I have a rather large (to me) query, with numerous[/color]
> inner[color=green]
> > joins, accessing a remote server, and it is taking about twenty times[/color]
> longer[color=green]
> > than most queries to the same database.
> > The query itself is built programmaticall y within my application, and
> > example of which is below. I am hoping someone in the group may have[/color]
> some[color=green]
> > 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
> >[/color]
> chronology.id,s tatus_id.status ,chronology.com pleted,chronolo gy.completedda
> te[color=green]
> >[/color]
> ,chronology.com pletedtime,acti vities_id.activ ity,chronology. activities_act
> iv[color=green]
> >[/color]
> ity,chronology. activities_atta chment,chronolo gy.activities_a vailable_to_al
> l,[color=green]
> >[/color]
> chronology.upca rds_firstnamela stname,upcard_i d.id,chronology .feedbackrequi
> re[color=green]
> >[/color]
> d,chronology.la ndondate,chrono logy.hasspecifi ctime,chronolog y.datetoperfor
> m,[color=green]
> >[/color]
> chronology.time toperform,chron ology.duration, chronology.week ends,chronolog
> y.[color=green]
> >[/color]
> prefix,statusac tivitieisid.id, associaterespon sible.username, activities_use
> rn[color=green]
> > ameid.username, chronology.edit Flag FROM
> > chronology,stat us,activities,u pcards,statusac tivities,associ ates
> > INNER JOIN status status_id on chronology.stat us_id=status_id .id
> > INNER JOIN activities activities_id on
> > chronology.acti vities_id=activ ities_id.id
> > INNER JOIN upcards upcard_id on chronology.upca rd_id=upcard_id .id
> > INNER JOIN statusactivitie s statusactivitie isid on
> > chronology.stat usactivitieisid =statusactiviti eisid.id
> > INNER JOIN associates associaterespon sible on
> > chronology.asso ciateresponsibl e=associateresp onsible.id
> > INNER JOIN associates activities_user nameid on
> > chronology.acti vities_username id=activities_u sernameid.id
> > WHERE chronology.upca rd_id = 18"[/color]
>
> 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
>[/color]

[Ike]

Re: 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" <bob.news@gmx.n et> wrote in message
news:bpvqeo$1ss 8l8$1@ID-52924.news.uni-berlin.de...[color=blue]
>
> "Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
> news:xbJwb.2143 3$Wy4.10462@new sread2.news.atl .earthlink.net. ..[color=green]
> > For some reason, I have a rather large (to me) query, with numerous[/color]
> inner[color=green]
> > joins, accessing a remote server, and it is taking about twenty times[/color]
> longer[color=green]
> > than most queries to the same database.
> > The query itself is built programmaticall y within my application, and
> > example of which is below. I am hoping someone in the group may have[/color]
> some[color=green]
> > 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
> >[/color]
> chronology.id,s tatus_id.status ,chronology.com pleted,chronolo gy.completedda
> te[color=green]
> >[/color]
> ,chronology.com pletedtime,acti vities_id.activ ity,chronology. activities_act
> iv[color=green]
> >[/color]
> ity,chronology. activities_atta chment,chronolo gy.activities_a vailable_to_al
> l,[color=green]
> >[/color]
> chronology.upca rds_firstnamela stname,upcard_i d.id,chronology .feedbackrequi
> re[color=green]
> >[/color]
> d,chronology.la ndondate,chrono logy.hasspecifi ctime,chronolog y.datetoperfor
> m,[color=green]
> >[/color]
> chronology.time toperform,chron ology.duration, chronology.week ends,chronolog
> y.[color=green]
> >[/color]
> prefix,statusac tivitieisid.id, associaterespon sible.username, activities_use
> rn[color=green]
> > ameid.username, chronology.edit Flag FROM
> > chronology,stat us,activities,u pcards,statusac tivities,associ ates
> > INNER JOIN status status_id on chronology.stat us_id=status_id .id
> > INNER JOIN activities activities_id on
> > chronology.acti vities_id=activ ities_id.id
> > INNER JOIN upcards upcard_id on chronology.upca rd_id=upcard_id .id
> > INNER JOIN statusactivitie s statusactivitie isid on
> > chronology.stat usactivitieisid =statusactiviti eisid.id
> > INNER JOIN associates associaterespon sible on
> > chronology.asso ciateresponsibl e=associateresp onsible.id
> > INNER JOIN associates activities_user nameid on
> > chronology.acti vities_username id=activities_u sernameid.id
> > WHERE chronology.upca rd_id = 18"[/color]
>
> 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
>[/color]

[Robert Klemme]

Re: Why does this query take forever?


"Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
news:hoLwb.2155 1$Wy4.20576@new sread2.news.atl .earthlink.net. ..[color=blue]
> Hmmm, all the id's do have indexes.[/color]

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...
[color=blue]
> Peculiarly, when I remove distinct....it
> returns nothing? Ike[/color]

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

robert

[Robert Klemme]

Re: Why does this query take forever?


"Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
news:hoLwb.2155 1$Wy4.20576@new sread2.news.atl .earthlink.net. ..[color=blue]
> Hmmm, all the id's do have indexes.[/color]

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...
[color=blue]
> Peculiarly, when I remove distinct....it
> returns nothing? Ike[/color]

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

robert

[Ike]

Re: Why does this query take forever?

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

"Robert Klemme" <bob.news@gmx.n et> wrote in message
news:bq00lp$1sc 9jl$1@ID-52924.news.uni-berlin.de...[color=blue]
>
> "Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
> news:hoLwb.2155 1$Wy4.20576@new sread2.news.atl .earthlink.net. ..[color=green]
> > Hmmm, all the id's do have indexes.[/color]
>
> 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...
>[color=green]
> > Peculiarly, when I remove distinct....it
> > returns nothing? Ike[/color]
>
> That's irritating. If anything it should return *more* without DISTINCT -
> not less.
>
> robert
>[/color]

[Ike]

Re: Why does this query take forever?

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

"Robert Klemme" <bob.news@gmx.n et> wrote in message
news:bq00lp$1sc 9jl$1@ID-52924.news.uni-berlin.de...[color=blue]
>
> "Ike" <rxv@hotmail.co m> schrieb im Newsbeitrag
> news:hoLwb.2155 1$Wy4.20576@new sread2.news.atl .earthlink.net. ..[color=green]
> > Hmmm, all the id's do have indexes.[/color]
>
> 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...
>[color=green]
> > Peculiarly, when I remove distinct....it
> > returns nothing? Ike[/color]
>
> That's irritating. If anything it should return *more* without DISTINCT -
> not less.
>
> robert
>[/color]

[Moran Ben-David]

Re: Why does this query take forever?

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

"Ike" <rxv@hotmail.co m> wrote in message
news:xbJwb.2143 3$Wy4.10462@new sread2.news.atl .earthlink.net. ..[color=blue]
> 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[/color]
longer[color=blue]
> than most queries to the same database.
> The query itself is built programmaticall y 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
>[/color]
chronology.id,s tatus_id.status ,chronology.com pleted,chronolo gy.completeddat e[color=blue]
>[/color]
,chronology.com pletedtime,acti vities_id.activ ity,chronology. activities_acti v[color=blue]
>[/color]
ity,chronology. activities_atta chment,chronolo gy.activities_a vailable_to_all ,[color=blue]
>[/color]
chronology.upca rds_firstnamela stname,upcard_i d.id,chronology .feedbackrequir e[color=blue]
>[/color]
d,chronology.la ndondate,chrono logy.hasspecifi ctime,chronolog y.datetoperform ,[color=blue]
>[/color]
chronology.time toperform,chron ology.duration, chronology.week ends,chronology .[color=blue]
>[/color]
prefix,statusac tivitieisid.id, associaterespon sible.username, activities_user n[color=blue]
> ameid.username, chronology.edit Flag FROM
> chronology,stat us,activities,u pcards,statusac tivities,associ ates
> INNER JOIN status status_id on chronology.stat us_id=status_id .id
> INNER JOIN activities activities_id on
> chronology.acti vities_id=activ ities_id.id
> INNER JOIN upcards upcard_id on chronology.upca rd_id=upcard_id .id
> INNER JOIN statusactivitie s statusactivitie isid on
> chronology.stat usactivitieisid =statusactiviti eisid.id
> INNER JOIN associates associaterespon sible on
> chronology.asso ciateresponsibl e=associateresp onsible.id
> INNER JOIN associates activities_user nameid on
> chronology.acti vities_username id=activities_u sernameid.id
> WHERE chronology.upca rd_id = 18"
>
>[/color]

[Moran Ben-David]

Re: Why does this query take forever?

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

"Ike" <rxv@hotmail.co m> wrote in message
news:xbJwb.2143 3$Wy4.10462@new sread2.news.atl .earthlink.net. ..[color=blue]
> 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[/color]
longer[color=blue]
> than most queries to the same database.
> The query itself is built programmaticall y 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
>[/color]
chronology.id,s tatus_id.status ,chronology.com pleted,chronolo gy.completeddat e[color=blue]
>[/color]
,chronology.com pletedtime,acti vities_id.activ ity,chronology. activities_acti v[color=blue]
>[/color]
ity,chronology. activities_atta chment,chronolo gy.activities_a vailable_to_all ,[color=blue]
>[/color]
chronology.upca rds_firstnamela stname,upcard_i d.id,chronology .feedbackrequir e[color=blue]
>[/color]
d,chronology.la ndondate,chrono logy.hasspecifi ctime,chronolog y.datetoperform ,[color=blue]
>[/color]
chronology.time toperform,chron ology.duration, chronology.week ends,chronology .[color=blue]
>[/color]
prefix,statusac tivitieisid.id, associaterespon sible.username, activities_user n[color=blue]
> ameid.username, chronology.edit Flag FROM
> chronology,stat us,activities,u pcards,statusac tivities,associ ates
> INNER JOIN status status_id on chronology.stat us_id=status_id .id
> INNER JOIN activities activities_id on
> chronology.acti vities_id=activ ities_id.id
> INNER JOIN upcards upcard_id on chronology.upca rd_id=upcard_id .id
> INNER JOIN statusactivitie s statusactivitie isid on
> chronology.stat usactivitieisid =statusactiviti eisid.id
> INNER JOIN associates associaterespon sible on
> chronology.asso ciateresponsibl e=associateresp onsible.id
> INNER JOIN associates activities_user nameid on
> chronology.acti vities_username id=activities_u sernameid.id
> WHERE chronology.upca rd_id = 18"
>
>[/color]