troubleshooting Question

Left Join - very very slow

Avatar of BenthamLtd
BenthamLtd asked on
MySQL ServerSQL
4 Comments1 Solution998 ViewsLast Modified:
Dear EE community,

Developed a system here for the company I work for. It is a telesales and out calling notes logging system. It pulls off a list of all customers, depending on the Part Code(s) used and then displays a list of all that have ordered. This list is pumped into an asp page and the results are displayed, 50 per page. This however, isn't the main issue.

As you can see from the SELECT statement, the main values retrieved are the Customer's Unique Ref No, Title, Forename, Surname, Telephone, their last order and the date a last note was made on their customer sheet.

The problem, therein lies with this left join:
left join outcall.callnotes on outcall.callnotes.Customer_URN = ijtd_export.customer.Customer_URN

Because, obviously, I still want customers to be displayed in the list, even if a note hasn't been put on their sheet. It works great but takes several minutes to pull off all the data from our linux SQL server.

Is there a quicker way of achieving this? At the moment, I have dropped this column from the system altogether as it was just too slow.

Thoughts and feelings would be most appreciated. If you need any other details, I will keep updating this thread. Thanks!


Luke Wellington
IT Engineer for Bentham Ltd t/a Inkjets & Toners
select
	ijtd_export.cart.Customer_URN
	ijtd_export.customer.Customer_Title
	ijtd_export.customer.Customer_Forename
	ijtd_export.customer.Customer_Surname
	ijtd_export.customer.Customer_Telephone
	ijtd_export.cart.Cart_Order_DateTime
	outcall.callnotes.CreatedDate
 
from
 
	ijtd_export.cart
	
	join ijtd_export.cartitem on ijtd_export.cartitem.Cart_URN = ijtd_export.cart.Cart_URN
	join ijtd_export.customer on ijtd_export.cart.Customer_URN = ijtd_export.customer.Customer_URN
	join ijtd_export.product on ijtd_export.product.Product_URN = ijtd_export.cartitem.Product_URN
	left join outcall.callnotes on outcall.callnotes.Customer_URN = ijtd_export.customer.Customer_URN
 
where
	cart.Cart_Status = 'Order' and
	cart.Cart_Order_Group = '1' and
	cart.Cart_Order_DateTime between '"&IncStartDate&"' and '"&IncEndDate&"' and
	cart.Cart_Order_DateTime between '"&ExcStartDate&"' and '"&ExcEndDate&"' and
	product.Product_Code IN ("&SPartCodes&") and
	
	NOT Customer_Surname like '' and
	NOT customer.Customer_Marketing like 'DEAD' and
	NOT customer.Customer_Telephone_Prefs like 'Do not call' and
	NOT EXISTS (select NULL from cart join cartitem on cartitem.Cart_URN = cart.Cart_URN join product on 			cartitem.Product_URN = product.Product_URN where cart.Customer_URN = customer.Customer_URN)
			
group by
 
	customer.Customer_URN
 
order by
 
	customer.Customer_Surname
	cart.Cart_Order_DateTime DESC
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros