title: Decreasing execution time with sql left outer join query
body: <p>How can I decrease the execution time with this sql squery? it is taking over a minute to execute and return rows.(executed in Sql server management studio 2008)</p>
  
  <pre><code>if(@NotInvoiced =1)
  BEGIN
  Select @Command = 
  'select Distinct C.FileID, C.FileName, convert(nvarchar,C.DateReported,111) AS ''DateReported'', 
  C.FileDetailsPlainText, CFIT.Level3 as ''InvestigationType'', INV.FName + '' '' + INV.LName AS ''Name'', 
  convert(nvarchar,DD,111) AS ''DD'', i.FirstName + '' '' + i.LastName AS ''ReportedBy''
  from CaseFiles C 
  join InvestigatorCaseFileAccess IA on C.FileID=IA.CaseFileID 
  LEFT OUTER JOIN CaseFileTimeBills tb on C.FileID=tb.FileID
  LEFT OUTER JOIN CaseFileExpenses e on C.FileID=e.FileID
  
  left join Element07a_Persons i on i.PersonID = c.PersonID
  LEFT OUTER JOIN CaseFileInvestigationTypes CFIT ON C.InvestigationTypeID = CFIT.InvestigationTypeID 
  LEFT OUTER JOIN Investigators INV ON C.InvestigatorID = INV.InvestigatorID
  where (tb.InvNumber IS null OR e.InvNumber IS null) and Deleted=0 and IA.InvestigatorID=' + CONVERT (nvarchar(max),@InvestigatorID) + ' and IA.AllowAccess = ''True''
   and tb.FileID = C.FileID or e.FileID=C.FileID '
  END
  </code></pre>