CODEWITHSUNDEEP
sqlsql-serverjoinoptimization
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Optimising a join SQL Server

I am very inexperienced when it comes to optimising queries so any help would be much appreciated.

I currently have a 3 way join which takes + 10mins to execute. I am currently trying to test this so to have to wait that long is not ideal. Is there any way i can make it faster?

Current query:

SELECT  
DISTINCT
S.[No],
S.[Date],
S.[No],
L.[No],
C.[Name],
C.[E-Mail],
C.[Order]
FROM [Customer] C
JOIN [Line] L
ON C.[No] = L.[No] 
JOIN [Sale] S 
ON S.[No] = L.[No] 
WHERE S.[Date] >= '2017-04-01'
AND L.[Type] = '2'
AND C.[Type] = '2'

As far as im aware none of the columns have indexes. Which would be the best columns to add indexes to?

Tables rows:

Customer: 80,000 rows

Line: 550,000 rows

Sale: 1,300,000 rows

I have initially altered this to join the smaller tables first but it doesn't seem to have made much difference.

thanks

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is your query:

SELECT DISTINCT S.[No], S.[Date], S.[No], L.[No],
       C.[Name], C.[E-Mail], C.[Order]
FROM [Customer] C JOIN
     [Line] L
     ON C.[No] = L.[No] JOIN
     [Sale] S 
     ON S.[No] = L.[No] 
WHERE S.[Date] >= '2017-04-01' AND L.[Type] = '1' AND C.[Type] = '1';

Here are suggestions:

  1. Remove SELECT DISTINCT unless you really need it. That adds unnecessary overhead.
  2. Change '1' to just 1 if Type is numeric. I would guess it it.
  3. Add indexes on all the join keys.
  4. Check the joins . . . I would be surprised if the same column is used for all tables.
  5. You might want indexes on the columns in the WHERE clause. However, I suspect the join keys are the real performance issue.

Under normal circumstances, such a query on three tables of that size should not be taking 10 minutes, which is why I suspect bad join keys.

Upvotes: 2

Related Questions