How to improve query performance for multiple inner joins?

Question

I have a query which is taking 1 minute to execute. After spending some time on the query I found there is some part that is actually causing the query to take the time. Please see my comments below for the query mentioned.

Complete query:

SELECT DISTINCT 
    CSU.*, U.txtFirstName, U.txtLastName 
FROM
    tblCRMShallowUsers CSU (NOLOCK) 
INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 
WHERE 
    MSL.lngRights > 0
    AND U.lngStatus > 19
    AND U.ysnAdminFlag = 0
    AND G.lngStatus > 19
    AND G.ysnFrontEndGroup = 0
    AND (UGL.FK_lngGroupID = MSL.FK_lngGroupID
         OR UGL.FK_lngGroupID = 2) 
ORDER BY 
    ysnHasAccess DESC, txtLastName, txtFirstName

Below joins are performing quickly in the above query:

INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 

Here's the AND part in the above query that is really slowing it down. When I remove this join it was working very fast but the result set is not the result that was coming earlier.(it returns more data)

AND (UGL.FK_lngGroupID = MSL.FK_lngGroupID
     OR UGL.FK_lngGroupID = 2)

I would really appreciate if you can show some direction to optimize the query or some example or other way of writing the same query.

Answer 1

Because the optimal execution plan with each of the OR predicates differs, performance is improved by refactoring the single query as separate SELECT queries and a UNION operator. This allows the optimizer to choose the best plan for each query independently of the other. DISTINCT is not needed since UNION removes duplicate rows from the result.

SELECT
    CSU.*, U.txtFirstName, U.txtLastName 
FROM
    tblCRMShallowUsers CSU (NOLOCK) 
INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 
WHERE 
    MSL.lngRights > 0
    AND U.lngStatus > 19
    AND U.ysnAdminFlag = 0
    AND G.lngStatus > 19
    AND G.ysnFrontEndGroup = 0
    AND UGL.FK_lngGroupID = MSL.FK_lngGroupID
UNION
SELECT
    CSU.*, U.txtFirstName, U.txtLastName 
FROM
    tblCRMShallowUsers CSU (NOLOCK) 
INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 
WHERE 
    MSL.lngRights > 0
    AND U.lngStatus > 19
    AND U.ysnAdminFlag = 0
    AND G.lngStatus > 19
    AND G.ysnFrontEndGroup = 0
    AND UGL.FK_lngGroupID = 2 
ORDER BY 
    ysnHasAccess DESC, txtLastName, txtFirstName;

On a side note, be aware than NOLOCK and the READ_UNCOMMITTED isolation level may cause rows to be skipped or duplicated during allocation order scans if data are updated while the query is running. Dirty reads should be used only when concurrency is more important than correct results.