title: Why is cross apply making the query slow?
body: <p>I have a query which is taking about 11 seconds to run for a single date. I want to run the same query for more multiple days. In other words, I want to be able to return snapshots for multiple days. Here is my original query:</p>
  
  <pre><code>SELECT 
      COUNT(*) AS 'Number of Cars',
      d.ManufacturerName AS 'Make',
      d.Name AS 'Model', 
      c.name AS 'Car Class'
  FROM CarRating a 
      INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID
      INNER JOIN CarClass c ON a.CarClassID = c.CarClassID
      INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID
  
  WHERE 
      @myDate &lt; a.ExpiredWhen AND @myDate  &gt;= a.EffectiveWhen 
  GROUP BY 
      d.Name, c.name,d.ManufacturerName
  </code></pre>
  
  <p>Like I mentioned the query about takes 11 sec. In order to run this query for multiple dates, I am using a date table and cross applying it to the above query:</p>
  
  <pre><code>SELECT [DATE], b.* FROM DimDate 
  CROSS APPLY
      (SELECT 
          COUNT(*) AS 'Number of Cars',
          d.ManufacturerName AS 'Make',
          d.Name AS 'Model', 
          c.name AS 'Car Class'
      FROM CarRating a 
          INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID
          INNER JOIN CarClass c ON a.CarClassID = c.CarClassID
          INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID
  
      WHERE 
      dimDate.Date &lt; a.ExpiredWhen AND dimDate.Date &gt;= a.EffectiveWhen    
      GROUP BY 
      d.Name, c.name,d.ManufacturerName) b
  
   WHERE DimDate.Date between @StartDate and @EndDate
  </code></pre>
  
  <p>This query takes 49 sec even for one day. Why is this slow? Is there a better way of doing this?</p>