Re: Why does this query take forever?

From: Robert Klemme (bob.news_at_gmx.net)
Date: 11/25/03 

Date: Tue, 25 Nov 2003 15:54:14 +0100

"Ike" <rxv@hotmail.com> schrieb im Newsbeitrag
news:xbJwb.21433$Wy4.10462@newsread2.news.atl.earthlink.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.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

Relevant Pages

  • Re: Union Queries involving 2 Queries
    ... My first query is as follows: ... the inner s will probably cause troubleafter you ... I hope you tried to simplifiy your real queries for this ... Why not union the two original Select statements in one ...
    (microsoft.public.access.queries)
  • Re: Update query
    ... placing a between statement in the inner query and return the summed values. ... Same goes with the agg after the inner select statement. ... SET tblUploadCount = tblUploadCount + agg.TotalCnt, ... > UPDATE titles ...
    (microsoft.public.sqlserver.datamining)
  • Re: Dont know how to change SQL correctly.
    ... separate query first is not a problem. ... FROM tblProductLines RIGHT JOIN ((tblCompany INNER JOIN tblEmployees ON ... One solution is to first filter your inner table in a preliminary query, ...
    (microsoft.public.access.queries)
  • Re: Summing time worked by an individual on a weekly basis
    ... query going without the inapplicable records in the other tables change ... Inner joins say that a matching record has ... TypeOfTime (Toil, Lunch, Holiday, TravelTo, TravelFrom, Etc.) ... which pull out the data for each adviser from each table and then adds ...
    (microsoft.public.access.queries)
  • Re: JOIN on multiple conditions
    ... >> Your query is not quite the same as Gunnar's. ... Gunnar had an inner ... search arguments have to be in WHERE clauses that new ACCESS ... and you have some handicapped programmers. ...
    (microsoft.public.sqlserver.programming)
Loading