title: Slow performing SQL query with triple self-join
body: <p>I have a <strong>legacy</strong> database with the following table (note: no primary key)</p>
  
  <p>It defines each a record for each accommodation "unit" and date, and the price for that date.</p>
  
  <pre><code>CREATE TABLE [single_date_availability](
      [accommodation_id] [int],
      [accommodation_unit_id] [int],
      [arrival_date] [datetime],
      [price] [decimal](18, 0),
      [offer_discount] [decimal](18, 0),
      [num_pax] [int],
      [rooms_remaining] [int],
      [eta_available] [int],
      [date_correct] [datetime],
      [max_occupancy] [int],
      [max_adults] [int],
      [min_stay_nights] [int],
      [max_stay_nights] [int],
      [nights_remaining_count] [numeric](2, 0)
  ) ON [PRIMARY]
  </code></pre>
  
  <p>The table contains roughly 16,500 records.</p>
  
  <p>But I need to multiply out the data in a completely different format, like such:</p>
  
  <ul>
  <li>Accommodation</li>
  <li>Date</li>
  <li>Duration</li>
  <li>Total price</li>
  </ul>
  
  <p>Up to a max duration for each arrival date.</p>
  
  <p>I'm using the following query to achieve this:</p>
  
  <pre><code>SELECT
      MIN(units.MaxAccommodationAvailabilityPax) AS MaxAccommodationAvailabilityPax,
      MIN(units.MaxAccommodationAvailabilityAdults) AS MaxAccommodationAvailabilityAdults,
      StartDate AS DepartureDate,
      EndDate AS ReturnDate,
      DATEDIFF(DAY, StartDate, EndDate) AS Duration,
      MIN(units.accommodation_id) AS AccommodationID, 
      x.accommodation_unit_id AS AccommodationUnitID,
      SUM(Price) AS Price,
      MAX(num_pax) AS Occupancy,
      SUM(offer_discount) AS OfferSaving,
      MIN(date_correct) AS DateTimeCorrect,
      MIN(rooms_remaining) AS RoomsRemaining,
      MIN(CONVERT(int, dbo.IsGreaterThan(ISNULL(eta_available, 0)+ISNULL(nights_remaining_count, 0), 0))) AS EtaAvailable
  FROM single_date_availability fp
  INNER JOIN (
      /* This gets max availability for the whole accommodation on the arrival date */
      SELECT accommodation_id, arrival_date,
          CASE EtaAvailable WHEN 1 THEN 99 ELSE MaxAccommodationAvailabilityPax END AS MaxAccommodationAvailabilityPax,
          CASE EtaAvailable WHEN 1 THEN 99 ELSE MaxAccommodationAvailabilityAdults END AS MaxAccommodationAvailabilityAdults
      FROM (SELECT accommodation_id, arrival_date, SUM(MaximumOccupancy) MaxAccommodationAvailabilityPax, SUM(MaximumAdults) MaxAccommodationAvailabilityAdults,
              CONVERT(int, WebData.dbo.IsGreaterThan(SUM(EtaAvailable), -1)) AS EtaAvailable                 
              FROM (SELECT accommodation_id, arrival_date, MIN(rooms_remaining*max_occupancy) as MaximumOccupancy,
                      MIN(rooms_remaining*max_adults) as MaximumAdults, MIN(ISNULL(eta_available, 0) + ISNULL(nights_remaining_count, 0) - 1) as EtaAvailable
                      FROM single_date_availability
                      GROUP BY accommodation_id, accommodation_unit_id, arrival_date) a 
              GROUP BY accommodation_id, arrival_date) b
  ) units ON fp.accommodation_id = units.accommodation_id AND fp.arrival_date = units.arrival_date
  INNER JOIN (
      /* This gets every combination of StartDate and EndDate for each Unit/Occupancy */
      SELECT DISTINCT a.accommodation_unit_id, StartDate = a.arrival_date,
          EndDate = b.arrival_date+1, Duration = DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1
          FROM single_date_availability AS a
          INNER JOIN (SELECT accommodation_unit_id, arrival_date FROM single_date_availability) AS b
          ON a.accommodation_unit_id = b.accommodation_unit_id
              AND DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1 &gt;= a.min_stay_nights
              AND DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1 &lt;= (CASE a.max_stay_nights WHEN 0 THEN 28 ELSE a.max_stay_nights END)
  ) x ON fp.accommodation_unit_id = x.accommodation_unit_id AND fp.arrival_date &gt;= x.StartDate AND fp.arrival_date &lt; x.EndDate
  GROUP BY x.accommodation_unit_id, StartDate, EndDate
  /* This ensures that all dates between StartDate and EndDate are actually available */
  HAVING COUNT(*) = DATEDIFF(DAY, StartDate, EndDate)
  </code></pre>
  
  <p>This works and gives me about 413,000 records. The results of this query I'm using to update another table.</p>
  
  <p>But the query performs quite badly, as you might expect with so many self-joins. It takes about 15 secs to run locally, but on our test server takes over 1:30 mins, and on our live SQL server takes over 30 secs; and in all cases it maxes out the CPU while it's performing the larger of the joins.</p>
  
  <p>No other processes are accessing the table at the same time, and that can be assumed.</p>
  
  <p>I don't really mind the length of the query so much as the demand on the CPU, which can cause problems for other queries trying to access other databases / tables at the same time.</p>
  
  <p>I have run the query through query optimizer and followed all the recommendations for indexes and statistics.</p>
  
  <p>Any help on making this query faster or at least less CPU intensive would be much appreciated. If it needs to be broken down into different stages, that's acceptable.</p>
  
  <p>To be honest speed is not so important as it's a bulk operation performed on a table that's not being touched by other processes. </p>
  
  <p>I'm not particularly looking for comments on how terrible and un-normalized this structure is... that, I already know :-)</p>