Access query performance

G

Guest

I have a report that is based off of query #3. Query #3 consolidates the
data from Queries #1 & #2. Queries 1 & 2 each run in about 10 seconds, but
query 3 takes 8 minutes to run. Query #1 is as follows (#2 is similar):

SELECT Data.LogNo, Data.Clock, Data.YrsPos, Data.Date, Data.Reported,
Data.Recordable, Data.Start, Data.Time, Data.What, Data.Happen,
BodyLoc.BodyLocDesc, BodyPart.BodyPartDec, Caught.CaughtDesc,
CaughtBe.CaughtBeDesc, Class.ClassDesc, Cond.CondDesc,
ContactBy.ContactByDesc, ContactW.ContactWDesc, Exposure.ExposureDesc,
FallFoot.FallFootDesc, FallFrom.FallFromDesc, Nature.NatureDesc,
OverEx.OverExDesc, Practice.PracticeDesc, StruckAg.StruckAgDesc,
StruckBy.StruckByDesc, Data.SafetySys, BodyLoc_1.BodyLocDesc,
Practice_1.PracticeDesc, BodyPart_1.BodyPartDec, Cond_1.CondDesc,
Data.Complete
FROM (((((StruckBy INNER JOIN (StruckAg INNER JOIN (Practice INNER JOIN
(OverEx INNER JOIN (Nature INNER JOIN (FallFrom INNER JOIN (FallFoot INNER
JOIN (ContactW INNER JOIN (Cond INNER JOIN (BodyPart INNER JOIN (CaughtBe
INNER JOIN (Caught INNER JOIN (BodyLoc INNER JOIN (ContactBy INNER JOIN
(Class INNER JOIN Data ON Class.ClassID = Data.Class) ON
ContactBy.ContactByID = Data.ContactBy) ON BodyLoc.BodyLocID = Data.BodyLoc)
ON Caught.CaughtID = Data.Caught) ON CaughtBe.CaughtBeID = Data.CaughtBe) ON
BodyPart.BodyPartID = Data.BodyPart) ON Cond.CondID = Data.Cond) ON
ContactW.ContactWID = Data.ContactW) ON FallFoot.FallFootID = Data.FallFoot)
ON FallFrom.FallFromID = Data.FallFrom) ON Nature.NatureId = Data.Nature) ON
OverEx.OverExID = Data.OverEx) ON Practice.PracticeID = Data.Practice) ON
StruckAg.StruckAgID = Data.StruckAg) ON StruckBy.StruckByID = Data.StruckBy)
INNER JOIN Exposure ON Data.Exposure = Exposure.ExposureID) INNER JOIN Cond
AS Cond_1 ON Data.Cond2 = Cond_1.CondID) INNER JOIN Practice AS Practice_1 ON
Data.Practice2 = Practice_1.PracticeID) INNER JOIN BodyPart AS BodyPart_1 ON
Data.BodyPart2 = BodyPart_1.BodyPartID) INNER JOIN BodyLoc AS BodyLoc_1 ON
Data.BodyLoc2 = BodyLoc_1.BodyLocID
WHERE (((Trim([Data].[LogNo]))=Trim([forms]![frmDataEdit]![cboLogNo])))

Query #3:
SELECT qryEditPrint1.*, qryEditPrint2.*
FROM qryEditPrint1 INNER JOIN qryEditPrint2 ON qryEditPrint1.Clock =
qryEditPrint2.Clock;

The database is a Access 2K FE(mde)/BE setup on a network drive. I am using
this to print reports on injuries so all 50 fields are needed in the report.
Any advice on how to improve performance with Access? Thanks in advance.
 
J

Jeff Boyce

Mike

The first thing to check on slow queries is that fields used for selection
criteria and for joins are indexed in their underlying tables. If you've
used the Relationship window to set relationships between tables, you've
covered the indexing for the "join" fields.

I don't know your data structure, so I don't know if this is relevant...

Another approach might be to do your selection in smaller, initial queries,
build a "main" report on that (limited) information, and build subreport(s)
on the related data.

--
Good luck

Jeff Boyce
<Access MVP>

