Befuddled by a query that seems so simple

pablotx

Registered User.
Local time
Today, 05:12
Joined
Feb 20, 2003
Messages
79
I have been trying to speed up a query that takes a long time to run on my users who use a VPN connection with Hotspots.

I have an Access front end linked to a SQL Server backend. I have a table called Review which contains a bunch of information as well as the Team Lead column. There is a related table called Team Members which has the additional people who go on the review. These tables are linked by review id. My dilemna is on my Calendar form. Depending on a hidden form which contains User Name, a query looks at if you are either in the team lead table OR in the Team Member table as member. The second query takes the result of the first query and queries back against the Team Member table to get the other folks who are the review with you (if you are not the team lead). The first query is the one that takes forever to run. Since it is looking for a parameter in 2 different tables, it is very slow. I thought I might try and make a view in SQL and link that to the Access front end, but I can't code in an access form parameter there. I thought about pass through queries, but don't think that will work either. I know I probably did not do a great job of explaining, but does anyone have any ideas on how I might optimize this? Thanks in advance for any help you might provide.
 
I thought I might put a brief example. Say Peter is the team lead for Review 1 and Paul and Mary are going on the trip with him. Let's say Mary is logged in and wants to look at her calendar. I need to have the results of Peter and Mary, but then also Peter and Paul, because they are related to Review #1.

I don't know if this information muddies the waters or if it is helpful... Thanks again.
 
More info... Here is the SQL for the first query:

SELECT tblScheduleAppt.SchedID, tblScheduleAppt.ContractNo, tblScheduleAppt.DateRevScheduled, tblScheduleAppt.Facilitator, tblScheduleAppt.TimeStamp, tblScheduleAppt.ReviewTypes, tblScheduleAppt.fkRefNo, tblReviewTeamMembers.ReviewerName, tblReviewTeamMembers.TeamMemDate, tblContractImport.ContractName, tblContractImport.CompCode, tblContractImport.WCA, tblContractImport.ContactPhone, tblContractImport.ContactAddress, tblContractImport.ContactCity, tblContractImport.ContactZip, tblContractImport.WaiverType, tblContractImport.CertEndDate, tblContractImport.Census, ReportLog.Actions, ReportLog.MgrApproveDate, ReportLog.SugRevLoc, ReportLog.EntryComplete, tblScheduleAppt.DisplayonCalendar, ReportLog.CAPRequired, ReportLog.CAPApprovedDate, ReportLog.OnSiteRefNo, ReportLog.DateOfEntrance, ReportLog.SubmittedMgrDate
FROM ReportLog RIGHT JOIN ((tblScheduleAppt INNER JOIN tblContractImport ON tblScheduleAppt.ContractNo = tblContractImport.ContractNo) LEFT JOIN tblReviewTeamMembers ON tblScheduleAppt.fkRefNo = tblReviewTeamMembers.fkRefNo) ON ReportLog.RLID = tblScheduleAppt.fkRefNo
GROUP BY tblScheduleAppt.SchedID, tblScheduleAppt.ContractNo, tblScheduleAppt.DateRevScheduled, tblScheduleAppt.Facilitator, tblScheduleAppt.TimeStamp, tblScheduleAppt.ReviewTypes, tblScheduleAppt.fkRefNo, tblReviewTeamMembers.ReviewerName, tblReviewTeamMembers.TeamMemDate, tblContractImport.ContractName, tblContractImport.CompCode, tblContractImport.WCA, tblContractImport.ContactPhone, tblContractImport.ContactAddress, tblContractImport.ContactCity, tblContractImport.ContactZip, tblContractImport.WaiverType, tblContractImport.CertEndDate, tblContractImport.Census, ReportLog.Actions, ReportLog.MgrApproveDate, ReportLog.SugRevLoc, ReportLog.EntryComplete, tblScheduleAppt.DisplayonCalendar, ReportLog.CAPRequired, ReportLog.CAPApprovedDate, ReportLog.OnSiteRefNo, ReportLog.DateOfEntrance, ReportLog.SubmittedMgrDate
HAVING (((tblScheduleAppt.Facilitator)=[Forms]![frmLocalUser]![txtUser]) AND ((tblScheduleAppt.DisplayonCalendar)=-1)) OR (((tblReviewTeamMembers.ReviewerName)=[Forms]![frmLocalUser]![txtUser]) AND ((tblScheduleAppt.DisplayonCalendar)=-1));


2nd query using the first query:
SELECT DISTINCT qryStaffCalendarWorkload.SchedID, qryStaffCalendarWorkload.ContractNo, qryStaffCalendarWorkload.DateRevScheduled, qryStaffCalendarWorkload.Facilitator, qryStaffCalendarWorkload.TimeStamp, tblReviewTeamMembers.ReviewerName, qryStaffCalendarWorkload.ReviewTypes, qryStaffCalendarWorkload.fkRefNo, qryStaffCalendarWorkload.ContractName, qryStaffCalendarWorkload.CompCode, qryStaffCalendarWorkload.WCA, qryStaffCalendarWorkload.ContactPhone, qryStaffCalendarWorkload.ContactAddress, qryStaffCalendarWorkload.ContactCity, qryStaffCalendarWorkload.ContactZip, qryStaffCalendarWorkload.WaiverType, qryStaffCalendarWorkload.CertEndDate, qryStaffCalendarWorkload.Census, qryStaffCalendarWorkload.Actions, qryStaffCalendarWorkload.MgrApproveDate, qryStaffCalendarWorkload.SugRevLoc, qryStaffCalendarWorkload.EntryComplete, qryStaffCalendarWorkload.DisplayonCalendar, qryStaffCalendarWorkload.CAPRequired, qryStaffCalendarWorkload.CAPApprovedDate, ReportLog.OnSiteRefNo
FROM (tblReviewTeamMembers RIGHT JOIN qryStaffCalendarWorkload ON tblReviewTeamMembers.fkRefNo = qryStaffCalendarWorkload.fkRefNo) LEFT JOIN ReportLog ON qryStaffCalendarWorkload.fkRefNo = ReportLog.RLID;
 
Your query is unreadable. Post it between code tags, alias the table and query names and format it to make the joins clear.

Without looking closely I would suggest that you move the Having conditions into Where conditions.

Make sure you have indexes on the Facilitator and Reviewer fields and have defined the relationships that are joins. Ideally, change the tables and queries to use numeric codes for the Facilitator and Reviewers. Number are generally faster to work with than strings though the indexes are the most critical factor.

Ensure that all the tables are on the server. Any local table would make the query very slow.
 
The Team Lead should also a member of the team. That should make the query simpler and faster. You can reference the same table twice with 1 query.
 

Users who are viewing this thread

Back
Top Bottom