title: What is an alternative to using subqueries in this SQL query that will give us better performance?
body: <p>We have a Transact-SQL query that contains subqueries in the where section.   Inner Join gives unwanted results of adding tOrderDetails fields multiple times so we resorted to the (poor performance) sub queries.   It is timing out often.  We need the same result but with better performance.  Any suggestions?</p>
  
  <pre><code>SELECT sum (cast(Replace(tOrders.TotalCharges,'$','') as money)) as TotalCharges
  FROM [ArtistShare].[dbo].tOrders
  WHERE tOrders.isGiftCardRedemption = 0
    and tOrders.isTestOrder=0
    and tOrders.LastDateUpdate between @startDate and @endDate
    and (SELECT count(tORderDetails.ID)
         from tORderDetails
         where tORderDetails.ORderID = tORders.ORderID
           and tOrderDetails.isPromo=1) = 0
    and (SELECT top 1 tProjects.ProjectReleaseDt
         from tProjects JOIN tOrderDetails
           on tOrderDetails.ProjectID = tProjects.ID
         where tOrderDetails.OrderID = tOrders.OrderID) &gt;= @startDate
  </code></pre>