• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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