Mike said:
I have a report that is based off of query #3. Query #3 consolidates the
data from Queries #1 & #2. Queries 1 & 2 each run in about 10 seconds, but
query 3 takes 8 minutes to run. Query #1 is as follows (#2 is similar):

SELECT Data.LogNo, Data.Clock, Data.YrsPos, Data.Date, Data.Reported,
Data.Recordable, Data.Start, Data.Time, Data.What, Data.Happen,
BodyLoc.BodyLocDesc, BodyPart.BodyPartDec, Caught.CaughtDesc,
CaughtBe.CaughtBeDesc, Class.ClassDesc, Cond.CondDesc,
ContactBy.ContactByDesc, ContactW.ContactWDesc, Exposure.ExposureDesc,
FallFoot.FallFootDesc, FallFrom.FallFromDesc, Nature.NatureDesc,
OverEx.OverExDesc, Practice.PracticeDesc, StruckAg.StruckAgDesc,
StruckBy.StruckByDesc, Data.SafetySys, BodyLoc_1.BodyLocDesc,
Practice_1.PracticeDesc, BodyPart_1.BodyPartDec, Cond_1.CondDesc,
Data.Complete
FROM (((((StruckBy INNER JOIN (StruckAg INNER JOIN (Practice INNER JOIN
(OverEx INNER JOIN (Nature INNER JOIN (FallFrom INNER JOIN (FallFoot INNER
JOIN (ContactW INNER JOIN (Cond INNER JOIN (BodyPart INNER JOIN (CaughtBe
INNER JOIN (Caught INNER JOIN (BodyLoc INNER JOIN (ContactBy INNER JOIN
(Class INNER JOIN Data ON Class.ClassID = Data.Class) ON
ContactBy.ContactByID = Data.ContactBy) ON BodyLoc.BodyLocID = Data.BodyLoc)
ON Caught.CaughtID = Data.Caught) ON CaughtBe.CaughtBeID = Data.CaughtBe) ON
BodyPart.BodyPartID = Data.BodyPart) ON Cond.CondID = Data.Cond) ON
ContactW.ContactWID = Data.ContactW) ON FallFoot.FallFootID = Data.FallFoot)
ON FallFrom.FallFromID = Data.FallFrom) ON Nature.NatureId = Data.Nature) ON
OverEx.OverExID = Data.OverEx) ON Practice.PracticeID = Data.Practice) ON
StruckAg.StruckAgID = Data.StruckAg) ON StruckBy.StruckByID = Data.StruckBy)
INNER JOIN Exposure ON Data.Exposure = Exposure.ExposureID) INNER JOIN Cond
AS Cond_1 ON Data.Cond2 = Cond_1.CondID) INNER JOIN Practice AS Practice_1 ON
Data.Practice2 = Practice_1.PracticeID) INNER JOIN BodyPart AS BodyPart_1 ON
Data.BodyPart2 = BodyPart_1.BodyPartID) INNER JOIN BodyLoc AS BodyLoc_1 ON
Data.BodyLoc2 = BodyLoc_1.BodyLocID
WHERE (((Trim([Data].[LogNo]))=Trim([forms]![frmDataEdit]![cboLogNo])))

Query #3:
SELECT qryEditPrint1.*, qryEditPrint2.*
FROM qryEditPrint1 INNER JOIN qryEditPrint2 ON qryEditPrint1.Clock =
qryEditPrint2.Clock;

The database is a Access 2K FE(mde)/BE setup on a network drive. I am using
this to print reports on injuries so all 50 fields are needed in the report.
Any advice on how to improve performance with Access? Thanks in advance.
Click to expand...
 
G

Guest

Jeff,
Thanks for the advice, subreports did the trick. Can you tell me where to
get information on how access is either processing the queries or the
reports. I have several databases similar to this one either existing or in
development and it would be helpful to know how to troubleshoot these
problems. None of the documentation that I have seen gives good
troubleshooting advice on whether these types of problems are query or report
related, or whether I have managed to hit a limitation of Access. I would
never have thought to try subreports to fix the problem. Thanks again.

Mike

Jeff Boyce said:
Mike

