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

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

Кроме того, у нас есть выбор: просто перенести большой набор данных обратно в наше приложение и затем обрабатывать их построчно, с помощью C #, Java, PHP или чего-либо еще.

Почему лучше использовать запросы на основе множеств? Какая теория стоит за этим выбором? Что является хорошим примером решения на основе курсора и его реляционного эквивалента?

23.08.2008 12:04:48
11 ОТВЕТОВ

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

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

18
23.08.2008 12:08:33

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

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

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

0
23.08.2008 12:16:59

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

0
23.08.2008 12:41:07

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

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

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

Простой курсор с процедурной логикой и примером на основе множества (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
15
23.08.2008 13:06:58
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',Can you explain this one ВЛЕВО (Customer.PhoneNumber, 3) `и его функциональность
Smart003 15.03.2016 10:36:49

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

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

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

3
1.05.2014 07:12:27

В дополнение к вышеприведенному «пусть СУБД выполняет свою работу» (что является отличным решением), есть еще несколько веских причин оставить запрос в СУБД:

  • Это (субъективно) легче читать. Рассматривая код позже, вы бы предпочли проанализировать сложную хранимую процедуру (или код на стороне клиента) с циклами и тому подобным, или вы бы предпочли краткую инструкцию SQL?
  • Это позволяет избежать сетевых поездок. Зачем отправлять все эти данные клиенту, а затем отправлять больше? Зачем перегружать сеть, если вам это не нужно?
  • Это расточительно. Ваша СУБД и сервер (ы) приложений должны будут буферизовать некоторые / все эти данные для работы на них. Если у вас нет бесконечной памяти, вы, вероятно, извлечете другие данные; зачем выбрасывать, возможно, важные вещи из памяти для буферизации набора результатов, который в основном бесполезен?
  • Почему бы тебе? Вы купили (или иным образом используете) высоконадежную, очень быструю СУБД. Почему бы тебе не использовать это?
16
23.08.2008 22:00:40
Я согласен с Мэттом. Чтение некоторых книг Джо Селко также помогает при принятии некоторых из этих решений.
Saif Khan 1.12.2008 18:35:32
Вы забыли упомянуть оптимизацию запросов и декларативную природу SQL; курсоры и другие подходы, основанные на строках, позволяют точно определить, как извлекать / обрабатывать данные, в то время как запросы SQL определяют только то, что нужно сделать, - тогда СУБД может предложить лучший план на основе статистики (например, в зависимости от индекса статистики поиск может быть хуже или лучше, чем поиск по индексу; СУБД может сделать различие; подходы на основе строк не могут ...)
Unreason 8.07.2010 15:38:38

Вы хотели несколько реальных примеров. У моей компании был курсор, который занимал более 40 минут для обработки 30 000 записей (и были времена, когда мне нужно было обновить более 200 000 записей). Потребовалось 45 секунд, чтобы выполнить ту же задачу без курсора. В другом случае я удалил курсор и отправил время обработки от более 24 часов до менее минуты. Один из них был вставкой с использованием предложения values ​​вместо select, а другой был обновлением, в котором вместо объединения использовались переменные. Хорошее практическое правило заключается в том, что если это вставка, обновление или удаление, вам следует искать основанный на множестве способ выполнения задачи.

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

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

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

8
1.12.2008 17:44:05
Хотел добавить эту ссылку: wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them
HLGEM 16.06.2010 18:20:09

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

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

1
1.12.2008 18:04:36

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

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

Просто посмотрите на пример создания таблицы поиска хешей. Для построения таблицы, вероятно, нужно выполнить 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 :)

1
1.12.2008 18:20:29

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

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

0
13.05.2009 20:05:57

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

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

0
13.07.2009 13:54:07