Avatar of BenthamLtd
BenthamLtd asked on

Left Join - very very slow

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

Open in new window

MySQL ServerSQL

Avatar of undefined
Last Comment
BenthamLtd

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
brad2575

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
dqmq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
fruey

Stick EXPLAIN in front of the query, run it on the server, and post that - it will help see what is indexed already and any other possible issues.
ASKER
BenthamLtd

Thank you, thank you, thank you, thank you, thank you, thank you.
Your help has saved me hundreds of hours of internet surfing.
fblack61