Версия для печати

На главную страницу

Замена курсоров и циклов While

Резюме

Получите большой прирост производительности, удаляя курсоры из вашего кода. Clinton Herring приводит пару примеров того, как он заменив курсоры в своем коде, снизил продолжительность обработки с 80 до 12 минут!

Введение

КУРСОРЫ и циклы WHILE. Кажется, мы не можем жить с ними и не можем жить без них, или все-таки можем?! Недавно на работе я должен был посмотреть некий рабочий код, который был написан не мной. Я тестировал производительность на новой машине (8 гигабайт RAM и 4 процессора с гипертредингом), и мы хотели сравнить ее производительность с одной из наших рабочих машин (4 гигабайта RAM и 4 более старых процессора). После обычной процедуры - создание резервных копий, восстановление, переиндексация и DBCC для 17-гигабайтной базы данных - мы решаем импортировать важное задание (job) и выполнить его. Хорошая новость заключалась в том, что, как и ожидалось, задание выполнилось значительно быстрее: 2 часа 35 минут свелись к 1 часу 19 минутам. Однако я немного оторопел, увидев, что код содержал внутренние курсоры, вложенные во внешний курсор. Для меня это БОЛЬШОЙ КРАСНЫЙ флаг, который говорит о том, что этот код выполняется не так, как следует.

Описание курсоров

Внутренние курсоры были попыткой соединить данные таблицы со стороны "многие" в связи "один ко многим" с данными в одностолбцовой таблице Output. Подход использовал цикл по всем первичным записям, задаваемым ключевым значением (1.7 миллионов записей), а затем цикл по вторичной таблице для каждой из этих первичных записей, изменяя и конкатенируя извлеченные данные для каждой из них в 3 различных переменных. Затем обновлялась таблица Output на основе совпадения с ID первичной записи, после чего процесс продолжался до тех пор, пока не были обновлены все выходные записи. Подход хорошо звучит, но является медленным - очень, очень медленным. Здесь много тормозов: объявление, открытие и перемещение по курсору, закрытие и освобождение ресурсов курсора. Я знал, что могу сделать лучше. Код курсора приведен ниже (измененный, чтобы скрыть всякую конкретную информацию о метаданных компании, где я работаю). Представлен только один внутренний курсор.

(Код объявления и начала внешнего курсора)

-- код внутреннего курсора … код только для обработки одного столбца
-- (предварительно объявляются переменные)
DECLARE temp_cursor CURSOR FOR
SELECT column_data
FROM DB.dbo.many_tbl
WHERE id = @tmp_id -- ключевые данные
ORDER BY column_data

OPEN temp_cursor

FETCH NEXT FROM temp_cursor

INTO @tmp_data

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @tmp_values = @tmp_values + convert(varchar(20), tmp_data) + ','

FETCH NEXT FROM temp_cursor
INTO @tmp_data
END

CLOSE temp_cursor
DEALLOCATE temp_cursor

UPDATE DB.dbo.OutPut_tbl
SET column_out = @tmp_values
WHERE id = @tmp_id

(Fetch next in outer Cursor)
(Close & Deallocate outer Cursor)

(Fetch next во внешнем курсоре)
(Закрытие и освобождение ресурсов курсора)

Замена внутренних курсоров

Еще в 2002 году я представил на SQL Server Central "Простую обработку таблицы" (Easy Table Pivot), и знал, что могу, по крайней мере, избавиться от внутреннего курсора (курсоров). Вот код, который непосредственно заменил вышеупомянутый внутренний курсор.

(Код объявления и начала внешнего курсора)

-- Непосредственное занесение вычисляемого столбца в переменную
-- (переменные предварительно объявлены),
SELECT @tmp_values = @tmp_values + convert(varchar(20), column_data) + ','
FROM DB.dbo.many_tbl
WHERE id = @tmp_id
ORDER BY column_data

UPDATE DB.dbo.OutPut_tbl
SET column_out = @tmp_values
WHERE id = @tmp_id

(Fetch next во внешнем курсоре),
(Закрытие и освобождение ресурсов внешнего курсора)

Замена внешнего курсора

Этот код проще и быстрее, чем курсор, и эта часть полного задания дала снижение времени выполнения с 52 минут до приблизительно 11 минут. Может ли быть что-нибудь лучше! Возможно, в SQL 7.0 Вы не сможете добиться большего, по крайней мере, я так думаю (весь вышеупомянутый код уже находился в хранимой процедуре), но что сказать о SQL2000? Анализируя вышеприведенный код, оператор select представляется подходящим кандидатом для скалярной функции. Я был уверен, что эта часть задания должна выполняться лучше, если задать в цикле единственное обновление с вызовом функции. Между прочим, я заменил курсор циклом WHILE, чтобы устранить потери времени на открытие, закрытие и освобождение ресурсов курсора. Так или иначе, вот та функция, которую я создал на основе того же самого оператора select.

CREATE function dbo.ufn_data_pivot(@id as int)
Returns varchar(20)
AS
BEGIN
DECLARE @value varchar(20)
SET @value = ''
SELECT @value = @value + convert(varchar(20), column_data) + ','
FROM DB.dbo.many_tbl
WHERE id = @id
ORDER BY column_data

Return @value
END

Запрос на обновление в основном коде, тогда будет, например, таким

UPDATE DB.dbo.OutPut_tbl
SET column_out = dbo.ufn_data_pivot(key_column)

Новые результаты

И как теперь выполняется этот код? Он буквально захлопнул двери перед старыми встроенными курсорами. Эта часть кода сократилась по времени выполнения до 2-х с небольшим минут! Примите во внимание, что у меня было три функций, вычисляющих значения для трех различных выходных столбцов, и ключевой набор данных составлял приблизительно 1.7 миллионов записей! ВОТ ТАК-ТО!

После внесения некоторых других изменений в других частях кода задания, все задание вместо 1 часа 20 минут на новой машине стало выполняться за немногим более 12 минут! Я (и еще кое-кто) были счастливы.

Заключение

Можете быть уверены, что я буду использовать каждую возможность, чтобы заменить весь ручной код выполнения цикла вызовами функций в SQL 2000 и не только, особенно на больших наборах данных.

07/04/2006

На главную страницу

  Версия для печати