У меня есть устаревшая база данных со следующей таблицей (примечание: без первичного ключа)
Она определяет каждую запись для каждой «единицы» размещения и даты, а также цена на эту дату.
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]
Таблица содержит примерно 16 500 записей.
Но мне нужно перемножить данные в совершенно другом формате, например:
До максимальной продолжительности для каждой даты прибытия.
Для этого я использую следующий запрос:
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 >= a.min_stay_nights
AND DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1 <= (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 >= x.StartDate AND fp.arrival_date < 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)
Это работает и дает мне около 413 000 записей. Результаты этого запроса я использую для обновления другой таблицы.
Но запрос выполняется довольно плохо, как и следовало ожидать при таком большом количестве самосоединений. Локальный запуск занимает около 15 секунд, но на нашем тестовом сервере это занимает более 1:30 минут, а на нашем живом сервере SQL - более 30 секунд; и во всех случаях он загружает процессор до максимума, пока выполняет большее из соединений.
Никакие другие процессы не обращаются к таблице одновременно, и это можно предположить.
Я не делаю ' Я действительно обращаю внимание на длину запроса и нагрузку на ЦП, что может вызвать проблемы для других запросов, пытающихся одновременно получить доступ к другим базам данных / таблицам.
Я выполнил запрос через оптимизатор запросов и выполнил все рекомендации по индексам и статистике.
Мы будем очень признательны за любую помощь по ускорению этого запроса или, по крайней мере, к меньшей загрузке процессора. Если нужно разбить ее на разные этапы, это приемлемо.
Честно говоря, скорость не так важна, поскольку это массовая операция, выполняемая над таблицей, которая не затрагивается другими процессами.
Я не особо ищу комментарии о том, насколько ужасна и ненормализована эта структура ... это я уже знаю: -)