CODEWITHSUNDEEP
sqlsql-server
JCircio
JCircio

Reputation: 543

SQL Multiple Left Joins performance is bad

So, I have a rather simple query, which works, but very slow.

Is there a better way to optimize the query? Should I break out the LEFT JOINS on multiple columns? What about doing multiple runs and using Union ALL to join them together?

The tables have maybe 500,000 ROWS in them, and are indexed well. enter image description here Please help. Query takes forever to run

SELECT 
ep.Id,
ep.DisplayName,
ep.EmailAddress,
COALESCE(
    gs.DisplayNameMember + ' (' + gd.DisplayName + ')',
    ea.DisplayName ,
    ep.TrusteeUserAccountName,
    ep.Trustee
) AS Trustee

FROM 
dbo.ExchangePermissions ep WITH (NOLOCK)

LEFT JOIN dbo.GroupDetail gd WITH (NOLOCK) ON (ep.Trustee = gd.[Identity] OR ep.Trustee = gd.DisplayName OR ep.TrusteeUserAccountName = gd.SamAccountName)
LEFT JOIN dbo.GroupMemberShip gs WITH (NOLOCK) ON gd.name = gs.groupname
LEFT JOIN dbo.ExchangeAccount ea WITH (NOLOCK) ON (ep.Trustee = ea.[Identity] OR ep.Trustee = ea.DisplayName OR ep.TrusteeUserAccountName = ea.SamAccountName)

WHERE
ep.OTHERID= @MyParameter

Upvotes: 0

Views: 2184

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

The ORs in your LEFT JOINs are not helping performance at all. You should break them down into separate queries and UNION the results together. Working from Vykintas answer, we get the following:

SELECT 
ep.Id,
ep.DisplayName,
ep.EmailAddress,
COALESCE(
    gs.DisplayNameMember + ' (' + gd.DisplayName + ')',
    ep.TrusteeUserAccountName,
    ep.Trustee
) AS Trustee
FROM 
dbo.ExchangePermissions ep WITH (NOLOCK)
LEFT JOIN dbo.GroupDetail gd WITH (NOLOCK) ON ep.Trustee = gd.[Identity] 
LEFT JOIN dbo.GroupMemberShip gs WITH (NOLOCK) ON gd.name = gs.groupname
WHERE
ep.OTHERID= @MyParameter

UNION

SELECT 
ep.Id,
ep.DisplayName,
ep.EmailAddress,
COALESCE(
    gs.DisplayNameMember + ' (' + gd.DisplayName + ')',
    ep.TrusteeUserAccountName,
    ep.Trustee
) AS Trustee
FROM 
dbo.ExchangePermissions ep WITH (NOLOCK)
LEFT JOIN dbo.GroupDetail gd WITH (NOLOCK) ON ep.Trustee = gd.DisplayName
LEFT JOIN dbo.GroupMemberShip gs WITH (NOLOCK) ON gd.name = gs.groupname
WHERE
ep.OTHERID= @MyParameter

UNION

SELECT 
ep.Id,
ep.DisplayName,
ep.EmailAddress,
COALESCE(
    gs.DisplayNameMember + ' (' + gd.DisplayName + ')',
    ep.TrusteeUserAccountName,
    ep.Trustee
) AS Trustee
FROM 
dbo.ExchangePermissions ep WITH (NOLOCK)
LEFT JOIN dbo.GroupDetail gd WITH (NOLOCK) ON ep.TrusteeUserAccountName = gd.SamAccountName
LEFT JOIN dbo.GroupMemberShip gs WITH (NOLOCK) ON gd.name = gs.groupname
WHERE
ep.OTHERID= @MyParameter

Upvotes: 2

Related Questions