title: Optimizing sql query which runs slow with large data
body: <p>I am using microsoft sql server 2012. Created one query to get detail but it runs slow with large data. Here is the query:</p>
  
  <pre><code>SELECT p.BackgroundID,
         sum(od.OrderQuantity) AS number
  FROM approval.orderdetail od(nolock)
  JOIN project p(nolock) ON od.ProjectID = p.projectid
  AND p.BackgroundID IS NOT NULL
  WHERE CAST(p.ModifiedDateUTC AS DATE) BETWEEN @startDate AND @endDate
    AND EXISTS
      (SELECT backgroundid
       FROM background b
       WHERE b.BackgroundID = p.BackgroundID
         AND (@isActive = 2
              OR b.IsActive = @isActive)
         AND (@filtertype = 0
              OR ((@filtertype IN (3, 5, 7, 8)
                   AND b.licenseid IS NULL
                   AND b.IsAR = 0)
                  OR (@filtertype IN (1, 4, 5)
                      AND b.IsAR = 1)
                  OR (@filtertype IN (2, 4, 6)
                      AND b.IsLicensed = 1))))
  GROUP BY p.BackgroundID
  </code></pre>
  
  <p>Can anyone help me to optimize query? Any suggestions to optimize performance.</p>
  
  <p>Currently when i run this query first time with large data it tooks almost 1 min and then when i run again it tooks only 3 seconds.</p>
  
  <p>Here is execution plan:
  <a href="https://www.brentozar.com/pastetheplan/?id=Syupdn-44" rel="nofollow noreferrer">Execution Plan</a></p>