title: optimize complex sql query
body: <p>I am using azure sql server database. I have written one sql query to generate reprot. Here it is:</p>
  
  <pre><code>;WITH cte AS
  (
    SELECT ProjectID, CreatedDateUTC, ProductID, LicenseID, BackgroundID from Project p 
    WHERE  CAST(p.CreatedDateUTC AS DATE) &gt;= @StartDate and CAST(p.CreatedDateUTC AS DATE) &lt;= @EndDate 
    and IsBackgroundUsed = 1
    and s7ImageGenerated = 1  and p.SiteCode in ('b2c' )
  )
  SELECT ProjectID , CreatedDateUTC,
  (SELECT BackgroundName from Background b WHERE b.BackgroundID = cte.BackgroundID) AS BackgroundName,
  (SELECT Name FROM Product pr WHERE pr.ProductID = cte.ProductID) AS ProductName,
  Case WHEN LicenseID is null THEN 'Standard' ELSE (SELECT LicenseName from License l WHERE l.LicenseID = cte.LicenseID) END AS CLA,
  (SELECT PurchaseFG from Product_background pb WHERE pb.BackgroundID = cte.BackgroundID and pb.ProductId = cte.productID) AS PurchaseFG,
  (SELECT FGcode from Product pr WHERE pr.ProductID = cte.ProductID) AS ProductFGCode,
  --(Select dbo.[getProjectFGCodeByBackground](cte.ProductID, cte.BackgroundID)) AS FGCode,
  '' AS ERPOrderNumber,
  0 AS DesignQuanity
  from cte
  WHERE (SELECT count(*) from Approval.OrderDetail od WHERE od.ProjectID = cte.ProjectID) = 0
  </code></pre>
  
  <p>Is there any way to optimize this query. Timeout issue comes. I have written this query in store procedure and calling that store procedure using linq entity framework.</p>
  
  <p>Earlier i have used join but it's more slow down so tried with sub query. Worked more then one year now not working.</p>