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.
Because the optimal execution plan with each of the
OR
predicates differs, performance is improved by refactoring the single query as separateSELECT
queries and aUNION
operator. This allows the optimizer to choose the best plan for each query independently of the other.DISTINCT
is not needed sinceUNION
removes duplicate rows from the result.On a side note, be aware than
NOLOCK
and theREAD_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.