Pull to refresh

Сравнение скорости .NET провайдеров для работы с Oracle DB

Reading time 5 min
Views 8.9K
Некоторое время назад, у меня с коллегой случился небольшой теоретический спор о том, как быстрее выбирать и записывать данные: с помощью вызова DML-команд (select/insert) напрямую из кода внешнего приложения или всё таки лучше использовать хранимые процедуры БД? Спор перерос в практическую плоскость, когда мы присоединились к команде одного проекта, использующего Oracle DB, и я думал какого .NET провайдера лучше использовать для наших целей. Было решено выбрать не на авось, а ориентируясь на результаты тестов, в которых мы не только сравним провайдеров между собой, но и проверим различные подходы к работе с БД.

Первые две страницы гугла определили участников теста:

  1. Oracle ODP.NET
  2. Devart dotConnect for Oracle

Как видите, выбор не богатый, но чем меньше вариантов, тем проще выбирать.

Возможности провайдеров почти идентичны. Из плюсов решения от Devart можно назвать чуть более легкую работу с простыми транзакциями (методы встроены в класс соединения) и возможность работы без установленного клиента Oracle (т.н. Direct Mode). Также у в пользу DevArt говорит наличие тестов производительности, на которых dotConnect for Oracle (в девичестве OraDirect) кладёт конкурентов на лопатки (смотреть результаты).

Так как основой проекта должен был стать сервер с существенной (в перспективе) клиентской нагрузкой, то было интересно оценить накладные расходы, которые привносит с собой тот или иной провайдер.

Что касается спора, то мой коллега утверждал что нет особой разницы между выполнением анонимного скрипта с десятью insert'ами и вызовом хранимой процедуры, с передачей параметров для выполнения этих 10 insert'ов. Аналогично и с выборкой данных: нет разницы сделать select напрямую, или вызвать функцию, которая вернет, например, ref cursor. Я же выступал за однозначное превосходство хранимых процедур.

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

Все тесты разбили на две группы:

  1. Выборка из 4-х таблиц: одна master-таблица и три detail-таблицы (суммарно 22 строки на каждую итерацию). Данные сразу фетчатся из IDataReader и складываются в DataTable для дальнейшей работы.
    • Каждый select выполняется отдельной командой.
    • Вызывается пакетная процедура, принимающая primary key master-таблицы на входе и возвращающая 4 ref cursor (на каждую из таблиц) на выходе.

  2. Запись данных в две таблицы: 1 строка в master и 8 строк в detail с возвращением уникального идентификатора записи из master-таблицы, сгенерированного триггером.
    • Все 9 insert'ов выполняются последовательно.
    • Выполняется одна команда с заранее сгенерированным анонимным PL/SQL блоком, содержащим все выполняемые insert'ы.
    • Вызывается пакетная функция, в которую передаются параметры для master-таблицы как есть и параметры detail-таблицы в виде одномерных ассоциативных массивов.
    • Используется array binding для многократной вставки записей в detail-таблицу

Для dotConnect тестировался как вариант работы через клиента Oracle, так и прямой доступ. Статистика по всем таблицам была собрана. Перед каждым запуском таблицы, использующиеся для тестирования insert'ов, очищались с помощью скрипта:

    truncate table <detail> drop storage;
    alter table <detail> modify constraint foreignkey01 disable;
    truncate table <master> drop storage;
    alter table <detail> modify constraint foreignkey01 enable; 

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

Update

Первоначальные результаты и выводы оказались неверными из-за того, что ODP.NET было дано преимущество из-за досадной ошибки: Commit происходил не после каждой итерации, а после всего теста, в отличие от dotConnect, который трудился честно.
Ошибочные результаты сохраню для истории
Начнем с того, что dotConnect for Oracle проиграл все тесты без исключения. И если в случае с select он был медленнее от 2% до 11%, что можно списать на различные погрешности и общее несовершенство эксперимента, то в случае с insert результаты просто катастрофические: от 61% до 227% медленнее! Также отмечу, что что Direct Mode самого dotConnect оказался несколько медленнее OCI Mode, поэтому в сравнении с ODP.NET не участвовал.