The first thing to check on slow queries is that fields used for selection
criteria and for joins are indexed in their underlying tables. If you've
used the Relationship window to set relationships between tables, you've
covered the indexing for the "join" fields.

I don't know your data structure, so I don't know if this is relevant...

Another approach might be to do your selection in smaller, initial queries,
build a "main" report on that (limited) information, and build subreport(s)
on the related data.

--
Good luck

Jeff Boyce
<Access MVP>

Mike said:
I have a report that is based off of query #3. Query #3 consolidates the
data from Queries #1 & #2. Queries 1 & 2 each run in about 10 seconds, but
query 3 takes 8 minutes to run. Query #1 is as follows (#2 is similar):

SELECT Data.LogNo, Data.Clock, Data.YrsPos, Data.Date, Data.Reported,
Data.Recordable, Data.Start, Data.Time, Data.What, Data.Happen,
BodyLoc.BodyLocDesc, BodyPart.BodyPartDec, Caught.CaughtDesc,
CaughtBe.CaughtBeDesc, Class.ClassDesc, Cond.CondDesc,
ContactBy.ContactByDesc, ContactW.ContactWDesc, Exposure.ExposureDesc,
FallFoot.FallFootDesc, FallFrom.FallFromDesc, Nature.NatureDesc,
OverEx.OverExDesc, Practice.PracticeDesc, StruckAg.StruckAgDesc,
StruckBy.StruckByDesc, Data.SafetySys, BodyLoc_1.BodyLocDesc,
Practice_1.PracticeDesc, BodyPart_1.BodyPartDec, Cond_1.CondDesc,
Data.Complete
FROM (((((StruckBy INNER JOIN (StruckAg INNER JOIN (Practice INNER JOIN
(OverEx INNER JOIN (Nature INNER JOIN (FallFrom INNER JOIN (FallFoot INNER
JOIN (ContactW INNER JOIN (Cond INNER JOIN (BodyPart INNER JOIN (CaughtBe
INNER JOIN (Caught INNER JOIN (BodyLoc INNER JOIN (ContactBy INNER JOIN
(Class INNER JOIN Data ON Class.ClassID = Data.Class) ON
ContactBy.ContactByID = Data.ContactBy) ON BodyLoc.BodyLocID = Data.BodyLoc)
ON Caught.CaughtID = Data.Caught) ON CaughtBe.CaughtBeID = Data.CaughtBe) ON
BodyPart.BodyPartID = Data.BodyPart) ON Cond.CondID = Data.Cond) ON
ContactW.ContactWID = Data.ContactW) ON FallFoot.FallFootID = Data.FallFoot)
ON FallFrom.FallFromID = Data.FallFrom) ON Nature.NatureId = Data.Nature) ON
OverEx.OverExID = Data.OverEx) ON Practice.PracticeID = Data.Practice) ON
StruckAg.StruckAgID = Data.StruckAg) ON StruckBy.StruckByID = Data.StruckBy)
INNER JOIN Exposure ON Data.Exposure = Exposure.ExposureID) INNER JOIN Cond
AS Cond_1 ON Data.Cond2 = Cond_1.CondID) INNER JOIN Practice AS Practice_1 ON
Data.Practice2 = Practice_1.PracticeID) INNER JOIN BodyPart AS BodyPart_1 ON
Data.BodyPart2 = BodyPart_1.BodyPartID) INNER JOIN BodyLoc AS BodyLoc_1 ON
Data.BodyLoc2 = BodyLoc_1.BodyLocID
WHERE (((Trim([Data].[LogNo]))=Trim([forms]![frmDataEdit]![cboLogNo])))

Query #3:
SELECT qryEditPrint1.*, qryEditPrint2.*
FROM qryEditPrint1 INNER JOIN qryEditPrint2 ON qryEditPrint1.Clock =
qryEditPrint2.Clock;

The database is a Access 2K FE(mde)/BE setup on a network drive. I am using
this to print reports on injuries so all 50 fields are needed in the report.
Any advice on how to improve performance with Access? Thanks in advance.
Click to expand...
Click to expand...
 
Top