Ms sql курсор: DECLARE CURSOR (Transact-SQL) — SQL Server

Как оптимизировать SQL Server при работе с курсорами – adminbd

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

[ FOR UPDATE [ OF column_name [ ,…n ] ] ] [;]

Остановлюсь на первых трех строчках ключевых параметров.
LOCAL или GLOBAL:    если хотим, чтобы курсор был доступен другим процедурам, функциям, пакетам в рамках нашей сессии, то GLOBAL – в этом случае за удалением курсора следим сами (команда DEALLOCATE). Во всех остальных случаях (т.е. в подавляющем своем большинстве) – LOCAL. Внимание, по умолчанию создается именно GLOBAL курсор!
FORWARD_ONLY или SCROLL: если хотим ходить по курсору, как ненормальные, туда-сюда, то SCROLL, иначе – FORWARD_ONLY. Внимание, по умолчанию создается SCROLL курсор!
STATIC или KEYSET, DYNAMIC, FAST_FORWARD: если хотим, чтобы при проходе по курсору отображалась актуальная информация из таблицы (т. е., если после открытия курсора, мы поменяли информацию в одном из полей таблицы и хотим, чтобы при проходе по курсору в нужной строчке курсора была уже обновленная информация), то используем или KEYSET (если КАЖДАЯ таблица, участвующая в выборке, имеет уникальный индекс) или DYNAMIC (самый медленный тип). Если же нам нужен снимок результата выборки после открытия курсора – STATIC (самый быстрый тип – копия результата выборки копируется в базу tempdb и работаем уже с ней). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – пацаны из инета пишут, что STATIC дольше открывается (т.к. создается копия в tempdb), но быстрее работает, а FAST_FORWARD – наоборот. Так что если количество записей велико (насколько большое показывает практика), то применяем STATIC, иначе – FAST_FORWARD. Внимание, по умолчанию создается DYNAMIC курсор.

Таким образом, для большого кол-ва записей в большинстве случаев мой выбор:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statement

для небольшого кол-ва записей:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement

Теперь перейдем к практике (что собственно и подтолкнуло меня к писанине сего).
Испокон веков при объявлении курсора я применял конструкцию DECLARE … CURSOR LOCAL FOR…
При разработке интеграции с одной очень нехорошей базой, в которой нет ни одного индекса и не одного ключа, я применил тот же подход при объявлении курсоров, что и всегда. Выборка одного курсора содержала 225 000 записей. В результате процесс импорта данных из такой базы занял 15 часов 14 минут !!! И хотя импорт и был первичный (т.е. одноразовый), но даже для нормального тестирования такого импорта потребовалось бы несколько суток! После замены вышеназванной конструкции при объявлении курсора на DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. весь процесс импорта занял … внимание … 10 минут 5 секунд !!! Так что игра точно стоит свеч.
Хочу повториться, что идеальный вариант – это все же не использовать курсоры вообще – для СУБД MS SQL намного роднее реляционный, а не навигационный подход.

взято от сюда. записал что бы незабыть и не потерять

Similar Posts:

Что такое курсор в SQL Server и зачем он нужен?

Курсор SQL — это объект базы данных, который используется для манипулирования данными в наборе, выполнения построчной обработки вместо команд T-SQL, которые одновременно работают со всеми строками результирующего набора.

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

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

Как создать курсор SQL Server?

Рисунок 1. Жизненный цикл курсора SQL Server.

Процесс использования курсора SQL (начиная с MS SQL 2008) можно описать следующим образом:

1. Объявление курсора путем определения оператора SQL.
2. Открытие курсора для сохранения данных, извлеченных из набора результатов.
3. Строки могут быть выбраны из курсора одна за другой или в блоке для выполнения манипуляций с данными.
4. Курсор должен быть явно закрыт после обработки данных.
5. Курсоры должны быть освобождены, чтобы удалить определение курсора и освободить все системные ресурсы.

Синтаксис курсора

Рисунок 2 – Синтаксис курсора MS SQL Server.

Какие бывают типы курсоров в SQL Server?

Microsoft SQL Server поддерживает следующие типы курсоров.

1. СТАТИЧЕСКИЙ КУРСОР
   Заполняет набор результатов во время создания курсора, а результат запроса кэшируется на время существования курсора. Статический курсор может двигаться вперед и назад.
2. FAST_FORWARD (тип курсора по умолчанию)
   Он идентичен статическому, за исключением того, что вы можете прокручивать только вперед.
3. ДИНАМИЧЕСКИЙ
   Добавления и удаления видны другим в источнике данных, пока курсор открыт. В отличие от статических курсоров, все изменения, сделанные в динамическом курсоре, будут отражать исходные данные. Вы можете использовать этот курсор для выполнения операций INSERT, DELETE и UPDATE.
4. KEYSET
   Это похоже на динамический курсор, за исключением того, что мы не можем видеть записи, добавленные другими. Если другой пользователь удаляет запись, она становится недоступной из нашего набора записей.

