Проблема с производительностью курсора SQL Server
Мне нужна помощь с оптимизацией курсора или полным изменением кода. У меня есть следующее требование:
Создайте столбец Sequence, сгруппированный по ColumnA, ColumnD и GroupA. StartA используется для сортировки. Пробовали использовать LAG, Row_Number и т. д., но безуспешно, поскольку последовательность группировки перезапускается при изменении столбца D с учетом столбца A (который может повторяться) и группы A, отсортированной по StartA.
Приведенный ниже код отлично работает для небольшого набора записей, но в последний раз, когда я запускал его, он занял более 3 часов и не был завершен, поэтому я закрыл задание. В таблице более 700 000 записей. Ищу любые советы о том, как это улучшить. Спасибо! Пример результата с использованием DENSE_RANK:
DECLARE
@ColumnA VARCHAR(10),
@StartA DATETIME,
@ColumnD VARCHAR(50),
@Sequence INTEGER,
@Sequence_Calc INTEGER = 1,
@Previous_ColumnA VARCHAR(10),
@Previous_ColumnD VARCHAR(50)
SELECT *
INTO #Temp_Table
FROM TABLEA
ORDER BY ColumnA,
ColumnD
DECLARE Seq_Cursor CURSOR
FOR SELECT ColumnA,
StartA,
ColumnD,
Sequence
FROM #Temp_Table
ORDER BY ColumnA,
ColumnD
FOR UPDATE OF Sequence
OPEN Seq_Cursor
FETCH NEXT FROM Seq_Cursor
INTO @ColumnA, @StartA, @ColumnD, @Sequence
WHILE @@FETCH_STATUS= 0
BEGIN
BEGIN
UPDATE #Temp_Table
SET Sequence = @Sequence_Calc
WHERE ColumnD = @ColumnD
AND StartA = @StartA
AND ColumnA = @ColumnA
SET @Previous_ColumnA = @ColumnA
SET @Previous_ColumnD = @ColumnD
END
FETCH NEXT FROM Seq_Cursor
INTO @ColumnA, @StartA, @ColumnD, @Sequence
BEGIN
SELECT @Sequence_Calc = CASE WHEN @Previous_ColumnD = @ColumnD THEN
CASE WHEN @Previous_ColumnA <> @ColumnA THEN @Sequence_Calc + 1 ELSE @Sequence_Calc END
ELSE 1 END
END
END
CLOSE Seq_Cursor
DEALLOCATE Seq_Cursor
Пожалуйста, добавьте пример данных в виде текста.
Я не хочу перепроектировать нерабочий код, поэтому не могли бы вы объяснить, как получается группа, поскольку опубликованные данные не имеют для меня смысла.
В соответствии с руководством по вопросам не публикуйте изображения кода, данных, сообщений об ошибках и т. д. — скопируйте или введите текст в вопрос. Пожалуйста, ограничьте использование изображений для диаграмм или демонстрации ошибок рендеринга, вещей, которые невозможно точно описать с помощью текста.
Не уверен, чего пытаются достичь ваши дополнительные блоки BEGIN/END в вашем цикле. Это не транзакция, это то, что вы предполагали.
не знаю, как добавить сюда текстовый файл @P.Salmon
@DaleK - есть ли способ добавить в эту тему образец текстового файла?
Вы не добавляете целый файл, вы добавляете достаточно образца текста, чтобы показать проблему, используя уценку таблицы.
Используйте Таблицы уценки, а не изображения. Вам придется объяснить точные шаги для получения результатов, объяснив все исключительные случаи, потому что на данный момент мы этого не понимаем. Похоже, что это логика «пробелов и островов», но на самом деле это неясно.
А пока оставьте тот факт, что ваш код не дает ожидаемого результата, и объясните, как получается ожидаемый результат. Я вижу, что строки до BB: 28/11/22 кажутся полученными на основе изменения. на columnsa и/или изменение на starta, но это не относится к BB:28/11/22 и следующей строке BB:01.12/22. аналогичный крайний случай имеет место в BB: 12.05.22.
И откуда взялась энда, ее нет в вашем коде или вопросе и, похоже, она не имеет какой-либо цели.
Чтобы добавить пример кода в виде текста (20 строк должно быть достаточно), запустите запрос для первых 20 строк изображения к таблице, возвращая выходные данные запроса в ssms в виде текста, скопируйте и вставьте его в вопрос или опубликуйте в скрипте (например, ️ 🔁 dbfiddle.uk ) вместе с определением таблицы для tablea (только соответствующие столбцы)
Ответы 1
Не знаю, зачем вы возитесь с курсорами, они медленные и неэффективные, их сложно писать и сложно понимать.
Действительно трудно сказать без более полного объяснения желаемой логики, но, похоже, это проблема пробелов и островов.
Вам нужно использовать
LAG, чтобы отметить строки, с которых начинается новая группа- Затем используйте оконное условное выражение
COUNT, чтобы создать идентификатор группы. - Затем используйте
ROW_NUMBER, разделенный по этому идентификатору.
WITH StartValues AS (
SELECT *,
CASE WHEN
ColumnA = LAG(ColumnA) OVER (PARTITION BY ColumnD ORDER BY StartA)
AND GroupA = LAG(GroupA) OVER (PARTITION BY ColumnD ORDER BY StartA)
THEN NULL ELSE 1 END AS IsStart
FROM TABLEA a
),
Grouped AS (
SELECT *,
COUNT(IsStart) OVER (PARTITION BY ColumnD ORDER BY StartA) AS GroupID
FROM Grouped
)
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ColumnD, GroupID ORDER BY StartA) AS Sequence
FROM Grouped;
Спасибо @Charlieface за предложение. Ранее я пробовал DENSE_RANK, но безуспешно. В приведенном примере последовательность группируется по столбцу A, что не дает желаемых результатов. Поэтому изменили код на DENSE_RANK() (разделение по ColumnD, порядок ColumnA по GroupA, StartA, ColumnA). Это работает для начальных строк, но не так, поскольку значения в столбце A продолжают повторяться. Добавление текстового файла с результатами. Есть ли у вас еще предложения?
это просто потрясающе! Именно то, что мне нужно, гораздо более простым и оптимизированным способом. Теперь этот процесс сократился с 8 часов до 9 секунд! Бесконечно благодарен!!!
Вы рассматривали возможность объединения курсора? SQL — это язык, основанный на множествах, он превосходно справляется с логикой, основанной на множествах, а не с итеративными задачами.