Вопрос:
Я задал здесь вопрос Использование курсора в базах данных OLTP (SQL-сервер)
где люди ответили, что курсоры никогда не должны использоваться.
Я считаю, что курсоры – это очень мощные инструменты, которые предназначены для использования (я не думаю, что Microsoft поддерживает курсоры для плохих разработчиков). Предположим, что у вас есть таблица, в которой значение столбца в строке зависит от значения тот же столбец в предыдущей строке. Если это одноразовый завершающий процесс, не считаете ли вы, что использование курсора будет приемлемым выбором?
Сверху моей головы я могу вспомнить пару сценариев, в которых я чувствую, что при использовании курсоров не должно быть стыда. Пожалуйста, дайте мне знать, если вы, ребята, почувствуете себя иначе.
1 > Однократный обратный процесс для очистки плохих данных, который завершает выполнение в течение нескольких минут.
2 > Пакетные процессы, которые запускаются один раз в течение длительного периода времени (что-то типа один раз в год).
Если в приведенных выше сценариях нет видимых деформаций для других процессов, не было бы необоснованным тратить лишние часы на написание кода, чтобы избежать курсоров? Другими словами, в некоторых случаях время разработки более важно, чем производительность процесса, который практически не влияет ни на что другое.
По-моему, это будут некоторые сценарии, в которых вы должны серьезно попытаться избежать использования курсора.
1 > Сохраненная процедура, вызванная с веб-сайта, которую можно вызвать очень часто.
2 > Задание SQL, которое будет выполняться несколько раз в день и потреблять много ресурсов.
Я думаю, что очень поверхностно сделать общее утверждение, такое как “курсоры никогда не должны использоваться”, не анализируя поставленную задачу и фактически взвешивая ее против альтернатив.
Пожалуйста, дайте мне знать о ваших мыслях.
Лучший ответ:
Существует несколько сценариев, где курсоры действительно работают лучше, чем эквиваленты на основе набора. Запуск итогов – это тот, который всегда приходит на ум – ищите слова Itzik на этом (и игнорируйте любые, которые включают SQL Server 2012, который добавляет новые функции окон, которые дают курсоры для их денег в этой ситуации).
Одна из больших проблем, с которыми люди сталкиваются с курсорами, заключается в том, что они работают медленно, они используют временное хранилище и т.д. Это частично объясняется тем, что синтаксис по умолчанию является глобальным курсором со всеми видами неэффективных параметров по умолчанию. В следующий раз, когда вы делаете что-то с помощью курсора, который не должен делать такие вещи, как UPDATE…WHERE CURRENT OF (который я смог избежать всей моей карьеры), дайте ему пошатнуть, сравнив эти два варианта синтаксиса:
DECLARE c CURSOR FOR <SELECT QUERY>; DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR <SELECT QUERY>;
Фактически первая версия представляет собой ошибку в недокументированной хранимой процедуре sp_MSforeachdb, которая заставляет ее пропускать базы данных, если при изменении состояния любой базы данных изменяется. Впоследствии я написал свою собственную версию хранимой процедуры (см. здесь и здесь), которые исправили ошибку ( просто используя последнюю версию синтаксиса выше) и добавил несколько параметров для контроля того, какие базы данных будут выбраны.
Многие считают, что методология не является курсором, потому что она не говорит DECLARE CURSOR. Я видел, как люди утверждают, что цикл while быстрее, чем курсор (который, я надеюсь, я развеял здесь) или что использование FOR XML PATH для выполнения групповой конкатенации не выполняя скрытую операцию курсора. Взгляд на план во многих случаях покажет правду.
В большинстве случаев курсоры используются там, где установлено множество оснований. Но существует множество допустимых вариантов использования, когда эквивалент, основанный на наборе, намного сложнее писать, так как оптимизатор генерирует план для обоих или не может (например, задачи обслуживания, в которых вы перебираете таблицы для обновления статистики, вызов хранимой процедуры для каждого значения в результате и т.д.). То же самое можно сказать и о множестве больших многосетевых запросов, в которых план становится слишком чудовищным для обработки оптимизатора. В этих случаях лучше сначала сбросить некоторые промежуточные результаты во временную структуру. То же самое касается некоторых эквивалентов на основе набора для курсоров (например, итоговых итогов). Я также написал о другом, где люди почти всегда инстинктивно думают использовать цикл while/курсор, и есть умные альтернативы на основе набора, которые намного лучше.
ОБНОВЛЕНИЕ 2013-07-25
Просто хотел добавить дополнительные записи в блоге, которые я написал о курсорах, какие параметры вы должны использовать, если вам нужно их использовать, а также использовать запросы на основе набора вместо циклов для создания наборов:
Лучшие подходы для выполнения итогов – Обновлено для SQL Server 2012
Какое влияние могут иметь различные параметры курсора?
Создать набор или последовательность без циклов: [Часть 1] [Часть 2] [Часть 3]
Ответ №1
Проблема с курсорами в SQL Server заключается в том, что движок встроен в набор, в отличие от других СУБД, таких как Oracle, которые внутренне ориентированы на курсоры. Это означает, что при создании курсора в SQL Server необходимо создать временное хранилище, и набор результатов на основе набора необходимо скопировать во временную память курсора. Вы можете понять, почему это стоило бы дорого с самого начала, не говоря уже о какой-либо пошаговой обработке, которую вы могли бы делать поверх самого курсора. Суть в том, что обработка на основе наборов более эффективна и часто, когда операция на основе курсора может быть выполнена лучше, используя CTE или временную таблицу.
Если говорить, есть случаи, когда курсор, вероятно, допустим, как вы сказали для одноразовых операций. Наиболее частое использование, о котором я могу думать, – это план обслуживания, в котором вы можете выполнять итерацию через все базы данных на сервере, выполняющие различные задачи обслуживания. До тех пор, пока вы ограничиваете свое использование и не разрабатываете все приложения вокруг обработки RBAR (строка за агонизацией), вы должны быть в порядке.
Ответ №2
В целом курсоры – это плохая вещь. Однако в некоторых случаях практичнее использовать курсор, а в некоторых – даже быстрее использовать его. Хорошим примером является курсор через таблицу контактов, отправляющую электронные письма на основе некоторых критериев. (Не открывать вопрос, если отправка электронной почты из вашей СУБД является хорошей идеей – давайте предположим, что это проблема.) Невозможно написать этот набор. Вы могли бы использовать некоторые хитрости, чтобы придумать комплексное решение для генерации динамического SQL, но реального решения на основе набора не существует.
Однако вычисление с использованием предыдущей строки может быть выполнено с использованием самосоединения. Обычно это быстрее, чем курсор.
Во всех случаях вам необходимо сбалансировать усилия, связанные с разработкой более быстрого решения. Если никто не заботится, если вы обрабатываете трассы за 1 минуту или через один час, используйте то, что быстрее выполняется. Если вы перебираете набор данных, который растет с течением времени, как таблица [orders], старайтесь держаться подальше от курсора, если это возможно. Если вы не уверены, выполните тест производительности, сравнивающий базу курсора с набором решений на нескольких существенно разных размерах данных.
Ответ №3
Они необходимы для таких вещей, как динамический SQL-поворот, но вы должны стараться избегать их использования, когда это возможно.
Ответ №4
Я всегда не любил курсоров из-за их медленной работы. Тем не менее, я обнаружил, что не полностью понял разные типы курсоров и что в некоторых случаях курсоры являются жизнеспособным решением.
Если у вас есть бизнес-проблема, которая может быть решена только обработкой одной строки за раз, то курсор подходит.
Чтобы улучшить производительность с помощью курсора, измените тип используемого вами курсора. Что-то, чего я не знал, если вы не укажете, какой тип курсора вы декларируете, по умолчанию вы получаете тип Dynamic Optimistic, который является самым медленным для производительности, потому что он делает много работы под капотом, Однако, объявив ваш курсор как другой тип, скажем, статический курсор, он имеет очень хорошую производительность.
См. эти статьи для более полного объяснения:
Я думаю, что самым большим препятствием для курсоров является производительность, однако, не выкладывая задачи в наборе, подход, вероятно, занимает второе место. В-третьих, это будет читаемость и макет задач, поскольку они обычно не имеют много полезных комментариев.
SQL Server оптимизирован для запуска основанного на наборе подхода. Вы пишете запрос, чтобы вернуть результирующий набор данных, например, например, объединение в таблицы, но механизм выполнения SQL Server определяет, какое соединение использовать: Merge Join, Nested Loop Join или Hash Join. SQL Server определяет наилучший возможный алгоритм объединения на основе участвующих столбцов, объема данных, структуры индексирования и набора значений в участвующих столбцах. Таким образом, использование подхода, основанного на наборе, как правило, является наилучшим подходом к производительности по сравнению с процедурным курсором.
