Very slow query

[janiotjoeawie via AccessMonster.com]

I have a form with tabs. On one of my tabs I have a subform. The data of this
subform is pulled trough a query. This specific query is bases on 1 query and
2 tables. It takes about 6 seconds to run the query wich is very slow if you
have to browse trough your records.

The query looks like:
SELECT qry_cursist_programme.CURSISTID, qry_cursist_programme.JAAR,
qry_cursist_programme.PROGRAMMAID, CURSIST_FLIGHTDETAILS.DEPARTURE_CITY,
CURSIST_ADRESSEN_VIEW.PLAATS, IIf(IsNull([departure_city]),[plaats],
[departure_city]) AS Departure, CURSIST_FLIGHTDETAILS.FLIGHTNUMBER
FROM
(qry_cursist_programme LEFT JOIN CURSIST_FLIGHTDETAILS
ON
(qry_cursist_programme.JAAR = CURSIST_FLIGHTDETAILS.YEAR)
AND
(qry_cursist_programme.PROGRAMMAID = CURSIST_FLIGHTDETAILS.PROGRAMME_ID)
AND
(qry_cursist_programme.CURSISTID = CURSIST_FLIGHTDETAILS.CURSIST_ID))
INNER JOIN
CURSIST_ADRESSEN_VIEW
ON
qry_cursist_programme.CURSISTID = CURSIST_ADRESSEN_VIEW.CURSISTID;

It pulls about 28000 records.

The query qry_cursist_programma on which it is based is the follow and has no
performance problem:
SELECT DISTINCT CURSUSSEN_VIEW.JAAR, CURSUSSEN_VIEW.PROGRAMMAID,
CURSIST_VOLGT_CURSUS_VIEW.CURSISTID
FROM
CURSIST_VOLGT_CURSUS_VIEW INNER JOIN CURSUSSEN_VIEW
ON
(CURSIST_VOLGT_CURSUS_VIEW.CURSUSID = CURSUSSEN_VIEW.CURSUSID) AND
(CURSIST_VOLGT_CURSUS_VIEW.JAAR = CURSUSSEN_VIEW.JAAR);

Why is the first query so slow. Is is not the iif case because if I ommit the
statment it is still to slow.

Regards.

 

[Stefan Hoffmann]

hi,

I have a form with tabs. On one of my tabs I have a subform. The data of this
subform is pulled trough a query. This specific query is bases on 1 query and
2 tables. It takes about 6 seconds to run the query wich is very slow if you
have to browse trough your records.

http://support.microsoft.com/kb/240434

You need the Jet SHOWPLAN here for concrete answers:

http://articles.techrepublic.com.com/5100-10878_11-5064388.html

Basically: Avoid type conversions in your joining fields and create
indices for them, e.g.:

FROM
(qry_cursist_programme LEFT JOIN CURSIST_FLIGHTDETAILS
ON
(qry_cursist_programme.JAAR = CURSIST_FLIGHTDETAILS.YEAR)
AND
(qry_cursist_programme.PROGRAMMAID = CURSIST_FLIGHTDETAILS.PROGRAMME_ID)
AND
(qry_cursist_programme.CURSISTID = CURSIST_FLIGHTDETAILS.CURSIST_ID))

Check whether you have a combined index consisting of the fields YEAR,
PROGRAMME_ID and CURSIST_ID in your table CURSIST_FLIGHTDETAILS.

and

FROM
CURSIST_VOLGT_CURSUS_VIEW INNER JOIN CURSUSSEN_VIEW
ON
(CURSIST_VOLGT_CURSUS_VIEW.CURSUSID = CURSUSSEN_VIEW.CURSUSID) AND
(CURSIST_VOLGT_CURSUS_VIEW.JAAR = CURSUSSEN_VIEW.JAAR);

Are CURSIST_VOLGT_CURSUS_VIEW and CURSUSSEN_VIEW tables? Then you need
in both of them a combined index CURSUSID, JAAR as long as these two
fields are not the primary or foreign key columns. In this case these
indices already exist.


mfG
--> stefan <--

 

[Jeff Boyce]

Queries can run slowly because:

* they involve a large number of tables and joins
* the underlying tables have a large number of records
* the fields on which the tables are joined are not indexed in the table
definitions
* the fields on which the query sorts and selects are not indexed in the
table definitions
* the query is running over a slow network to a distant datasource
* the query includes functions
* the PC on which the query runs is under-powered

Do any of these factors apply?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/