Средние значения, полученные в результате тестов для 100 000 итераций приведены в таблице ниже. Время в миллисекундах.
Описание теста dotConnect OCI Mode dotConnect Direct Mode ODP.NET
мс % мс % мс %
Select: последовательное выполнение 167267 111% 194648 129% 150563 100%
Select: вызов пакетной процедуры 147084 102% 161508 112% 144499 100%
Insert: последовательное выполнение 217352 161% 207536 154% 134956 100%
Insert: вызов анонимного PL/SQL блока 154241 182% 152470 180% 84572 100%
Insert: вызов пакетной функции 98528 327% 105318 350% 30088 100%
Выводы просты:

  • Если особо не важна производительность, но нужна максимальная переносимость — покупайте dotConnect и используйте его в Direct Mode. Это действительно удобно для небольших проектов.
  • Для максимальной производительности используйте ODP.NET. Без вариантов.
  • Выборку данных, в принципе, можно делать как угодно. Но если на счету действительно каждая миллисекунда, то выгоднее вызывать хранимую процедуру, которая вернет несколько курсоров, уже подготовленных к фетчингу.
  • Что касается вставки данных, то видно, что хранимая процедура, в случае использования ODP.NET, даёт 3х-4х кратный выигрыш по сравнению с другими способами. Подход удобен тем, что весь код, включая объявления типов, сосредотачивается в одном PL/SQL пакете, что облегчает в будущем правку и управление версионностью.


Также в новые результаты включен тест вставки записей с помощью array-binding (спасибо VladVR за идею).
DotConnect в целом так остался медленнее ODP.NET: при выборке данных от 2% до 11%, при вставке с использованием хранимых процедур и array binding: от 3% до 19%. Но при этом оказался быстрее ODP.NET при вставке записей при последовательном вызове insert'ов и с помощью анонимного PL/SQL скрипта: от 8% до 14%.
Direct Mode dotConnect хорош только в одной дисциплине: последовательной вставке записей, опрередив остальных на 7-8%. Но так как он имеет ряд ограничений, то как реальный вариант выбора не рассматривался.

Средние значения, полученные в результате тестов для 100 000 итераций приведены в таблице ниже. Время в миллисекундах.
Описание теста dotConnect OCI Mode dotConnect Direct Mode ODP.NET
мс % мс % мс %
Select: последовательное выполнение 167267 111% 194648 129% 150563 100%
Select: вызов пакетной процедуры 147084 102% 161508 112% 144499 100%
Insert: последовательное выполнение 193374 107% 181218 100% 196228 108%
Insert: вызов анонимного PL/SQL блока 126916 100% 128962 102% 144762 114%
Insert: вызов пакетной функции 83692 119% 94004 133% 70580 100%
Insert: array binding 87258 103% 90308 107% 84406 100%

По итогам дискуссии с GlukKazan также были сделаны тесты без коммита после каждой транзакции. Общую картину они не поменяли — изменились только относительные проценты опережения. Полные результаты теста в Excel-файле на GitHub

Выводы почти не изменились:

  • Для максимальной производительности по прежнему следует использовать ODP.NET.
  • dotConnect используйте, когда возможностей ODP.NET уже не хватает, например вам очень нужно вызывать в запросе функцию, которая для каждой строки возвращает ref cursor.
  • Выборку данных, в принципе, можно делать как угодно. Но если на счету действительно каждая миллисекунда, то выгоднее вызывать хранимую процедуру, которая вернет несколько курсоров, уже подготовленных к фетчингу.
  • Что касается вставки данных, то лучшим вариантом при использовании обоих провайдеров будет вызов хранимой процедуры. DotConnect в этом случае работает на 19% медленнее ODP.NET, что и определило его судьбу. Данный подход также удобен тем, что весь код, включая объявления типов, сосредотачивается в одном PL/SQL пакете, что облегчает в будущем правку и управление версионностью.


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

Скрипт создания и заполнения объектов схемы, обновлённый код проекта (C#, VS.2013) и детальные результаты тестирования выложены на GitHub

PS: Версии Oracle 11.2.0.4, ODP.NET Managed 4.121.2.0, DevArt dotConnect for Oracle Trial 8.4.359.0
Tags:
Hubs:
+9
Comments 35
Comments Comments 35

Articles