Как избавиться от CURSOR, если для каждой FETCH требуется SELECT на основе предыдущего результата
У нас есть пара таблиц, назовем их «Sections» и «SectionTimeWindows». Таблица разделов содержит данные об участках «движения», которое начинается в каком-то месте, заканчивается в каком-то месте и имеет тип. Тип важен, поскольку он определяет, сколько времени займет раздел — каждый тип раздела имеет набор временных окон, и окно определяет время работы раздела в зависимости от того, когда мы доберемся до этого раздела.
Нам нужна новая таблица, в которой будут записаны StopTimes, если мы знаем DepartureTime. Мы начинаем с остановки в начале первого раздела в StopTime=DepartureTime, затем определяем RunningTime для каждого раздела на основе предыдущего StopTime и вставляем StopTime как новую строку. Это означает, что значения RunningTime и, следовательно, StopTime основаны на результатах, вычисленных для предыдущих строк.
В нашей текущей реализации КУРСОР используется для перебора разделов, сохранения текущего StopTime, проверки соответствующего окна на предмет следующего RunningTime, добавления RunningTime к StopTime и INSERT.
Упрощенная версия нашего кода такова:
CREATE TABLE #Sections
(
SectionID INT PRIMARY KEY IDENTITY,
SectionTypeID INT,
StartPlaceID INT,
EndPlaceID INT,
[Order] INT,
);
CREATE TABLE #SectionTimeWindows
(
SectionTimeWindowID INT PRIMARY KEY IDENTITY,
SectionTypeID INT,
WindowStart INT,
WindowEnd INT,
RunningTime INT,
)
CREATE TABLE #StopTimes
(
StopTimeID INT PRIMARY KEY IDENTITY,
PlaceID INT,
IncommingSectionID INT NULL,
ArrivalTime INT,
[Order] INT,
)
INSERT INTO #Sections (SectionTypeID, StartPlaceID, EndPlaceID, [Order]) VALUES
(1, 10, 20, 1),
(1, 20, 30, 2),
(1, 30, 40, 3),
(1, 40, 50, 4),
(1, 50, 60, 5)
INSERT INTO #SectionTimeWindows (SectionTypeID, WindowStart, WindowEnd, RunningTime) VALUES
(1, 00*60, 09*60, 30), -- from midnight to 9AM
(1, 09*60, 18*60, 50), -- from 9AM to 18AM
(1, 18*60, 24*60, 5) --- dtto
DECLARE @Departure INT = 8 * 60 -- the departure of the whole movement. Used at the first iteration only. This is a parameter of this (when this is converted to SP)
DECLARE @CurrentTime INT = NULL -- the stop time variable hold the current result of current interation
DECLARE @RunningTime INT = NULL -- the current iteration running time, based on window
DECLARE @CurrentSectionID INT;
DECLARE @CurrentSectionTypeID INT;
DECLARE @CurrentStartPlaceID INT;
DECLARE @CurrentEndPlaceID INT;
DECLARE @Order INT = 1;
DECLARE rowIterator CURSOR
LOCAL FAST_FORWARD FOR
SELECT SectionID, SectionTypeID, StartPlaceID, EndPlaceID
FROM #Sections
ORDER BY [Order]
OPEN rowIterator;
WHILE (1=1)
BEGIN
FETCH NEXT FROM rowIterator INTO @CurrentSectionID, @CurrentSectionTypeID, @CurrentStartPlaceID, @CurrentEndPlaceID
IF (@@FETCH_STATUS <> 0) BREAK;
IF (@CurrentTime is null) -- First iteration, we have no previous results, need @Departure as first StopTime
BEGIN
SET @CurrentTime = @Departure
INSERT INTO #StopTimes (PlaceID, IncommingSectionID, ArrivalTime, [Order])
VALUES (@CurrentStartPlaceID, NULL, @CurrentTime, @Order)
END
SET @Order = @Order + 1
-- Fetch @RunningTime from signle window where @CurrentStopTime is in the window and SectionType matches
SELECT TOP 1 @RunningTime = RunningTime FROM #SectionTimeWindows
WHERE SectionTypeID = @CurrentSectionTypeID AND WindowStart <= @CurrentTime AND @CurrentTime < WindowEnd
-- Add @RunningTime to @CurrentStopTime. This value will be used in next iteration to get new window
SET @CurrentTime = @CurrentTime + @RunningTime
INSERT INTO #StopTimes (PlaceID, IncommingSectionID, ArrivalTime, [Order])
VALUES (@CurrentEndPlaceID, @CurrentSectionID, @CurrentTime, @Order)
END
CLOSE rowIterator
DEALLOCATE rowIterator
SELECT StopTimeID, PlaceID, IncommingSectionID,
ArrivalTime / 60 as Hours, ArrivalTime % 60 as Minutes,
ArrivalTime - LAG(ArrivalTime) Over (ORDER BY [Order]) as RunningTime
FROM #StopTimes
DROP TABLE #Sections
DROP TABLE #StopTimes
DROP TABLE #SectionTimeWindows
Реальная структура немного сложнее, данные также группируются по MovementID и помимо ArrivalTime мы отслеживаем еще несколько свойств. Мы также можем подождать на StopPlace, что также задерживает отправление на следующий участок. Но в этом вся суть.
Вычисление реальной процедуры занимает до 6 часов (реальная таблица разделов содержит около 10 ^ 6 строк, а таблицаsectionTimeWindows — около 10 ^ 7 строк). Проблема, скорее всего, заключается в SELECT на каждой итерации FETCH, но я не знаю, как ПРИСОЕДИНИТЬСЯ к РазделTimeWindows, если у меня нет зонда для этого.
Я пытался решить эту проблему с помощью LAG, но ничего не добился, поскольку ArrivalTime предыдущей строки является рекурсивным результатом ее прибытия предыдущей строки. У меня та же проблема с SUM OVER(PARTITION..RANGE..), но опять же, я понятия не имею, как ПРИСОЕДИНИТЬСЯ к окну. Мне понадобится какая-то СУММА (ВЫБРАТЬ... ГДЕ ЗОНД)...
Есть ли способ удалить такой КУРСОР? А если нет, то есть ли способ ускорить SELECT TOP 1?
Вот, надеюсь, более четкое объяснение того, что должна делать функция:
- У нас есть набор разделов, каждый раздел имеет набор секцийTimeWindows, где каждое окно определяет интервал времени, в течение которого действительно время выполнения в разделе. Останавливаемся между секциями. Если мы прибудем в секцию между 00:00 и 9:00, Время выполнения в секции составит 30 минут; если мы приедем с 9:00 до 18:00, время работы составит 50 минут и т. д.
- Прибытие в раздел фиксируется в таблице StopTimes, столбец ArrivalTime. Существует N+1 StopTimes, поскольку мы также останавливаемся перед первым разделом.
- Прибытие в раздел определяется параметром ArrivalTime в предыдущий раздел + Время выполнения, заданное параметром DivisionTimeWindows, где Время прибытия в предыдущем разделе попадает в окно <WindowStart, WindowEnd)
Есть ли у вас правильные индексы, чтобы ТОП-1 хотя бы не просматривал всю таблицу? Сколько строкsectionTimeWindows в каждом разделе? Всегда ли 3?
Часть этой логики кажется запутанной. Например, у вас есть пункт WindowStart <= @Probe. @Prode определяется как @StopTime, а @StopTime определяется как @Departure. Почему бы просто не WindowStart <= @Departure?
Кроме того, ваш запрос с TOP не имеет ORDER BY, поэтому значение, присвоенное @RunningTime, будет совершенно произвольным. Я чувствую, что вам нужно сделать шаг назад и объяснить реальную логику перехода от А к Б.
Кажется, интервалы SectionTypeID в #SectionTimeWindowstable никогда не пересекаются. Вам не нужно Top(1).
@ThomA Информация взята из предыдущей строки результата StopTimes, а не из предыдущей строки входных разделов; @StopTime меняется на каждой итерации. @Departure используется только в первой итерации, а @Probe — это просто псевдоним. Я добавил @Probe для краткости и потому, что в реальной реализации @Probe необходим, так как есть также @StopTime и @WaitTime. Это может быть WindowStart <= @StopTime, да. Для TOP 1 не требуется никакого ORDER BY, поскольку для каждогоsectionTypeID существует только одно окно, куда попадает @Probe. Оно существует, потому что оно ускоряет выбор.
@siggemannen Да, индекс есть, иначе все это было бы бесполезно. Я думаю, проблема в том, что даже с индексом итеративный характер курсора делает его медленнее, поскольку он будет выполнять безумное количество поисков по индексу, и я думаю, что если бы объединение было возможно, сканирование индекса здесь было бы на самом деле лучше, возможно?
Без описания логики я не буду пытаться написать попытку, основанную на множествах. Как я уже говорил, происходит сильное «мутение воды» (@Probe = @StopTime = @Departure = 8 * 60 — это всего лишь пример). Если вы потратите время на объяснение цели, то выработать комплексное решение, скорее всего, будет намного проще.
Разве @Probe не увеличивается все время? Должен ли быть какой-то сброс, когда дело доходит до следующего дня? Или я что-то упускаю?
@ThomA Я немного обновил код, добавил комментарии и описание того, что должна делать логика.
@siggemannen На самом деле это так, есть родительский объект, который сбрасывает счетчик времени. Я пропустил это, поскольку это не имеет значения, и я могу РАЗДЕЛИТЬ реализацию этого на основе набора, если это возможно.
Это важно, потому что через некоторое время вы не получите никаких попаданий в таблицу #windows, что искажает результаты, по крайней мере, когда я тестирую. Кстати, это, наверное, глупо, но вы использовали: SET NOCOUNT ON при выполнении этой процедуры? С такой настройкой такие курсоры занимают вечность OFF. Может ли это движение длиться несколько дней?
@siggemannen да, я использую SET NOCOUNT ON и да, в этом примере окна закончатся, но в реальной реализации есть функция по модулю - когда мы приходим в 24:00, мы переносимся на 00:00. К сожалению, я не могу показать исходный код :( Я попытался выделить соответствующую часть моей проблемы.
Я предполагаю, что ты по модулю этой штучки-зонда сохранишь время окончания как есть? Спонтанно, я не думаю, что так просто отойти курсором, но вам придется каждый день пересчитывать эту штуку с нуля?
@siggemannen Я по модулю зонда, да, тогда я всегда получаю окно и всегда получаю RunningTime. Однако время прибытия постоянно увеличивается (без модуля) и может достигать нескольких дней, накапливая все значения времени выполнения. Мы пересчитываем его каждый раз, когда происходит миграция данных из старой системы, что в настоящее время происходит ежедневно. Процесс занимает до 6 часов (на плохом оборудовании я ничего не могу поделать. Локально это занимает 45 минут)
сколько существует различных вариантов окон? Возможно, там можно что-то сделать, чтобы избежать выбора? Например, можно сопоставить различные варианты и вместо этого объединиться против них.
Каждый раздел имеет свои собственные окна и время работы в этих окнах. Сами окна (отличающиеся началом и концом) несколько, но время работы меняется в зависимости от каждого участка (в зависимости от расстояния, например, улицы между перекрестками и пробки на дорогах, влияющие на время работы). ТаблицаsectionTimeWindows в моем примере уже представляет собой представление иерархии нескольких таблиц. Но в итоге у нас есть набор окон со временем выполнения для каждого раздела.
Хорошо, я сдаюсь :) Купите приличный компьютер, создайте к нему связанный сервер, запустите все там, а затем скопируйте обратно на «слабую» машину. Время разработки намного дороже, чем оборудование
@siggemannen Хе-хе, да, мне бы хотелось, но у нас большой срок развертывания, поэтому время разработки ничего не значит :( В любом случае, большое спасибо за попытку, у меня было нутром предчувствие, что у меня есть один пример CURSOR, где CURSOR удалить... сложно, но я попробую предварительное объединение окон в курсор, предложенное Лайошем Арпадом ниже, думаю, это может немного помочь...
Ответы 2
Вы можете присоединиться к #Sections с помощью #SectionTimeWindows:
SELECT S.SectionID, S.SectionTypeID, S.StartPlaceID, S.EndPlaceID, STW.Runtime
FROM #Sections S
JOIN #SectionTimeWindows STW
ON S.SectionTypeID = STW.SectionTypeID
ORDER BY [Order]
а затем просмотреть результаты без поиска разницы в окне. Если у вас есть несколько записей окон одного типа, вы можете SUM(Runtime) AS Runtime и использовать GROUP BY.
Кроме того, вы даже можете избавиться от цикла, используя insert-select, но тогда вам нужно будет соединить текущий элемент со следующим и иметь левое соединение для промежуточного элемента, который будет иметь значение null (доказывая, что next действительно является следующим) и вставка первой специальной записи отдельно. Но это большое изменение. Если проблема с производительностью сохранится, дайте мне знать.
Наконец, рассмотрим создание индексов, ускоряющих поиск.
Вы имеете в виду объединение окон по определению CURSOR, а затем пропуск итераторов итератора, когда окно неверно в соответствии с CurrentTime? Я могу попробовать это, хотя я не уверен, как мне показать результат... Отредактируйте вопрос и добавьте новый код? Также я не понимаю, что сумма WindowEnd-WindowStart - RunningTime имеет значение, если мое CurrentTime попадает в окно. Разница (= длина окна) произвольна (определяется пользователем).
Со вторым абзацем у меня возникли проблемы. Я хотел бы каким-то образом присоединиться к предыдущей строке результатов, но, поскольку эта строка зависит от всех предыдущих строк результатов, как мне это сделать? Какая-то рекурсия? Функция возврата? Индексы присутствуют, без них весь CURSOR в любом случае был бы непривычно медленным. Также есть «SET NOCOUNT ON».
@DavidCholt, второй абзац посвящен агрегированию. Вы можете группировать и суммировать. Да, пятый зависит от четвертого, а четвертый зависит от третьего, у вас есть STW.WindowEnd - STW.WindowStart первого + STW.WindowEnd - STW.WindowStart второго, что сводится к SUM().
Я думаю тут недоразумение, WindowEnd-WindowStart просто дает мне WindowSize, который меня не волнует. Все, что меня волнует, это RunningTime из окон. Но я попытался объединить записи окон в разделы, поскольку выбор внутри фильтра, и логика теперь пропускает окна, которые не имеют значения при получении RunTime... Это работает, но, к сожалению, производительность та же самая :(
@DavidCholt действительно, насколько я понимаю, WindowEnd - WindowStart - это продолжительность, то есть это моменты времени. Поскольку вас интересует Runtime, нам нужно использовать его вместо вычитания, которое я предлагал изначально. Отредактировал мой ответ соответственно.
Да, это то, что я реализовал, и это работает (т.е. дает правильные результаты), но производительность такая же или немного ниже. Но это подтвердило для меня, что выбор внутри CURSOR, по-видимому, не является проблемой, проблема в другом...
@DavidCholt просто из любопытства: сколько времени занимает выполнение запроса, который вы можете найти в моем ответе?
Я не делал полный прогон для всей таблицы, только TOP(3000) движений, но оригинал занимает около 6 секунд, а предварительно объединенный курсор занимает 7-8 секунд (оба дают одинаковые результаты по 45 тысяч+, так что это хорошо). Оно того стоило, поскольку, похоже, повторяющиеся выборы в курсорном решении не являются узким местом. Я попробую изучить этот путь, также удерживая курсор, но, к сожалению, не могу отметить 2 ответа как принятые. Однако это было полезно. Спасибо!
@DavidCholt, не беспокойся, рад помочь.
Расписание не зависит от мест остановок. Поэтому сначала делаем отдельное расписание (расписание) для каждогоsectionTypeId.
Из #SectionTimeWindows:
К расписанию:
Преобразовано в расписание. Часть расписания дляsectionTypeId=1. Начинаем с 8:00.
Затем присоедините места (отсортированные по [Порядок]) к расписанию.
Некоторая манипуляция с начальной точкой немного усложняет (запутывает) запрос.
См. пример
Данные испытаний:
with r as( -- recursively create timetable
select 0 lvl, null Num,SectionTypeID
,null WindowStart
,08*60 as WindowEnd
,null RunningTime
from #SectionTimeWindows w
where w.WindowStart=(select min(WindowStart) from #SectionTimeWindows w2
where w2.SectionTypeId=w.SectionTypeId)
union all
select lvl+1,coalesce(r.Num,-1)+1,w.SectionTypeID
,r.WindowEnd WindowStart
,r.WindowEnd+w.RunningTime WindowEnd
,w.RunningTime
from r inner join #SectionTimeWindows w
on r.SectionTypeId=w.SectionTypeId
and w.WindowStart<=r.WindowEnd and w.WindowEnd>r.WindowEnd
-- where lvl<17 -- for debug only
)
select
s.SectionTypeID, WindowStart, WindowEnd, RunningTime
,case when num is null then StartPlaceID else EndPlaceId end PlaceId
,case when num is null then null else SectionId end IncomingSectionId
,WindowEnd/60 hours
,WindowEnd%60 minutes
from r
inner join (select *, row_number()over(partition by SectionTypeId order by [Order] )rn
from #Sections) s
on r.SectionTypeId=s.SectionTypeId
and (r.Num=(s.rn-1) or (r.num is null and rn=1))
order by s.SectionTypeId,lvl;
Выход
Обновление 1.
Мы можем уменьшить глубину рекурсии.
Если размер окна (WindowEnd-WindowStart)>> RunningTime, мы должны осторожно брать строки рядом с WindowStart и WindowEnd с помощью рекурсии, а все остальные строки генерировать через generate_series.
-- timetable with reduced recursion
with r as(
select 0 lvl, 1 repeatCnt, null Num,SectionTypeID
,null WindowStart
,08*60 as WindowEnd
,null RunningTime
from #SectionTimeWindows w
where w.WindowStart=(select min(WindowStart) from #SectionTimeWindows w2
where w2.SectionTypeId=w.SectionTypeId)
union all
select lvl+1
,case when (w.WindowEnd-r.WindowEnd)>w.runningTime then -- multy row
floor((w.WindowEnd-r.WindowEnd)/w.runningTime)
else 1
end repeatCnt
,coalesce(r.Num,-1)+1,w.SectionTypeID
,r.WindowEnd WindowStart
,case when (w.WindowEnd-r.WindowEnd)>w.runningTime then -- multy row
r.WindowEnd+floor((w.WindowEnd-r.WindowEnd)/w.runningTime)*w.runningTime
else r.WindowEnd+w.RunningTime
end WindowEnd
,w.RunningTime
from r inner join #SectionTimeWindows w
on r.SectionTypeId=w.SectionTypeId
and w.WindowStart<=r.WindowEnd and w.WindowEnd>r.WindowEnd
where lvl<17
)
,ExpandedTimetable as(
select lvl,repeatCnt,Num,SectionTypeID
,WindowStart+RunningTime*(value-1) WindowStart
,WindowStart+RunningTime*(value) WindowEnd
,WindowStart grStart, WindowEnd grEnd
,RunningTime
,value n
from r
cross apply generate_series(1,r.repeatCnt)tn
)
select * from r order by SectionTypeId,lvl;
-- select * from ExpandedTimeTable order by SectionTypeId,lvl;
select *
,WindowEnd/60 hours
,WindowEnd%60 minutes
from ExpandedTimeTable order by SectionTypeId,lvl;
Вывод рекурсивного запроса составляет всего несколько строк. Глубина рекурсии равна 4.
Тогда мы сможем расширить график. В каждой строке до repeatNum строк.
По сути, это то, что я и предполагал — своего рода рекурсия на основе наборов для возврата данных. Я опробовал версию этой идеи на наших реальных данных (поскольку полное решение потребует полной переработки SP). Рекурсия настолько глубока, что не удается достичь предела рекурсии, поскольку в реальных данных время выполнения намного меньше → больше итераций рекурсии для вычисления расписания → предел рекурсии. Но я могу использовать фильтр отладки на уровне, чтобы получить первые 20 строк рекурсивного расписания для тестирования.
Мне удалось заставить работать вторую часть запроса, она дает немного странные результаты, но я думаю, что это моя ошибка, я слишком сильно упростил пример в вопросе (т. е. я объявил, что многие Секции имеют общий идентификатор секции, хотя на самом деле каждая секция имеет свой собственный идентификатор секции с собственными окнами), что вводит в образец данных логику, которой нет в реальных данных и которая, как мне кажется, используется при объединении расписаний с секциями. .
Тем не менее, мне удалось его немного настроить и получить некоторые результаты, однако, даже для одного движения и ограниченных уровней рекурсии, существует слишком много рекурсий, а таблица огромна (поскольку она в основном содержит все возможные RunTime за любое возможное время, когда мы можем прибыть в секцию). К сожалению, запрос выполняется на несколько порядков медленнее. Тем не менее, этот подход верен, и я собираюсь изучить его подробнее. В этом ответе также показан способ избежать курсора, поэтому я отмечу его. Спасибо!
Вы можете значительно уменьшить глубину рекурсии. Минимальная глубина рекурсии близка к числу строк в таблице #SectionTimeWindows. См. пример с уменьшенной глубиной рекурсии.
если вам нужна информация из предыдущей строки, почему бы не использовать
LAG? Какую версию SQL Server вы используете?