Почему реляционные основанные на наборе запросы лучше, чем курсоры?

Главная причина, что я знаю, состоит в том, что основанные на наборе операции могут быть оптимизированы механизмом путем выполнения их через несколько потоков. Например, думайте о quicksort - можно разделить список, Вы сортируете в несколько "блоков" и вида каждого отдельно в их собственном потоке. Механизмы SQL могут сделать подобные вещи с огромными объемами данных в одном основанном на наборе запросе.

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

В дополнение к вышеупомянутому "позволяет DBMS сделать работу" (который является отличным решением), существует пара других серьезных оснований оставить запрос в DBMS:

• (субъективно) легче читать. При рассмотрении кода позже, Вы попытались бы проанализировать сложную хранимую процедуру (или клиентский код) с циклами и вещами, или Вы посмотрели бы на краткий SQL-оператор?
• Это избегает сетевых распространений в прямом и обратном направлениях. , Почему толчок все эти данные клиенту и затем пихает больше назад? Почему перегружают сеть, если Вы не должны?
• Это расточительно. Ваш DBMS и сервер (серверы) приложений должны будут буферизовать некоторые/все из тех данных для работы над ним. Если у Вас не будет бесконечной памяти, то Вы, вероятно, разобьете на страницы другие данные; почему выгоняют возможно важные вещи из памяти для буферизации набора результатов, который главным образом бесполезен?
• , Почему не был бы Вы? Вы купили (или иначе используют), высоконадежный, очень быстрый DBMS. Почему Вы не использовали бы его?

Основанные на наборе запросы (обычно) быстрее потому что:

1. у Них есть больше информации для оптимизатора запросов для оптимизации
2. , Они могут обработать чтения в пакетном режиме от диска
3. , Там меньше регистрируется включенный для откатов, журналов транзакций, и т.д.
4. , Меньше блокировок взято, который уменьшается наверху
5. , основанная на наборе логика является фокусом RDBMSs, таким образом, они были в большой степени оптимизированы для него (часто, за счет процедурной производительности)

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

А простой курсор с процедурной логикой по сравнению с основанным на наборе примером (T-SQL), который присвоит код области на основе телефонной станции:

--Cursor
DECLARE @phoneNumber char(7)
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
   SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL
OPEN c
FETCH NEXT FROM c INTO @phoneNumber
WHILE @@FETCH_STATUS = 0 BEGIN
   DECLARE @exchange char(3), @areaCode char(3)
   SELECT @exchange = LEFT(@phoneNumber, 3)

   SELECT @areaCode = AreaCode 
   FROM AreaCode_Exchange 
   WHERE Exchange = @exchange

   IF @areaCode IS NOT NULL BEGIN
       UPDATE Customer SET AreaCode = @areaCode
       WHERE CURRENT OF c
   END
   FETCH NEXT FROM c INTO @phoneNumber
END
CLOSE c
DEALLOCATE c
END

--Set
UPDATE Customer SET
    AreaCode = AreaCode_Exchange.AreaCode
FROM Customer
JOIN AreaCode_Exchange ON
    LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange
WHERE
    Customer.AreaCode IS NULL

Вы хотели некоторые реальные примеры. Моя компания имела курсор, который принял 40 минут для обработки 30 000 записей (и были времена, когда я должен был обновить более чем 200 000 записей). Это взяло 45-секундный, чтобы сделать ту же задачу без курсора. В другом случае я удалил курсор и отправил время обработки из-за 24 часов к меньше чем минуте. Каждый был вставкой с помощью пункта значений вместо выбора, и другой было обновление, которое использовало переменные вместо соединения. Хорошее эмпирическое правило - то, что, если это - вставка, обновление, или удаляют, необходимо искать основанный на наборе способ выполнить задачу.

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

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

использование трети А курсора должно работать, система сохранила procs через группу входных значений. Так как это ограничено обычно маленьким набором, и никто не должен смешивать с системой procs, это - приемлемая вещь для adminstrator, чтобы сделать. Я не рекомендую делать, то же самое с созданным пользователем сохранило proc для обработки большого пакета и снова использовать код. Лучше записать основанную на наборе версию, которая будет лучшим исполнителем, поскольку производительность должна превзойти повторное использование кода в большинстве случаев.

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

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

2. преимущество для использования SQL состоит в том, что объем работы для оптимизации обрабатывается механизмом базы данных при большинстве обстоятельств. С механизмами дб класса предприятия разработчики перешли к кропотливым длинам, чтобы удостовериться, что система эффективна при обработке данных. Недостаток состоит в том, что SQL является основанным на наборе языком. Необходимо смочь определить ряд данных для использования его. Хотя это звучит легким, при некоторых обстоятельствах это не. Запрос может быть столь сложным, что внутренние оптимизаторы в механизме не могут эффективно создать путь выполнения и предположить то, что происходит..., Ваше супер мощное поле с 32 процессорами использует единственный поток для выполнения запроса, потому что это не знает, как сделать что-либо еще, таким образом, Вы тратите впустую процессорное время на сервер базы данных который обычно существует только один из в противоположность нескольким серверам приложений (поэтому назад для обоснования 1, Вы сталкиваетесь с конкуренциями ресурса с другими вещами, бывшими должными работать на сервере базы данных). С находящимся на строке языком (C#, PHP, JAVA и т.д.), Вы имеете больше контроля относительно того, что происходит. Можно получить набор данных и вынудить его выполнить способ, к которому Вы хотите его. (Выделите набор данных для работы нескольких потоков и т.д.). Большую часть времени это все еще не будет эффективным как выполнение его на механизме базы данных, потому что это должно будет все еще получить доступ к механизму для обновления строки, но когда необходимо сделать 1000 + вычисления для обновления строки (и позволяет, говорят, что у Вас есть миллион строк), сервер базы данных может начать иметь проблемы.

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

Функционально, штраф за курсоры будет варьироваться чрезвычайно от продукта до продукта. Некоторые (большинство?) rdbmss создаются по крайней мере частично сверху isam механизмов. Если вопрос является соответствующим, и достаточно тонкая фанера, могло бы на самом деле быть столь же эффективно использовать курсор. Но это - одна из вещей, необходимо стать глубоко знакомыми с, с точки зрения бренда DBMS, прежде, чем попробовать ее.

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

, Например, просто посмотрите на способ, которым присоединяется база данных. Путем взгляда на объясняют планы, Вы видите несколько способов сделать соединения. Скорее всего, с курсором Вы идете строка строкой в одной таблице и затем выбираете значения, в которых Вы нуждаетесь от другой таблицы. В основном это похоже на вложенный цикл только без плотности цикла (который, скорее всего, скомпилирован на машинный язык и супер оптимизирован). SQL Server самостоятельно имеет целый набор способов присоединиться. Если строки будут отсортированы, то это будет использовать некоторый тип алгоритма слияния, если одна таблица будет маленькой, это может превратить одну таблицу в справочную таблицу хеша и сделать соединение путем выполнения O (1) поиски от одной таблицы в справочную таблицу. Существует много стратегий соединения, что многие, которых DBMS имеет, который изобьет Вас ищущий значения от одной таблицы в курсоре.

Только посмотрели на пример создания справочной таблицы хеша. Создавать таблицу - вероятно, m операции, если Вы присоединяетесь к двум таблицам одна из длины n и одна из длины m, где m является меньшей таблицей. Каждый поиск должен быть постоянным временем, так, чтобы были n операции. таким образом, в основном эффективность хэширования вокруг m (установка) + n (поиски). Если Вы делаете это сами и принимающий поиски/индексы, то для каждой из n строк необходимо будет искать записи m (в среднем, это приравнивается к поискам m/2). Таким образом, в основном уровень операций идет от m + n (присоединение к набору записей сразу) к m * n / 2 (выполнение поисков через курсор). Также операции являются упрощениями. В зависимости от типа курсора, выбирая каждую строку курсора может совпасть с выполнением другого выбора из первой таблицы.

Блокировки также уничтожают Вас. Если у Вас есть курсоры на таблице, Вы запираете строки (в SQL-сервере, это менее серьезно для статических и forward_only курсоров..., но большинство кода курсора, который я вижу просто, открывает курсор, не указывая ни одной из этих опций). Если Вы сделаете операцию в наборе, то строки будут все еще заперты, но для меньшего количества времени. Также оптимизатор видит то, что Вы делаете, и он может решить, что более эффективно заблокировать целую таблицу вместо набора строк или страниц. Но если Вы идете линию за линией, оптимизатор понятия не имеет.

другая вещь, я услышал, что в случае Oracle она супер оптимизирована, чтобы сделать операции курсора, таким образом, это нигде не около того же штрафа за основанные на наборе операции по сравнению с курсорами в Oracle, как это находится в SQL Server. Я не эксперт Oracle, таким образом, я не могу сказать наверняка. Но больше чем один человек Oracle сказал мне, что курсоры являются путем, более эффективным в Oracle. Таким образом, при принесении в жертву родившегося первым сына за Oracle, Вам не, вероятно, придется волноваться о курсорах, консультироваться с Вашей локальной высокооплачиваемой Oracle DBA :)

Идея позади предпочтения сделать работа в запросах состоит в том, что механизм базы данных может оптимизировать путем переформулирования его. Это также, почему Вы хотели бы работать, ОБЪЯСНЯЮТ на Вашем запросе, для наблюдения то, что дб на самом деле выполнение. (например, использование в своих интересах индексов, размеров таблицы и иногда даже знания о дистрибутивах значений в столбцах.)

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

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

базирующийся набор сделан в одном операционном курсоре столько же операций сколько набор строк курсора

НАСТОЯЩИЙ ответ - взять одну из книг Э. Ф. Кодда и освежить в памяти реляционную алгебру . Тогда возьмите хорошую книгу по нотации Big O . После почти двух десятилетий работы в ИТ это, ИМХО, одна из самых больших трагедий современной системы информационных и информационных технологий: очень немногие из них действительно изучают вычисления. Вы знаете ... "вычислительная" часть "компьютера"? Язык структурированных запросов (и все его надмножества) - это просто практическое приложение реляционной алгебры. Да, СУБД оптимизировала управление памятью и чтение / запись, но то же самое можно сказать и о процедурных языках. Я читал, что исходный вопрос касается не IDE, программного обеспечения, а, скорее, эффективности одного метода вычислений по сравнению с другим.

Проще говоря, в большинстве случаев быстрее / проще позволить базе данных сделать это за вас.

Цель базы данных в жизни - хранить / извлекать / обрабатывать данные в заданных форматах и ​​работать очень быстро. Ваш код VB.NET/ASP.NET, вероятно, далеко не так быстр, как выделенный движок базы данных. Это разумное использование ресурсов.