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

Ответ 1

Потому что курсоры занимают память и создают блокировки.

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

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

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

Итак, как правило, курсоры не одобряются. Особенно, если первое решение пришло к решению проблемы.

Ответ 2

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

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

Невозможно достичь вышеуказанной цели, используя SQL-based.

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

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

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

READ_ONLY будет следить за тем, чтобы блокировки не находились в базовом наборе результатов. Изменения в базовом наборе результатов будут отражены в последующих выборках (так же, как если бы вы получили TOP 1 от вашего псевдо-курсора).

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

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

Ответ 3

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

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

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

Затем сделайте блок 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

Ответ 4

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

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

Ответ 5

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

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

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

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

Ответ 6

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

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

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

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

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

Ответ 7

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

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

Ответ 8

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

DECLARE @commandname NVARCHAR(1000) = '';

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

EXEC sp_executesql @commandname;

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

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

Gayam

Ответ 9

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

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

Ответ 10

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

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

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

SELECT * FROM @temptable WHERE [email protected] 

или

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

Такой подход, как показано в коде другого ответа, делает вещи намного хуже и не исправляет исходную проблему. Это анти-шаблон, называемый грузовым культовым программированием: не зная, ПОЧЕМУ что-то плохое и, таким образом, внедряя что-то хуже, чтобы этого избежать! Недавно я изменил этот код (используя #temptable и no index on 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);

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

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

Ответ 11

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

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

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