Speed of query?

J

Jim Shady

Hi there,

I've made a query but it doesn't really work. When I run it, it always gets
to the end but never completely finishes? Hope that makes sense. I think it's
because of the amount of data that it is trying to show me or something. It
is sorting through hundreds of thousands of records and the trying to return
a few thousand or so.

I wonder if anyone had any suggestions as to how to make it work?

SQL below if it helps?

Jim Shady

SELECT DISTINCT app.app_id, app.forename, app.surname, crs.title,
lkp_lookups_2.short_descr, app_crs_enrol.start_date,
app_crs_enrol.offer_date, lkp_lookups_3.short_descr,
lkp_lookups_4.short_descr, app_crs_enrol.app_decision_date,
app_crs_enrol.application_rcvd_date, app_crs_enrol.reference1_rcvd_date,
app_crs_enrol.reference2_rcvd_date, app_crs_enrol.transcript_rcvd_date,
app_crs_enrol.application_to_tutor_date, app_crs_enrol.condition1,
app_crs_enrol.condition2, app_crs_enrol.condition3,
app_crs_enrol.met_condition1, app_crs_enrol.met_condition2,
app_crs_enrol.met_condition3, year.year_descr, lkp_lookups_5.short_descr,
qualification.descr, app.notes, app_crs_enrol.fee_paid,
app_crs_enrol.fee_amount, app_crs_enrol.tuition_fee, crs.crs_sub_type,
crs_subject.relationship_lid, app_crs_enrol.year_id, org_1.org_type_lid,
org_1.org_name
FROM org AS org_1 RIGHT JOIN (org_org RIGHT JOIN (org RIGHT JOIN (subject
RIGHT JOIN (crs_subject RIGHT JOIN (crs_asp RIGHT JOIN ((((((((((app LEFT
JOIN app_address ON app.app_id = app_address.app_id) LEFT JOIN app_crs_enrol
ON app.app_id = app_crs_enrol.app_id) LEFT JOIN [year] ON
app_crs_enrol.year_id = year.year_id) LEFT JOIN lkp_lookups AS lkp_lookups_1
ON app_address.address_type_lid = lkp_lookups_1.code) LEFT JOIN crs ON
app_crs_enrol.crs_id = crs.crs_id) LEFT JOIN lkp_lookups AS lkp_lookups_2 ON
app_crs_enrol.mos_lid = lkp_lookups_2.code) LEFT JOIN lkp_lookups AS
lkp_lookups_3 ON app_crs_enrol.offer_lid = lkp_lookups_3.code) LEFT JOIN
lkp_lookups AS lkp_lookups_4 ON app_crs_enrol.app_decision_lid =
lkp_lookups_4.code) LEFT JOIN lkp_lookups AS lkp_lookups_5 ON
app.res_category_lid = lkp_lookups_5.code) LEFT JOIN qualification ON
crs.qual_id = qualification.qual_id) ON crs_asp.crs_id =
app_crs_enrol.crs_id) ON crs_subject.crs_id = crs.crs_id) ON subject.subj_id
= crs_subject.subj_id) ON org.org_id = subject.org_id) ON org_org.sub_org_id
= subject.org_id) ON org_1.org_id = org_org.org_id
WHERE (((crs.crs_sub_type)="Sos") AND
((crs_subject.relationship_lid)="003056") AND
((org_1.org_type_lid)="001414"));
 
L

Larry Linson

I wouldn't even try to analyze that SQL in detail, but just looking at the
number of reads required for all the joins, I can certainly understand why
it could drag on for a long, long time. If the SQL is complex, and there are
a large number of input records, there is a good chance that the Query is
going to take some time to execute.

First thing I could suggest is to check and make certain that every field
that particpates in a Join or WHERE is indexed, unique if possible.

For someone else to analyze it, you might start by listing the tables and
the pertinent fields for the joins, so they don't have to, first, go through
15 joins to try to make sure they have even identified all the tables.

Just for my edification, at a high level, what is this Query intended to
accomplish?

Larry Linson
Microsoft Access MVP


Jim Shady said:
Hi there,

I've made a query but it doesn't really work. When I run it, it always
gets
to the end but never completely finishes? Hope that makes sense. I think
it's
because of the amount of data that it is trying to show me or something.
It
is sorting through hundreds of thousands of records and the trying to
return
a few thousand or so.

I wonder if anyone had any suggestions as to how to make it work?

SQL below if it helps?

Jim Shady

SELECT DISTINCT app.app_id, app.forename, app.surname, crs.title,
lkp_lookups_2.short_descr, app_crs_enrol.start_date,
app_crs_enrol.offer_date, lkp_lookups_3.short_descr,
lkp_lookups_4.short_descr, app_crs_enrol.app_decision_date,
app_crs_enrol.application_rcvd_date, app_crs_enrol.reference1_rcvd_date,
app_crs_enrol.reference2_rcvd_date, app_crs_enrol.transcript_rcvd_date,
app_crs_enrol.application_to_tutor_date, app_crs_enrol.condition1,
app_crs_enrol.condition2, app_crs_enrol.condition3,
app_crs_enrol.met_condition1, app_crs_enrol.met_condition2,
app_crs_enrol.met_condition3, year.year_descr, lkp_lookups_5.short_descr,
qualification.descr, app.notes, app_crs_enrol.fee_paid,
app_crs_enrol.fee_amount, app_crs_enrol.tuition_fee, crs.crs_sub_type,
crs_subject.relationship_lid, app_crs_enrol.year_id, org_1.org_type_lid,
org_1.org_name
FROM org AS org_1 RIGHT JOIN (org_org RIGHT JOIN (org RIGHT JOIN (subject
RIGHT JOIN (crs_subject RIGHT JOIN (crs_asp RIGHT JOIN ((((((((((app LEFT
JOIN app_address ON app.app_id = app_address.app_id) LEFT JOIN
app_crs_enrol
ON app.app_id = app_crs_enrol.app_id) LEFT JOIN [year] ON
app_crs_enrol.year_id = year.year_id) LEFT JOIN lkp_lookups AS
lkp_lookups_1
ON app_address.address_type_lid = lkp_lookups_1.code) LEFT JOIN crs ON
app_crs_enrol.crs_id = crs.crs_id) LEFT JOIN lkp_lookups AS lkp_lookups_2
ON
app_crs_enrol.mos_lid = lkp_lookups_2.code) LEFT JOIN lkp_lookups AS
lkp_lookups_3 ON app_crs_enrol.offer_lid = lkp_lookups_3.code) LEFT JOIN
lkp_lookups AS lkp_lookups_4 ON app_crs_enrol.app_decision_lid =
lkp_lookups_4.code) LEFT JOIN lkp_lookups AS lkp_lookups_5 ON
app.res_category_lid = lkp_lookups_5.code) LEFT JOIN qualification ON
crs.qual_id = qualification.qual_id) ON crs_asp.crs_id =
app_crs_enrol.crs_id) ON crs_subject.crs_id = crs.crs_id) ON
subject.subj_id
= crs_subject.subj_id) ON org.org_id = subject.org_id) ON
org_org.sub_org_id
= subject.org_id) ON org_1.org_id = org_org.org_id
WHERE (((crs.crs_sub_type)="Sos") AND
((crs_subject.relationship_lid)="003056") AND
((org_1.org_type_lid)="001414"));
Click to expand...
 
Top