Пример

В приведенном ниже примере все таблицы в образце базы данных с именем Spreadsheet переименовываются путем добавления «_Backup» к имени каждой таблицы, а также гарантируют, что таблицы, содержащие «_Backup» в своем имени, не будут переименованы снова, запустив следующий код. :

Рис. 3. Образец курсора SQL Server.

Каковы ограничения курсоров?

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

Проблема с курсором заключается в том, что в большинстве случаев мы можем использовать JOINS, даже предложения WHILE, пакеты SSIS или другие альтернативные методы, чтобы получить тот же результат быстрее, с меньшим влиянием на производительность и даже с написанием меньшего количества строк синтаксиса. .

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

Курсоры можно использовать в некоторых приложениях для сериализации, но в целом их следует избегать, поскольку они отрицательно влияют на производительность, особенно при работе с большим набором данных.

Альтернативы курсорам SQL Server

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

Мы будем использовать временную таблицу для переименования всех таблиц в образце, выполнив следующие действия:

1. Объявить временную таблицу;
2. Сохранить имена и идентификаторы таблиц;
3. Установите счетчик на 1 и получите общее количество записей из временной таблицы;
4. Используйте цикл while, если значение счетчика меньше или равно общему количеству записей;
5. В цикле переименовывать таблицы одну за другой, если они еще не переименованы, и увеличивать счетчик для каждой таблицы;

Рисунок 4. Альтернативный код для курсора SQL.

Выводы

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

Мы должны думать об операциях на основе наборов, а не по одной строке за раз, чтобы получить нужные нам данные. Использование циклов вроде FOR или FOREACH из языков программирования и связывание этой логики с операциями SQL является препятствием для получения правильного решения для наших нужд.

Вложенный курсор T-SQL в SQL Server для разработчиков баз данных

Разработчики SQL могут создать вложенный курсор в SQL Server, определив внешний курсор, и в коде курсора новый курсор определяется для каждой строки в основном выборе курсора.
Внутренний курсор создается, выполняется, закрывается и освобождается каждый раз во внешнем курсоре для каждой строки.

Короче говоря, вложенный курсор — это определение цикла в другом цикле в SQL Server.

Администраторы SQL Server и разработчики t-sql могут использовать вложенные курсоры для сложных структур.
Но при рассмотрении проблем с производительностью даже использование sql-курсора не предлагается, вложенный курсор — не первое решение, которое будет выбрано.

Я хочу дать образец кода t-sql с вложенным курсором, который использует образец базы данных AdventureWorks2008.
Этот пример вложенного курсора sql будет перебирать все отделы в таблице «Отдел» в базе данных sql AdventureWorks2008 и перечислять всех сотрудников, работающих в этом отделе, с помощью таблиц «Employee» и «EmployeeDepartmentHistory».


DECLARE
 @DepartmentID int, @DepartmentName nvarchar(50),
 @PersonID int, @FirstName nvarchar(50), @LastName nvarchar(50)

DECLARE Department_cursor КУРСОР ДЛЯ
SELECT DepartmentID, Name FROM HumanResources. Department

ОТКРЫТЬ Department_cursor;
ВЫБЕРИТЕ СЛЕДУЮЩИЙ ИЗ Department_cursor INTO @DepartmentID, @DepartmentName;

WHILE @@FETCH_STATUS = 0
BEGIN
 DECLARE employee_cursor CURSOR FOR
 SELECT
  E.BusinessEntityID, P.FirstName, P.LastName
 FROM HumanResources.EmployeeDepartmentHistory H
 INNER JOIN HumanResources.Employee E ON E.BusinessEntityID = H.BusinessEntityID
 INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
 ГДЕ 9003 2   H.EndDate IS NULL
  И H.DepartmentID = @DepartmentID

 OPEN employee_cursor;
 FETCH NEXT FROM employee_cursor INTO @PersonID, @FirstName, @LastName

 WHILE @@FETCH_STATUS = 0
 BEGIN
  PRINT
  CAST(@DepartmentID as varchar(10)) + ' ' +
  @DepartmentName + ' ' +
  CAST(@PersonID as varchar(10)) + ' ' +
  @FirstName + ' ' + @LastName
  FETCH NEXT FROM employee_cursor INTO @PersonID, @FirstName, @LastName
 END;
 CLOSE employee_cursor;
 DEALLOCATE employee_cursor;

 ВЫБЕРИТЕ СЛЕДУЮЩИЙ ИЗ Department_cursor INTO @DepartmentID, @DepartmentName;
КОНЕЦ
ЗАКРЫТЬ Department_cursor;
DEALLOCATE Department_cursor;

Код

Вывод вышеуказанного вложенного курсора SQL Server будет следующим:

Разработчики SQL всегда должны помнить об этом, если есть лучший способ решить проблему t-sql без использования курсоров sql, этот метод следует выбрать.