Курсоры быстрой перемотки SQL Server

«Лучшая практика» отказа от курсоров в SQL Server восходит к SQL Server 2000 и более ранним версиям. Переписывание движка в SQL 2005 решило большинство проблем, связанных с проблемами курсоров, особенно с введением опции быстрой перемотки вперед. Курсоры не обязательно хуже, чем основанные на наборах, и широко и успешно используются в Oracle PL / SQL (LOOP).

«Общепринятое», о котором вы говорите, было действительным, но теперь оно устарело и неверно - исходите из предположения, что курсоры быстрой перемотки вперед ведут себя так, как заявлено, и работают. Проведите несколько тестов и исследований, основываясь на своих выводах на SQL2005 и более поздних версиях.

Хотя курсор ускоренной перемотки вперед в Sql Server 2005 имеет некоторые оптимизации, неверно, что они хоть сколько-нибудь близки к запросу на основе набора с точки зрения производительности. В очень редких случаях логика курсора не может быть заменена запросом на основе набора. Курсоры всегда будут медленнее по своей природе, отчасти из-за того, что вам нужно постоянно прерывать выполнение, чтобы заполнить ваши локальные переменные.

Вот несколько ссылок, которые станут лишь верхушкой айсберга, если вы исследуете эту проблему:

http://www.code-magazine.com/Article.aspx?quickid=060113

http://dataeducation.com/re-inventing-the-recursive-cte/ < / а>

В этом ответе мы надеемся объединить ответы, полученные на сегодняшний день.

1) Если это вообще возможно, используйте логику на основе наборов для ваших запросов, т.е. попробуйте использовать только SELECT, INSERT, UPDATE или DELETE с соответствующими предложениями FROM или вложенными запросами - они почти всегда будут быстрее.

2) Если это невозможно, то в SQL Server 2005+ FAST FORWARD курсоры эффективны и работают хорошо, и их следует использовать вместо циклов while.

«Если вам нужен даже более быстрый курсор, чем FAST FORWARD, используйте СТАТИЧЕСКИЙ курсор. Он быстрее, чем FAST FORWARD. Не очень быстро, но может иметь значение».

Не так быстро! Согласно Microsoft: «Обычно, когда происходят эти преобразования, тип курсора деградирует до« более дорогого »типа курсора. Как правило, курсор (FAST) FORWARD-ONLY является наиболее производительным, за ним следуют DYNAMIC, KEYSET и, наконец, STATIC, который обычно наименее эффективен ".

из: http://blogs.msdn.com/b/mssqlisv/archive/2006/06/23/644493.aspx

В большинстве случаев курсоры можно избегать, но иногда это необходимо.

Просто имейте в виду, что FAST_FORWARD - ДИНАМИЧЕСКИЙ ... FORWARD_ONLY, который можно использовать со СТАТИЧЕСКИМ курсором.

Попробуйте использовать его в задаче о Хеллоуине, чтобы увидеть, что произойдет !!!

IF OBJECT_ID('Funcionarios') IS NOT NULL
DROP TABLE Funcionarios
GO

CREATE TABLE Funcionarios(ID          Int IDENTITY(1,1) PRIMARY KEY,
                          ContactName Char(7000),
                          Salario     Numeric(18,2));
GO

INSERT INTO Funcionarios(ContactName, Salario) VALUES('Fabiano', 1900)
INSERT INTO Funcionarios(ContactName, Salario) VALUES('Luciano',2050)
INSERT INTO Funcionarios(ContactName, Salario) VALUES('Gilberto', 2070)
INSERT INTO Funcionarios(ContactName, Salario) VALUES('Ivan', 2090)
GO

CREATE NONCLUSTERED INDEX ix_Salario ON Funcionarios(Salario)
GO

-- Halloween problem, will update all rows until then reach 3000 !!!
UPDATE Funcionarios SET Salario = Salario * 1.1
  FROM Funcionarios WITH(index=ix_Salario)
 WHERE Salario < 3000
GO

-- Simulate here with all different CURSOR declarations
-- DYNAMIC update the rows until all of then reach 3000
-- FAST_FORWARD update the rows until all of then reach 3000
-- STATIC update the rows only one time. 

BEGIN TRAN
DECLARE @ID INT
DECLARE TMP_Cursor CURSOR DYNAMIC 
--DECLARE TMP_Cursor CURSOR FAST_FORWARD
--DECLARE TMP_Cursor CURSOR STATIC READ_ONLY FORWARD_ONLY
    FOR SELECT ID 
          FROM Funcionarios WITH(index=ix_Salario)
         WHERE Salario < 3000

OPEN TMP_Cursor

FETCH NEXT FROM TMP_Cursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT * FROM Funcionarios WITH(index=ix_Salario)

  UPDATE Funcionarios SET Salario = Salario * 1.1 
   WHERE ID = @ID

  FETCH NEXT FROM TMP_Cursor INTO @ID
END

CLOSE TMP_Cursor
DEALLOCATE TMP_Cursor

SELECT * FROM Funcionarios

ROLLBACK TRAN
GO

Люди избегают курсора, потому что его обычно сложнее написать, чем простые циклы while, однако цикл while может быть дорогостоящим, потому что вы постоянно выбираете данные из таблицы, временные или другие.

С помощью курсора, который быстро перематывается только для чтения, данные сохраняются в памяти и были специально разработаны для циклического воспроизведения.

