Некоторое время назад, у меня с коллегой случился небольшой теоретический спор о том, как быстрее выбирать и записывать данные: с помощью вызова DML-команд (select/insert) напрямую из кода внешнего приложения или всё таки лучше использовать хранимые процедуры БД? Спор перерос в практическую плоскость, когда мы присоединились к команде одного проекта, использующего Oracle DB, и я думал какого .NET провайдера лучше использовать для наших целей. Было решено выбрать не на авось, а ориентируясь на результаты тестов, в которых мы не только сравним провайдеров между собой, но и проверим различные подходы к работе с БД.
Первые две страницы гугла определили участников теста:
- Oracle ODP.NET
- Devart dotConnect for Oracle
Как видите, выбор не богатый, но чем меньше вариантов, тем проще выбирать.
Возможности провайдеров почти идентичны. Из плюсов решения от Devart можно назвать чуть более легкую работу с простыми транзакциями (методы встроены в класс соединения) и возможность работы без установленного клиента Oracle (т.н. Direct Mode). Также у в пользу DevArt говорит наличие тестов производительности, на которых dotConnect for Oracle (в девичестве OraDirect) кладёт конкурентов на лопатки (смотреть результаты).
Так как основой проекта должен был стать сервер с существенной (в перспективе) клиентской нагрузкой, то было интересно оценить накладные расходы, которые привносит с собой тот или иной провайдер.
Что касается спора, то мой коллега утверждал что нет особой разницы между выполнением анонимного скрипта с десятью insert’ами и вызовом хранимой процедуры, с передачей параметров для выполнения этих 10 insert’ов. Аналогично и с выборкой данных: нет разницы сделать select напрямую, или вызвать функцию, которая вернет, например, ref cursor. Я же выступал за однозначное превосходство хранимых процедур.
Что ж, мы за научный подход! Поэтому создаём структуру таблиц для тестирования select-ов и insert-ов, максимально приближенную к нашим реалиям, пишем немного кода для тестирования и начинаем…
Все тесты разбили на две группы:
- Выборка из 4-х таблиц: одна master-таблица и три detail-таблицы (суммарно 22 строки на каждую итерацию). Данные сразу фетчатся из IDataReader и складываются в DataTable для дальнейшей работы.
- Каждый select выполняется отдельной командой.
- Вызывается пакетная процедура, принимающая primary key master-таблицы на входе и возвращающая 4 ref cursor (на каждую из таблиц) на выходе.
- Запись данных в две таблицы: 1 строка в master и 8 строк в detail с возвращением уникального идентификатора записи из master-таблицы, сгенерированного триггером.
- Все 9 insert’ов выполняются последовательно.
- Выполняется одна команда с заранее сгенерированным анонимным PL/SQL блоком, содержащим все выполняемые insert’ы.
- Вызывается пакетная функция, в которую передаются параметры для master-таблицы как есть и параметры 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 результаты становились очень похожими… И они немного удивили.
Начнем с того, что 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 пакете, что облегчает в будущем правку и управление версионностью.
Думаю нет смысла говорить, что для своего проекта мы выбрали ODP.NET, ну а коллеге пришлось бежать за соком, который мы потом вместе и выпили.
Скрипт создания схемы, код проекта (C#, VS.2013) и детальные результаты тестирования выложены на GitHub
PS: Версии Oracle 11.2.0.4, ODP.NET 4.121.2.0, DevArt dotConnect for Oracle Trial 8.4.359.0
ссылка на оригинал статьи http://habrahabr.ru/post/261463/
Добавить комментарий