CODEWITHSUNDEEP
databasevbams-access
Avi
Avi

Reputation: 567

Access Query, which is generated from another query, takes a long time to run

I am adding some new features to a MS Access DB for a client. The original DB has several queries. The new features that I need to add require that I re-use these queries and incorporate them in my VBA and SQL code.

For example, I am generating a new query (via VBA and SQL) based one of these previous queries. I then export the result as an excel file.

However, whenever I try to run one of the new queries it takes about 15 minutes to complete. During this time there is message in the bottom right of the screen which says "running query. "

Here is one of the SQL queries that I am running. Please note that it ran quickly when there was only one WHERE condition. 

SELECT
   StudentProgram.fkCohortID AS [Cohort],
   Student.pkStudentID AS [Student ID],
   Student.EmplID AS [Employee ID],
   Student.LastName AS [Last Name],
   Student.FirstName AS [First Name],
   PostBaccActivity.fkSemesterID AS [Semester],
   PostBaccActivity.fkPostBaccID AS [PostBacc],
   PostBaccActivity.fkGradSchoolID AS [GradSchool],
   PostBaccActivity.ProjectTitle AS [ProjectTitle],
   PostBaccActivity.fkFacultyID AS [Faculty], 
   PostBaccActivity.BeginDate AS [BeginDate],
   PostBaccActivity.EndDate AS [EndDate],
   PostBaccActivity.Status AS [Status] 

FROM qryRptJoinAll
WHERE
       qryRptJoinAll.StudentProgram.fkCohortID  BETWEEN 1 AND 12 
    OR qryRptJoinAll.StudentProgram.fkCohortID = 25 
    OR qryRptJoinAll.StudentProgram.fkCohortID = 28 
    OR qryRptJoinAll.StudentProgram.fkCohortID = 49 
    OR qryRptJoinAll.StudentProgram.fkCohortID = 215 
    OR qryRptJoinAll.StudentProgram.fkCohortID = 220  

 GROUP BY StudentProgram.fkCohortID, Student.pkStudentID, Student.EmplID, Student.LastName,
          Student.FirstName, PostBaccActivity.fkSemesterID, PostBaccActivity.fkPostBaccID,
          PostBaccActivity.fkGradSchoolID, PostBaccActivity.ProjectTitle, PostBaccActivity.fkFacultyID,
          PostBaccActivity.BeginDate, PostBaccActivity.EndDate, PostBaccActivity.Status

This is the query I am using to generate the other queries:

SELECT 
   Student.pkStudentID, Student.EmplID, Student.OldID, Student.Inactive,
   Student.InactiveReason, Student.Status, Student.LastName, Student.MarriedName,
   Student.FirstName, Student.MiddleName, Student.DOB, Student.Sex, Student.SSN,
   Student.Email, Student.Race, Student.Ethnicity, Student.EmailSecondary,
   Student.fkSemesterBCStart, Student.fkSemesterGrad, Student.TotalCredits,
   Student.CreditsAttempted, Student.IndexCredits, Student.QualityPoints,
   Student.LocalCredits, Student.TransferCredits, Student.OtherCredits,
   StudentProgram.*, StudentEvent.*, StudentResearch.*, StudentEmployment.*,
   StudentMajor.*, PostBaccActivity.*, StudentPresentation.*, Presentation.*,
   StudentNote.*, SemesterGPA.*, StudentPublication.*, Publication.*, StudentCourse.fkCourseID,
   StudentCourse.fkFacultyID, StudentCourse.fkSemesterID, StudentCourse.Grade, Grade.GradeValue,
   Grade.NoValue, Course.*, RptCumulativeScienceGPA2.ScienceGPACalc, RptStudentControlList.pkStudentControlID
FROM 

    ((((((((PostBaccActivity RIGHT JOIN ((((((StudentProgram RIGHT JOIN 
Student ON StudentProgram.fkStudentID = Student.pkStudentID)
LEFT JOIN Cohort ON StudentProgram.fkCohortID = Cohort.pkCohortID) LEFT JOIN StudentEmployment ON Student.pkStudentID = StudentEmployment.fkStudentID)
LEFT JOIN StudentNote ON Student.pkStudentID = StudentNote.fkStudentID) LEFT JOIN StudentPresentation ON Student.pkStudentID = StudentPresentation.fkStudentID)
LEFT JOIN Presentation ON StudentPresentation.fkPresentationID = Presentation.pkPresentationID) ON PostBaccActivity.fkStudentID = Student.pkStudentID) 
LEFT JOIN RptCumulativeScienceGPA2 ON Student.pkStudentID = RptCumulativeScienceGPA2.fkStudentID) 
LEFT JOIN RptStudentControlList ON Student.pkStudentID = RptStudentControlList.fkControlID)
LEFT JOIN (StudentPublication LEFT JOIN Publication ON StudentPublication.fkPublicationID = Publication.pkPubID) ON Student.pkStudentID = StudentPublication.fkStudentID)
LEFT JOIN StudentResearch ON Student.pkStudentID = StudentResearch.fkStudentID) LEFT JOIN StudentMajor ON Student.pkStudentID = StudentMajor.fkStudentID)
LEFT JOIN StudentEvent ON Student.pkStudentID = StudentEvent.fkStudentID) 
LEFT JOIN (Course RIGHT JOIN (Grade RIGHT JOIN StudentCourse ON Grade.Grade = StudentCourse.Grade) ON Course.pkCourseID = StudentCourse.fkCourseID) ON Student.pkStudentID = StudentCourse.fkStudentID) 
LEFT JOIN SemesterGPA ON Student.pkStudentID = SemesterGPA.fkStudentID;

Is there anyway in which I can reduce the time it takes for them to run?

Upvotes: 0

Views: 56

Answers (2)

Andre
Andre

Reputation: 27634

  • Since you're not using any aggregate functions, get rid of the GROUP BY clause.
  • Make sure there is an index on StudentProgram.fkCohortID

But my guess is that the actual complexity is in qryRptJoinAll, so you'd have to show us this query too.

Upvotes: 1

user2191247
user2191247

Reputation:

Without knowing the exact queries, can give only generic advice:

  1. add indices to relevant fields in the tables, these depend on the queries
  2. if the same query is repeated, cache the results and reuse them
  3. joins of large tables might be optimized, by pre-filtering and caching
  4. if possible, implement asynchronous queries which can return partial results while still running in the background; this can give the illusion of a faster response

Upvotes: 1

Related Questions