title: SQL query takes much time to execute
body: <p>I have a table <code>Candidate</code> with 43,437 rows of data, which have been created by many employees.</p>
  
  <p>I want to get a report in which I get each employee's individual created candidate in a period of 7 days.. For that I executed the following query.</p>
  
  <pre><code>select E.Id, E.Employeename,
  (select 
      count(C.ID) 
  from 
      candidate C 
  where 
      CreatorID = E.ID  
      and C.Createdate between Convert(DateTime,'19/07/2014',103) And Convert(DateTime,'26/07/2014',103))
  
  From Employee E
  </code></pre>
  
  <p>where <code>E.ID</code> is employee's ID from <code>Employee</code> table..</p>
  
  <p>But the above query takes around 2 minutes to execute.. I didn't done indexing in <code>Candidate</code> table..</p>
  
  <p>How to make this query execute within few seconds..?</p>