В этой статье подчеркивается, что средний курсор работает в 50 раз быстрее, чем цикл while.

Некоторые альтернативы использованию курсора:

Циклы WHILE Temp tablolar Производные таблицы Связанные подзапросы Операторы CASE Множественные запросы Часто операции с курсором также могут выполняться без использования курсора.

Если вы уверены, что курсор необходимо использовать, количество обрабатываемых записей следует максимально сократить. Один из способов сделать это - сначала обработать записи во временной таблице, а не в исходной таблице, а в курсоре, который будет использовать записи во временной таблице. При использовании этого пути предполагается, что количество записей во временной таблице значительно уменьшено по сравнению с исходной таблицей. С меньшим количеством записей курсор завершается быстрее.

Некоторые свойства курсора, влияющие на производительность, включают:

FORWARD_ONLY: поддерживает пересылку только курсора из первой строки в конец с помощью FETCH NEXT. Если не задано значение KEYSET или STATIC, предложение SELECT повторно оценивается при вызове каждой выборки.

СТАТИЧЕСКИЙ: Создает временную копию созданных данных и используется курсором. Это предотвращает пересчет курсора при каждом его вызове, что повышает производительность. Это не позволяет изменять тип курсора, и изменения в таблице не отражаются при вызове выборки.

KEYSET: строки с курсором помещаются в таблицу в базе данных tempdb, и изменения неключевых столбцов отражаются при вызове выборки. Однако новые записи, добавленные в таблицу, не отображаются. С курсором набора ключей оператор SELECT больше не вычисляется.

ДИНАМИЧНЫЙ: все изменения в таблице отражаются в курсоре. Курсор повторно оценивается при каждом вызове выборки. Он использует много ресурсов и отрицательно сказывается на производительности.

FAST_FORWARD: курсор односторонний, например FORWARD_ONLY, но указывает, что курсор доступен только для чтения. FORWARD_ONLY - это повышение производительности, и курсор не переоценивается при каждой выборке. Он дает лучшую производительность, если подходит для программирования.

ОПТИМИСТИЧЕСКИЙ: этот параметр можно использовать для обновления строк в курсоре. Если строка выбирается и обновляется, а другая строка обновляется между операциями выборки и обновления, операция обновления курсора завершается ошибкой. Если используется OPTIMISTIC курсор, который может выполнять обновление строки, он не должен обновляться другим процессом.

ПРИМЕЧАНИЕ. Если cursore не указан, по умолчанию используется FORWARD_ONLY.

Чтобы ответить на оригинальные вопросы Майл ...

Быстрая перемотка вперед, только для чтения, статические курсоры (ласково известные как «Курсор пожарного шланга») обычно работают так же быстро или быстрее, как эквивалентная таблица темпов и цикл «Пока», потому что такой курсор является не чем иным, как таблицей темпов и циклом «Пока», который был немного оптимизирован за кулисами.

Чтобы добавить к тому, что Эрик З. Бирд разместил в этой ветке, и дополнительно ответить на вопрос ...

«Разве все разговоры о том, что не используются курсоры, действительно об отказе от использования курсоров, когда доступны подходы на основе наборов, и об использовании обновляемых курсоров и т. Д.»

да. За очень немногими исключениями требуется меньше времени и меньше кода для написания правильного основанного на наборах кода, который выполняет то же самое, что и большинство курсоров, и имеет дополнительное преимущество в виде использования гораздо меньшего количества ресурсов и обычно выполняется НАМНОГО быстрее, чем курсор или цикл While. Вообще говоря, за исключением некоторых административных задач, их действительно следует избегать в пользу правильно написанного кода, основанного на наборах. Конечно, есть исключения из каждого «правила», но в случае курсоров, циклов While и других форм RBAR большинство людей может подсчитывать исключения на одной руке, не задействуя все пальцы. ;-)

Также есть понятие «Скрытый RBAR». Это код, который выглядит как набор, но на самом деле это не так. Этот тип «основанного на множестве» кода является причиной того, почему некоторые люди приняли методы RBAR и говорят, что они «в порядке». Например, решение проблемы промежуточной суммы с использованием агрегированного (SUM) коррелированного подзапроса с неравенством в нем для построения промежуточной суммы на самом деле не основано на наборах в моей книге. Вместо этого это RBAR на стероидах, потому что для каждой вычисленной строки он должен многократно «касаться» многих других строк со скоростью N * (N + 1) / 2. Это известно как «треугольное соединение» и по крайней мере вдвое хуже, чем полное декартово соединение (перекрестное соединение или «квадратное соединение»).

Хотя MS внесла некоторые улучшения в работу курсоров с момента выхода SQL Server 2005, термин «быстрый курсор» по-прежнему является оксюмороном по сравнению с правильно написанным кодом, основанным на наборах. Это также верно даже в Oracle. В прошлом я работал с Oracle всего 3 года, но моя работа заключалась в улучшении производительности существующего кода. Большинство действительно существенных улучшений были реализованы, когда я преобразовал курсоры в код, основанный на наборах. Многие задания, выполнение которых раньше занимало от 4 до 8 часов, сократились до минут, а иногда и секунд.

Если вам нужен еще более быстрый курсор, чем FAST FORWARD, используйте СТАТИЧЕСКИЙ курсор. Они быстрее, чем FAST FORWARD. Не очень быстро, но может иметь значение.