title: How to improve query performance for multiple inner joins?
body: <p>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.</p>
  
  <p>Complete query: </p>
  
  <pre><code>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 &gt; 0
      AND U.lngStatus &gt; 19
      AND U.ysnAdminFlag = 0
      AND G.lngStatus &gt; 19
      AND G.ysnFrontEndGroup = 0
      AND (UGL.FK_lngGroupID = MSL.FK_lngGroupID
           OR UGL.FK_lngGroupID = 2) 
  ORDER BY 
      ysnHasAccess DESC, txtLastName, txtFirstName
  </code></pre>
  
  <p>Below joins are performing quickly in the above query:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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)</p>
  
  <pre><code>AND (UGL.FK_lngGroupID = MSL.FK_lngGroupID
       OR UGL.FK_lngGroupID = 2)
  </code></pre>
  
  <p>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.</p>