title: Count Queries slow down with increased number of records
body: <p>I have following structure of tables:</p>
  
  <p>tb_Students</p>
  
  <pre><code>CustomerId  StudentId  StudentName
   1             1        Michael
   1             2        Sam
   2             1        Declan
  </code></pre>
  
  <p>tb_Contact_Detail</p>
  
  <pre><code>StudentId ContactId CustomerId ContactName Relation
    1          1         1         Rory      Father
    1          2         1         Rachel    Mother
  </code></pre>
  
  <p>tb_Channel</p>
  
  <pre><code>ChannelId  ChannelName
     1        Emergency
     2        GeneralInfo
  </code></pre>
  
  <p>tb_Contact_Channel_Mapping</p>
  
  <pre><code>CustomerId ChannelId StudentId ContactId
     1         1         1          2
     1         2         1          2
     1         2         1          1
  </code></pre>
  
  <p>tb_Contact_Fields</p>
  
  <pre><code>CustomerId ContactId ContactType(Phone/SMS/Email)
     1         1          Phone
     1         1          Email
     1         1          SMS
     1         2          Email
     1         2          SMS
  
  
  SELECT @EmailCnt = ISNULL(SUM(CASE WHEN field.FieldType = 'Email' THEN 1 ELSE 0 END), 0),
         @PhoneCnt = ISNULL(SUM(CASE WHEN field.FieldType = 'Phone' THEN 1 ELSE 0 END), 0),
         @SMSCnt = ISNULL(SUM(CASE WHEN field.FieldType = 'SMS' THEN 1 ELSE 0 END), 0)
  FROM tb_Students stu WITH (NOLOCK)
       INNER JOIN tb_Contact_Detail det WITH (NOLOCK) ON stu.CustomerId = det.CustomerId
                                                     AND stu.StudentId = det.StudentId
       INNER JOIN tb_Contact_Channel_Mapping map WITH (NOLOCK) ON det.CustomerId = map.CustomerId
                                                              AND det.StudentId = map.StudentId
                                                              AND det.ContactId = map.ContactId
       INNER JOIN tb_Contact_Fields field WITH (NOLOCK) ON det.CustomerId = field.CustomerId
                                                       AND det.ContactId = map.ContactId
  WHERE stud.CustomerId = @CustomerId
    AND map.ChannelId = @ChannelId;
  
  For ChannelId=1, CustomerCode=1
  Result id EmailCount 2, PhoneCount 1, SMSCount 2
  </code></pre>
  
  <p>This query is being frequently used throughout multiple workflows. However issue now observed is that as the records of students and contacts and channels is increasing the results of this query slow down and time out. Is there any performance improvement or alternatives can be suggested for the same?</p>