30.06.2015 08:48
GreenPavel 35 5788 Источник
Некоторое время назад, у меня с коллегой случился небольшой теоретический спор о том, как быстрее выбирать и записывать данные: с помощью вызова 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

Комментарии (35)


  1. CDuke
    30.06.2015 14:28
    #8482665

    А проводили сравнение с Managed ODP?


    1. GreenPavel Автор
      01.07.2015 04:58
      #8483597

      Как раз Managed.ODP и использовался…


  1. Rupper
    30.06.2015 15:48
    #8482809

    Managed ODP.NET пока не поддерживается на Mono. По скорости тесты не гоняли. У DevArt есть проблемы совместимости (уже плохо помню, но кажется с блобами какие-то косяки были) и распределенными транзакциями.
    Мы думали использовать его, по полезли баги и отказались. В случае с ораклом производительность провайдера скорее последнее что будет тормозить :)


    1. CDuke
      30.06.2015 16:10
      #8482861

      Что то в статье не увидел где указано, что речь идет о Mono. Кстати, если речь идет о Mono, то под какой операционной системой проводилось тестирование?


      1. Rupper
        30.06.2015 16:22
        #8482887

        Нигде, в статье указано, что если требуется переносимость. Managed ODP не совсем такой ибо нет Mono.


        1. GreenPavel Автор
          01.07.2015 04:59
          #8483599

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


          1. Rupper
            01.07.2015 10:35
            #8483831

            А в чем преимущество в переносимости тогда перед DevArt? Для Managed ODP надо таскать одну библиотеку, котороую просто можно включить в дистрибутив (в некоторых случаях — две или три если с разной архитектурой:
            Oracle.DataAccess.dll
            Oracle.ManagedDataAccess.dll
            Oracle.ManagedDataAccessDTC.dll


            1. GreenPavel Автор
              01.07.2015 13:57
              #8484173

              Для ODP ещё нужен установленный клиент Oracle как минимум. В случае dotConnect Direct Mode нужна пара его библиотек и oci8.dll


              1. Rupper
                01.07.2015 14:09
                #8484187

                Для Managed не нужен.


                1. GreenPavel Автор
                  01.07.2015 14:15
                  #8484201

                  Спасибо, не знал.


  1. mik
    30.06.2015 18:09
    #8483013

    А при пакетной вставке использовался один раз подготовленный стейтмент, и много раз привязанные переменные?


    1. GlukKazan
      30.06.2015 19:53
      #8483125

      Нет. Парсится в каждой итерации цикла и хуже того, в каждой итерации комитится.
      Посмотрите на GitHub. В общем, странное тестирование.


      1. GreenPavel Автор
        01.07.2015 05:20
        #8483605

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


        1. GlukKazan
          01.07.2015 09:24
          #8483719

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


          1. GreenPavel Автор
            01.07.2015 10:12
            #8483777

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


            1. GlukKazan
              01.07.2015 10:25
              #8483807

              Так то оно так, но вы меряете накладные расходы самого Oracle.
              Накладные расходы любых провайдеров (честно выполняющих синхронный commit) просто меркнут на их фоне.


              1. GreenPavel Автор
                01.07.2015 11:31
                #8483959

                А без коммита мы будем тестировать хватает ли undo. В любом случае мне нужно было оценить свою ситуацию, а не некую абстракцию.


                1. GlukKazan
                  01.07.2015 11:58
                  #8484009

                  Ну, в Oracle теперь есть асинхронный commit. О его достоинствах можно спорить, но, как минимум, вы не будете его мерять в тесте. Если же по старинке, то можно коммитить, по несколько записей (100-1000) и вынести commit за пределы замера времени.


                  1. GreenPavel Автор
                    01.07.2015 13:02
                    #8484103

                    Я понял вашу мысль, но тогда получится тест ради теста. Моё же тестирование чисто практическое для конкретной задачи.
                    Вообще в реальной системе на каждой итерации происходит следующее: открывается новое соединение, выполняется кучка select/insert, commit/rollback по ситуации (синхронный, естественно), после чего соединение закрывается.

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

                    PS: Если честно, то я довольно давно использую OraDirect/dotConnect и был в нём всегда уверен. Поэтому сам несколько обескуражен результатами.


                    1. GlukKazan
                      01.07.2015 13:19
                      #8484129

                      Поверьте мне, построчный commit изменяет результат так, что все прочие биндинги и префетчи плачут кровавыми слезами. Для производительности самого приложения это тоже может быть очень актуально. РСУБД хорошо работают с массовыми операциями, построчная обработка — это не про них.


                      1. GreenPavel Автор
                        01.07.2015 14:25
                        #8484209
                        +1

                        Про построчные коммиты очень хорошо знаю. В тесте ведь нет речи про commit после каждой записи, транзакция завершается после выполнения последовательность DML-команд (итерации).

                        PS: Но спасибо вам за эту цепочку, потому что выключая commit'ы и увидел непростительную ошибку копипасты — при тестировании ODP.NET всё идёт в рамках одной транзакции, в отличие от dotConnect.
                        После повторного тестирования возможно всё преимущество ODP.NET испарится… Обновлю публикацию в ближайшее время (и, скорее всего, придется посыпать голову пеплом).


    1. GreenPavel Автор
      01.07.2015 05:12
      #8483601

      Стeйтмент готовился на каждой итерации (потому что тестирование приближали к тому, что потребуется нам), но на выходе был один и тот же PL/SQL блок вида:

      begin
  insert into t1 (c1, c2, ...) values (:v1, :v2, ...);
  insert into t2 (c1, c2, ...) values (:v1, :v2, ...);
  ...
end;

      А использование bind-переменных в OLTP — это закон.


      1. GlukKazan
        01.07.2015 09:26
        #8483725

        Биндинг переменных — это хорошо (в большинстве случаев). Спасает от жёстких разборов.
        А как быть с мягкими?


        1. GreenPavel Автор
          01.07.2015 13:09
          #8484111

          Как минимум использовать stored proc… :)

          Если же вы намекаете на повторное использование заранее приготовленного OracleCommand, то, да — можно убрать soft parse в самом первом тесте со многими insert. В остальных случаях не получится, т.к. в моей реальности я имею дело с новым соединением для каждой транзакции.


          1. GlukKazan
            01.07.2015 13:21
            #8484133

            Это аргумент. Но мы у себя с ним справились.


            1. GreenPavel Автор
              01.07.2015 16:56
              #8484431

              Интересно узнать как


              1. GlukKazan
                01.07.2015 18:05
                #8484531

                Элементарно. Сохраняем разобранный запрос в каждой сессии где он засветился.
                Пул сессий наш. Всё на Java.


                1. GreenPavel Автор
                  01.07.2015 18:34
                  #8484561

                  Во, «свой connection pool» — это ключевое. Я думал, что есть некие стандартные средства…


                  1. GlukKazan
                    01.07.2015 18:41
                    #8484569

                    Нет конечно стандартных. Есть query result cache, но это немного другое.


                    1. GreenPavel Автор
                      01.07.2015 19:03
                      #8484597

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


  1. VladVR
    30.06.2015 21:23
    #8483239

    Включите пожалуйста в сравнение insert с помощью array-binding.


    1. GreenPavel Автор
      01.07.2015 09:42
      #8483743

      Спасибо за идею!
      В array-binding dotConnect и ODP.NET выступили практически на равных (dotConnect на на незначительные 4% медленнее).

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


      1. Rupper
        01.07.2015 10:37
        #8483841

        Оригинально. У нас array binding всегда побеждает. С большим отрывом.
        Oracle 12c


        1. GreenPavel Автор
          01.07.2015 13:16
          #8484123

          У всех разные условия. Возможно, у вас немного смещены акценты на то, что нужно именно вам.

          В любом случае, что array binding, что stored proc показали себя хорошо. Первый. конечно, поддерживать гораздо проще…


        1. VladVR
          04.07.2015 01:48
          #8486615

          Секрет, я полагаю, вот в этом — Parameter.Size = 8;
          Мы, к примеру сотнями и тысячами вставляли за раз.