Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to optimize this mulit-JOIN query so that it executes faster?

Posted on 2014-08-15
3
Medium Priority
?
145 Views
Last Modified: 2014-08-22
Experts,

On average the following query takes 5 seconds to execute.  Is there any way to optimize the query so that it takes less time to execute?

SELECT employees.EmpId, employees.FirstName, employees.Lastname, employees.HomeSite, employees.HomeDepartment, employees.UserField13, employees.ManagerNUID, employees.HomeDepartment, employees.HomeSite, schedule.JobStartTime, schedule.JobEndTime, jobs.JobDepartment, jobs.JobSite FROM employees JOIN schedule ON employees.EmpId=schedule.EmpId JOIN jobs ON schedule.JobAssignmentId=jobs.JobAssignmentId 
	WHERE schedule.DeleteFlag!='1' AND employees.ManagerNUID LIKE '$user' AND schedule.ScheduleforDate='".date('Y-m-d', strtotime(str_replace('-', '/', $stoppage_date)))." 00:00:00' AND jobs.UserField3!='1' ORDER BY employees.HomeSite, employees.HomeDepartment, employees.Lastname, employees.FirstName, schedule.JobStartTime ASC

Open in new window


Thank you in advance for your help!
0
Comment
Question by:evibesmusic
  • 2
3 Comments
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40264696
1. make sure there are indexes on all the joined fields.

employees.ManagerNUID LIKE '$user'
2. Unless you are using some wildcard matching, this should run faster with an equality string comparison:
employees.ManagerNUID = '$user'

3. Your casting of a string field to a date value isn't very efficient and is probably the primary cause of your 'slow' performance.  Make sure that both columns are the same data type to avoid/eliminate data conversions in the query.
0
 

Author Comment

by:evibesmusic
ID: 40264750
@aikimark:

Thank you very much. I know my questions was very novice.

I've only implemented suggestion 1 of your solution and the query is already 3-4 times faster.

I will continue updating my code using your suggestions and hope to see even more improved execution times.

Cheers!
0
 

Author Closing Comment

by:evibesmusic
ID: 40279702
@all:

Sorry...didn't realize that this question was not closed.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month21 days, 1 hour left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question