Почему считается плохой практикой использовать курсоры в SQL Server?

приведенные выше комментарии о том, что SQL является средой на основе набора, все верны. Однако бывают случаи, когда операции по строкам полезны. Рассмотрим комбинацию метаданных и dynamic-sql.

в качестве очень простого примера, скажем, у меня есть 100+ записей в таблице, которые определяют имена таблиц, которые я хочу скопировать/усечь/что угодно. Что лучше? Жесткий код SQL, чтобы сделать то, что мне нужно? Или повторите этот набор результатов и используйте dynamic-SQL (sp_executesql) для выполнения операции?

нет способа достичь вышеуказанной цели с помощью SQL на основе набора.

Итак, чтобы использовать курсоры или цикл while (псевдо-курсоры)?

SQL курсоры в порядке, пока вы используете правильные параметры:

нечувствительный сделает временную копию вашего результирующего набора (избавляя вас от необходимости делать это самостоятельно для вашего псевдо-курсора).

режим read_only будет убедиться, что нет блокировки на основной результирующий набор. Изменения в базовый набор результатов будут отражены в последующих выборках (так же, как если ТОП 1 из псевдо-курсора).

FAST_FORWARD создаст оптимизированный курсор только для чтения.

прочитайте о доступных вариантах, прежде чем управлять всеми курсорами как злом.

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

Я создаю переменную таблицы со столбцом идентификаторов в ней.

вставьте в него все данные, с которыми мне нужно работать.

затем сделайте блок while с переменной счетчика и выберите данные, которые я хочу из переменной таблицы с оператором select, где столбец идентификатора соответствует счетчику.

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

и код блока легко увидеть и обрабатывать.

Это простой пример:

DECLARE @TAB TABLE(ID INT IDENTITY, COLUMN1 VARCHAR(10), COLUMN2 VARCHAR(10))

DECLARE @COUNT INT,
        @MAX INT, 
        @CONCAT VARCHAR(MAX), 
        @COLUMN1 VARCHAR(10), 
        @COLUMN2 VARCHAR(10)

SET @COUNT = 1

INSERT INTO @TAB VALUES('TE1S', 'TE21')
INSERT INTO @TAB VALUES('TE1S', 'TE22')
INSERT INTO @TAB VALUES('TE1S', 'TE23')
INSERT INTO @TAB VALUES('TE1S', 'TE24')
INSERT INTO @TAB VALUES('TE1S', 'TE25')

SELECT @MAX = @@IDENTITY

WHILE @COUNT <= @MAX BEGIN
    SELECT @COLUMN1 = COLUMN1, @COLUMN2 = COLUMN2 FROM @TAB WHERE ID = @COUNT

    IF @CONCAT IS NULL BEGIN
        SET @CONCAT = '' 
    END ELSE BEGIN 
        SET @CONCAT = @CONCAT + ',' 
    END

    SET @CONCAT = @CONCAT + @COLUMN1 + @COLUMN2

    SET @COUNT = @COUNT + 1
END

SELECT @CONCAT

Я думаю, что курсоры получают плохое имя, потому что новички SQL обнаруживают их и думают: "Эй, цикл for! Я знаю, как ими пользоваться!- а потом они продолжают использовать их для всего.

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

SQL-это язык на основе набора-это то, что он делает лучше всего.

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

еще одна причина, по которой я не люблю курсоры-это ясность. Блок курсора настолько уродлив, что его трудно использовать четким и эффективным способом.

все что было сказано, есть are некоторые случаи, когда курсор действительно лучше-они просто обычно не являются случаи, что новички хотят использовать их для.

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

Я бы не назвал это "плохой практикой" использовать курсоры, но они потребляют больше ресурсов на сервере (чем эквивалентный подход на основе набора), и чаще всего они не нужны. Учитывая это, мой совет состоял бы в том, чтобы рассмотреть другие варианты, прежде чем прибегать к указатель.

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

один аргумент для использования курсора - это когда вам нужно обработать и обновить отдельные строки, особенно для набора данных, который не имеет хорошего уникального ключа. В этом случае вы можете использовать предложение FOR UPDATE, когда объявление курсора и процесс обновления с обновлением ... ГДЕ ТОК.

обратите внимание, что" серверные " курсоры были популярны (из ODBC и OLE DB), но ADO.NET не поддерживает их и АФАИК никогда не поддержит.

@ Daniel P - > вам не нужно использовать курсор, чтобы сделать это. Вы можете легко использовать теорию, основанную на множестве, чтобы сделать это. Например: с Sql 2008

DECLARE @commandname NVARCHAR(1000) = '';

SELECT @commandname += 'truncate table ' + tablename + '; ';
FROM tableNames;

EXEC sp_executesql @commandname;

просто делать то, что вы сказали выше. И вы можете сделать то же самое с SQL 2000, но синтаксис запроса будет разной.

однако, мой совет-избегать курсоров как можно больше.

Гаям

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

даже курсор быстрой перемотки вперед в Sql Server 2005 не может конкурировать с запросами на основе набора. График снижения производительности часто начинает выглядеть как операция n^2 по сравнению с set-based, которая имеет тенденцию быть более линейной, поскольку набор данных становится очень большим.

курсоры имеют свое место, однако я думаю, что это в основном потому, что они часто используются, когда одного оператора select достаточно для обеспечения агрегации и фильтрации результатов.

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

курсоры обычно не болезнь, а симптом ее: не используя подход на основе набора (Как упоминалось в других ответах).

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

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

SELECT * FROM @temptable WHERE Id=@counter 

или

SELECT TOP 1 * FROM @temptable WHERE Id>@lastId

такой подход, как показано в коде другого ответа, делает вещи намного хуже и не решает исходной задачи. Это анти-шаблон под названием культ карго программирования: не зная, почему что-то плохо и, таким образом, реализуя что-то хуже, чтобы избежать этого! Недавно я изменил такой код (используя #temptable и без индекса на identity/PK) обратно на курсор, и обновление чуть более 10000 строк заняло всего 1 секунду вместо почти 3 минуты. Все еще не хватает подхода на основе набора (будучи меньшим злом), но лучшее, что я мог сделать в этот момент.

var items = new List<Item>();
foreach(int oneId in itemIds)
{
    items.Add(dataAccess.GetItemById(oneId);
}

вместо

var items = dataAccess.GetItemsByIds(itemIds);

первый, как правило, наводнение базы данных с тоннами выбирает, один туда и обратно для каждого, особенно когда в игру вступают деревья/графики объектов, и печально известная проблема выбора N+1 поражает.

это прикладная сторона непонимания реляционных баз данных и подхода на основе набора, точно так же, как курсоры при использовании процедурного кода базы данных, такого как T-SQL или PL/SQL!

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

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

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