• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Please increase performance of this extremely slow sql statement

Hi Experts,

I have a sql statement which is extremely slow.
I know, the reason are the two joins. But I don't know how to increase the performance.
Please help with reconstructing this statement.
Thanks a lot

Select 
coalesce(T2.Salesperson_Code, 'BLANK') AS Salesperson_Code, 
coalesce(T2.Company + '-' + T2.Salesperson_Code, T2.Company+'_BLANK') AS Salesperson_ID, 
coalesce(T3.Description, 'Area not defined') AS [Area Description], 
coalesce(T3.Code, T2.[Company]+'_BLANK') AS [Area Code], RTRIM(LTRIM(T3.Code)) + ' / ' + T3.Description AS Area, 

coalesce(T2.Salesperson_Code_2, 'BLANK') AS Salesperson_Code_2, 
coalesce(T2.Company + '-' + T2.Salesperson_Code_2, T2.Company+'_BLANK') AS Salesperson_2_ID,
coalesce(T4.Description, 'Area not defined') AS [Area2 Description],
coalesce(T4.Code, T2.[Company]+'_BLANK') AS [Area2 Code], RTRIM(LTRIM(T4.Code)) + ' / ' + T4.Description AS Area2, 
T2.Company AS Company, T2.Name As [Customer Name], RTRIM(LTRIM(T2.No_)) + ' / ' + T2.Name As [Customer Description] 

FROM 
       
[05_Customer] AS T2 LEFT OUTER JOIN
	VW_Sales_Area_Force_0 AS T3 ON T2.Salesperson_Code = T3.Salesperson_Code AND T2.Company = T3.Company,
[05_Customer] AS T5 LEFT OUTER JOIN
	VW_Sales_Area_Force_1 AS T4 ON T5.Salesperson_Code = T4.Salesperson_Code AND T5.Company = T4.Company

Open in new window

0
arthrex
Asked:
arthrex
  • 2
  • 2
3 Solutions
 
joriszwaenepoelCommented:
Why is there a T5 in your FROM-clause?  I don't see where it is used in the query.

Do you have indexes on the fields [Salesperson_Code] and [Company] in the [05_Customer] table?  That is probably the first thing you should look at.

HTH,

Joris
0
 
yuchingCommented:
Ya, should take out the T5, you can use T2 to join with T4 as below:-

Select  
coalesce(T2.Salesperson_Code, 'BLANK') AS Salesperson_Code,  
coalesce(T2.Company + '-' + T2.Salesperson_Code, T2.Company+'_BLANK') AS Salesperson_ID,  
coalesce(T3.Description, 'Area not defined') AS [Area Description],  
coalesce(T3.Code, T2.[Company]+'_BLANK') AS [Area Code], RTRIM(LTRIM(T3.Code)) + ' / ' + T3.Description AS Area,  
 
coalesce(T2.Salesperson_Code_2, 'BLANK') AS Salesperson_Code_2,  
coalesce(T2.Company + '-' + T2.Salesperson_Code_2, T2.Company+'_BLANK') AS Salesperson_2_ID,
coalesce(T4.Description, 'Area not defined') AS [Area2 Description],
coalesce(T4.Code, T2.[Company]+'_BLANK') AS [Area2 Code], RTRIM(LTRIM(T4.Code)) + ' / ' + T4.Description AS Area2,  
T2.Company AS Company, T2.Name As [Customer Name], RTRIM(LTRIM(T2.No_)) + ' / ' + T2.Name As [Customer Description]  
 FROM  [05_Customer] AS T2
LEFT OUTER JOIN VW_Sales_Area_Force_0 AS T3 ON T2.Salesperson_Code = T3.Salesperson_Code AND T2.Company = T3.Company,
LEFT OUTER JOIN VW_Sales_Area_Force_1 AS T4 ON T2.Salesperson_Code = T4.Salesperson_Code AND T2.Company = T4.Company
0
 
arthrexAuthor Commented:
Hi,
thank you both!
There are indexes on fields, Company, Salespersoncode and Salespersoncode2.
When I try to use T2 instead of T5 I get the error:
Incorrect Syntax near the keyword 'LEFT'

So I tried with bringing in the Customer Table a second time.

Please Note: the last line needs to be the join on T2.Salesperson_Code_2 = T4.Salesperson_Code instead of T2.Salesperson_Code = T4.Salesperson_Code . Was my fault, I posted it wrong.
Below the correct one.
But anyway. The problem is the same. And also the error message.
Thanks for your help.
Select  
coalesce(T2.Salesperson_Code, 'BLANK') AS Salesperson_Code,  
coalesce(T2.Company + '-' + T2.Salesperson_Code, T2.Company+'_BLANK') AS Salesperson_ID,  
coalesce(T3.Description, 'Area not defined') AS [Area Description],  
coalesce(T3.Code, T2.[Company]+'_BLANK') AS [Area Code], RTRIM(LTRIM(T3.Code)) + ' / ' + T3.Description AS Area,  
 
coalesce(T2.Salesperson_Code_2, 'BLANK') AS Salesperson_Code_2,  
coalesce(T2.Company + '-' + T2.Salesperson_Code_2, T2.Company+'_BLANK') AS Salesperson_2_ID, 
coalesce(T4.Description, 'Area not defined') AS [Area2 Description], 
coalesce(T4.Code, T2.[Company]+'_BLANK') AS [Area2 Code], RTRIM(LTRIM(T4.Code)) + ' / ' + T4.Description AS Area2,  
T2.Company AS Company, T2.Name As [Customer Name], RTRIM(LTRIM(T2.No_)) + ' / ' + T2.Name As [Customer Description]  
 FROM  [05_Customer] AS T2 
LEFT OUTER JOIN VW_Sales_Area_Force_0 AS T3 ON T2.Salesperson_Code = T3.Salesperson_Code AND T2.Company = T3.Company, 
LEFT OUTER JOIN VW_Sales_Area_Force_1 AS T4 ON T2.Salesperson_Code_2 = T4.Salesperson_Code AND T2.Company = T4.Company

Open in new window

0
 
joriszwaenepoelCommented:
What error message do you get?
I think there is a syntax error in your last query, and you don't need to add the comma before the second LEFT OUTER JOIN.

0
 
arthrexAuthor Commented:
The message was:
"Incorrect Syntax near the keyword 'LEFT'"
I deleted the comma. Now it works!! Fast!!
Thank you very much for you fast help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now