Jump to content

Recommended Posts

Dear  Team,

I have the below query to get my output

SELECT c.branchName as Branch,a.CustomerID as CustomerID, a.CustomerName, b.VisitInterval, b.VisitPlan, 
b.VisitType, d.Fname as Name, e.deptName as Department   
FROM  sbms.machinemaster as a 
LEFT JOIN sbms.customerassign as b ON b.RowID = a.id
INNER JOIN sbms.branch as c ON c.branchcode = a.BranchCode
LEFT JOIN sbms.usertable AS d ON d.EmpNo = b.EmpID 
LEFT JOIN sbms.deparment AS e ON e.deptID = d.Department
WHERE a.MachineStatus = 'A' AND c.branchcode NOT IN ('EXP','HO') 
GROUP BY a.BranchCode, a.CustomerID order by Branch

When I am using the above query it takes 62 seconds to execute. But I have another query which is the same as the above 

SELECT c.branchName as Branch,a.CustomerID as CustomerID, a.CustomerName, b.VisitInterval, b.VisitPlan, 
b.VisitType, d.Fname as Name, e.deptName as Department   
FROM  sbms.machinemaster as a 
LEFT JOIN sbms.customerassign as b ON b.CustomerID = a.CustomerID
INNER JOIN sbms.branch as c ON c.branchcode = a.BranchCode
LEFT JOIN sbms.usertable AS d ON d.EmpNo = b.EmpID 
LEFT JOIN sbms.deparment AS e ON e.deptID = d.Department
WHERE a.MachineStatus = 'A' AND c.branchcode NOT IN ('EXP','HO') 
GROUP BY a.BranchCode, a.CustomerID order by Branch

This query is executing within 2 seconds. The only difference between both the queries is b.RowID = a.id and b.CustomerID = a.CustomerID on my left join.

I create the index for all my join columns.

can anyone please suggest why the first query takes a long time to execute?

I uploaded my table dump here for your reference.

https://drive.google.com/file/d/1zQTJV-2ppQPjyEdxtOwAh_EVnOaQ-TOO/view?usp=sharing

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.