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

Некоторое время назад, у меня с коллегой случился небольшой теоретический спор о том, как быстрее выбирать и записывать данные: с помощью вызова 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
Share post
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 35

    0
    А проводили сравнение с Managed ODP?
      0
      Как раз Managed.ODP и использовался…
      0
      Managed ODP.NET пока не поддерживается на Mono. По скорости тесты не гоняли. У DevArt есть проблемы совместимости (уже плохо помню, но кажется с блобами какие-то косяки были) и распределенными транзакциями.
      Мы думали использовать его, по полезли баги и отказались. В случае с ораклом производительность провайдера скорее последнее что будет тормозить :)
        0
        Что то в статье не увидел где указано, что речь идет о Mono. Кстати, если речь идет о Mono, то под какой операционной системой проводилось тестирование?
          0
          Нигде, в статье указано, что если требуется переносимость. Managed ODP не совсем такой ибо нет Mono.
            0
            Наверное я не совсем корректно выразился, т.к. под переносимостью имел ввиду легкое разворачивание софта на клиентской машине.
              0
              А в чем преимущество в переносимости тогда перед DevArt? Для Managed ODP надо таскать одну библиотеку, котороую просто можно включить в дистрибутив (в некоторых случаях — две или три если с разной архитектурой:
              Oracle.DataAccess.dll
              Oracle.ManagedDataAccess.dll
              Oracle.ManagedDataAccessDTC.dll
                0
                Для ODP ещё нужен установленный клиент Oracle как минимум. В случае dotConnect Direct Mode нужна пара его библиотек и oci8.dll
                  0
                  Для Managed не нужен.
                    0
                    Спасибо, не знал.
        0
        А при пакетной вставке использовался один раз подготовленный стейтмент, и много раз привязанные переменные?
          0
          Нет. Парсится в каждой итерации цикла и хуже того, в каждой итерации комитится.
          Посмотрите на GitHub. В общем, странное тестирование.
            0
            Потому что тестировался процесс обработки некой транзакции, которую нужно атомарно сохранить в БД, закоммитить, забыть и заниматься следующей.
              0
              Только вот получилось, что меряли вы не скорость вставки, а сплошные накладные расходы.
                0
                Так в этом и был основной замысел: сравнить накладные расходы двух провайдеров при выполнении аналогичных операций с БД.
                Если мерять скорость вставки, то проще уж запустить скрипт в sqlplus…
                  0
                  Так то оно так, но вы меряете накладные расходы самого Oracle.
                  Накладные расходы любых провайдеров (честно выполняющих синхронный commit) просто меркнут на их фоне.
                    0
                    А без коммита мы будем тестировать хватает ли undo. В любом случае мне нужно было оценить свою ситуацию, а не некую абстракцию.
                      0
                      Ну, в Oracle теперь есть асинхронный commit. О его достоинствах можно спорить, но, как минимум, вы не будете его мерять в тесте. Если же по старинке, то можно коммитить, по несколько записей (100-1000) и вынести commit за пределы замера времени.
                        0
                        Я понял вашу мысль, но тогда получится тест ради теста. Моё же тестирование чисто практическое для конкретной задачи.
                        Вообще в реальной системе на каждой итерации происходит следующее: открывается новое соединение, выполняется кучка select/insert, commit/rollback по ситуации (синхронный, естественно), после чего соединение закрывается.

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

                        PS: Если честно, то я довольно давно использую OraDirect/dotConnect и был в нём всегда уверен. Поэтому сам несколько обескуражен результатами.
                          0
                          Поверьте мне, построчный commit изменяет результат так, что все прочие биндинги и префетчи плачут кровавыми слезами. Для производительности самого приложения это тоже может быть очень актуально. РСУБД хорошо работают с массовыми операциями, построчная обработка — это не про них.
                            +1
                            Про построчные коммиты очень хорошо знаю. В тесте ведь нет речи про commit после каждой записи, транзакция завершается после выполнения последовательность DML-команд (итерации).

                            PS: Но спасибо вам за эту цепочку, потому что выключая commit'ы и увидел непростительную ошибку копипасты — при тестировании ODP.NET всё идёт в рамках одной транзакции, в отличие от dotConnect.
                            После повторного тестирования возможно всё преимущество ODP.NET испарится… Обновлю публикацию в ближайшее время (и, скорее всего, придется посыпать голову пеплом).
            0
            Стeйтмент готовился на каждой итерации (потому что тестирование приближали к тому, что потребуется нам), но на выходе был один и тот же PL/SQL блок вида:
            begin
              insert into t1 (c1, c2, ...) values (:v1, :v2, ...);
              insert into t2 (c1, c2, ...) values (:v1, :v2, ...);
              ...
            end;
            

            А использование bind-переменных в OLTP — это закон.
              0
              Биндинг переменных — это хорошо (в большинстве случаев). Спасает от жёстких разборов.
              А как быть с мягкими?
                0
                Как минимум использовать stored proc… :)

                Если же вы намекаете на повторное использование заранее приготовленного OracleCommand, то, да — можно убрать soft parse в самом первом тесте со многими insert. В остальных случаях не получится, т.к. в моей реальности я имею дело с новым соединением для каждой транзакции.
                  0
                  Это аргумент. Но мы у себя с ним справились.
                    0
                    Интересно узнать как
                      0
                      Элементарно. Сохраняем разобранный запрос в каждой сессии где он засветился.
                      Пул сессий наш. Всё на Java.
                        0
                        Во, «свой connection pool» — это ключевое. Я думал, что есть некие стандартные средства…
                          0
                          Нет конечно стандартных. Есть query result cache, но это немного другое.
                            0
                            Да, пользовались им для кэширования тяжелых функций, но это вещь в себе — неявный сброс кеша и latch free events возникают в самый неподходящий момент. Поэтому, кстати, было довольно трудно оценить реальный прирост производительности при его использовании.
            0
            Включите пожалуйста в сравнение insert с помощью array-binding.
              0
              Спасибо за идею!
              В array-binding dotConnect и ODP.NET выступили практически на равных (dotConnect на на незначительные 4% медленнее).

              Получается что если используется решение от DevArt, то array-binding в два раза производительнее хранимых процедур. В случае ODP.NET — на 36% медленнее. Итоговый вывод о том, что ODP.NET + stored proc = высокая производительность остаётся в силе.
                0
                Оригинально. У нас array binding всегда побеждает. С большим отрывом.
                Oracle 12c
                  0
                  У всех разные условия. Возможно, у вас немного смещены акценты на то, что нужно именно вам.

                  В любом случае, что array binding, что stored proc показали себя хорошо. Первый. конечно, поддерживать гораздо проще…
                    0
                    Секрет, я полагаю, вот в этом — Parameter.Size = 8;
                    Мы, к примеру сотнями и тысячами вставляли за раз.

              Only users with full accounts can post comments. Log in, please.