Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / May 2005

Tip: Looking for answers? Try searching our database.

Access query performance

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike - 12 May 2005 14:48 GMT
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.
Jeff Boyce - 12 May 2005 15:04 GMT
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.

Signature

Good luck

Jeff Boyce
<Access MVP>

> 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
[quoted text clipped - 37 lines]
> 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.
Mike - 13 May 2005 13:30 GMT
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

> Mike
>
[quoted text clipped - 61 lines]
> report.
> > Any advice on how to improve performance with Access?  Thanks in advance.
Jeff Boyce - 14 May 2005 13:57 GMT
Mike

I don't recall anything specific, but Jeff Conrad has an extensive list of
resources which might contain what you're looking for.  See:

http://www.ltcomputerdesigns.com/JCReferences.html#Books

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

Good luck!

Jeff Boyce
<Access MVP>

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.