April 30, 2025 at 1:32 pm
I have a query that is performing poorly
It has a left join to a table with 2 conditions, the query execution plan is recommending an index but before adding the index I was reviewing and it seems when I place on of the conditions from left join into a where clause the performance improves
The data returned appears to be the same, is there an issue with doing this approach? I'd like to avoid adding unnecessary indexes if rewriting the query could help
April 30, 2025 at 2:18 pm
If the condition is on a LEF T JOIN, moving it to the WHERE will effectively convert the LEFT JOIN to an INNER JOIN.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 30, 2025 at 2:31 pm
As Scott points out moving the ON condition of a left join to the WHERE will make it an inner join.
But it is a good idea to try rewriting the query using different methods to see if performance can be improved. Here are some alternatives to a left join.
-- Assumed table structure:
-- Employees(EmployeeID INT, Name NVARCHAR(100), DepartmentID INT)
-- Departments(DepartmentID INT, DepartmentName NVARCHAR(100))
-- 1. LEFT JOIN
SELECT e.EmployeeID,
e.Name,
d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID
;
-- 2. OUTER APPLY
SELECT e.EmployeeID,
e.Name,
d.DepartmentName
FROM Employees e
OUTER APPLY (SELECT TOP(1) DepartmentName
FROM Departments d
WHERE d.DepartmentID = e.DepartmentID
) d
;
-- 3. NOT EXISTS + UNION ALL
SELECT e.EmployeeID,
e.Name,
d.DepartmentName
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
UNION ALL
SELECT e.EmployeeID,
e.Name,
NULL AS DepartmentName
FROM Employees e
WHERE NOT EXISTS (SELECT 1
FROM Departments d
WHERE d.DepartmentID = e.DepartmentID)
;
-- 4. Scalar Subquery
SELECT e.EmployeeID,
e.Name,
(SELECT d.DepartmentName
FROM Departments d
WHERE d.DepartmentID = e.DepartmentID) AS DepartmentName
FROM Employees e
;
-- 5. FULL JOIN filtered like LEFT JOIN
SELECT e.EmployeeID,
e.Name,
d.DepartmentName
FROM Employees e
FULL JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE e.EmployeeID IS NOT NULL
;
-- 6. EXISTS with CASE fallback
SELECT e.EmployeeID,
e.Name,
CASE WHEN EXISTS (SELECT 1
FROM Departments d
WHERE d.DepartmentID = e.DepartmentID)
THEN (SELECT d.DepartmentName
FROM Departments d
WHERE d.DepartmentID = e.DepartmentID)
ELSE NULL
END AS DepartmentName
FROM Employees e
;
April 30, 2025 at 3:11 pm
Yes I was thinking it would make it an inner join initially
Lets assume this is the query
-- Assumed table structure:
-- Employees(EmployeeID INT, Name NVARCHAR(100), DepartmentID INT, EmployeeTimeStamp DATETIME2(3))
-- Departments(DepartmentID INT, DepartmentName NVARCHAR(100), DepartmentValidFrom DATETIME2(3), DepartmentValidTo DATETIME2(3))
SELECT e.EmployeeID,
e.Name,
d.DepartmentName,
e.EmployeeTimeStamp
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
AND e.EmployeeTimeStamp >= d.DepartmentValidFrom
AND e.EmployeeTimeStamp < ISNULL(d.DepartmentValidTo, DATEADD(d, 1, SYSDATETIME()))
I have attached execution plan
April 30, 2025 at 6:09 pm
Would need to see row counts to better understand the query.
How is the Departments table clustered? In general, again without knowing any details at this point, DepartmentID first followed by DepartmentValidFrom, i.e., ( DepartmentID, DepartmentValidFrom ), would be best for this query (assuming the DepartmentValidFrom doesn't change too frequently).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 1, 2025 at 9:24 am
This was removed by the editor as SPAM
May 2, 2025 at 3:50 pm
There is 88 rows returned, so not too much data
It has clustered index on ID column.
Then there is a few indexes, including one for DepartmentValidFrom, DepartmentValidTo & DepartmentID
It does suggest an index which in the include column are the other columns in the select (my sample query is just a sample but there is additional columns)
Probably index creation is the best option
May 14, 2025 at 4:41 pm
index doens't seem to improve performance much.
What does, which is a bit strange is that in my left join it is based on 3 different criteria, when I change the order of the columns the performance is improved
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply