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

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

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

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

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

Запросы на основе наборов обычно выполняются быстрее, потому что:

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

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

Простой курсор с процедурной логикой против примера на основе набора (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 часов до менее чем минуты. Одно было вставить с помощью предложения values вместо того, чтобы выбрать и другие обновления, которые используются переменные вместо соединения. Хорошее эмпирическое правило состоит в том, что если это вставка, обновление или удаление, вы должны искать способ выполнения задачи на основе набора.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Цель базы данных в жизни состоит в том, чтобы store/retrieve/manipulate данные в заданных форматах и быть действительно быстрым. Ваш код VB.NET/ASP.NET, скорее всего, далеко не так быстр, как выделенный компонент database engine. Используя это, можно разумно использовать ресурсы.

Идея предпочтения выполнять работу в запросах заключается в том, что компонент database engine может оптимизировать ее, переформулировав. Вот почему вы также хотите запустить EXPLAIN в своем запросе, чтобы увидеть, что на самом деле делает БД. (например, использование преимуществ индексов, размеров таблиц и иногда даже знаний о распределении значений в Столбцах.)

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

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

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

REAL ответ-Иди и получи один из E.F. Книги Кодда и освежить в памяти реляционную алгебру . Тогда возьмите хорошую книгу по большой O нотации . После почти двух десятилетий в 32-м году это одна из самых больших трагедий современной степени 34 или 35: очень немногие действительно изучают вычисления. Вы know...the "compute" часть "computer"? Структурированный язык запросов (и все его суперсети) - это всего лишь практическое применение реляционной алгебры. Да, RDBMS оптимизировали управление памятью и чтение / запись, но то же самое можно сказать и о процедурных языках. Когда я читаю его, первоначальный вопрос заключается не в IDE, программном обеспечении,а скорее в эффективности одного метода вычислений по сравнению с другим.

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