Как лучше всего обрабатывать большие объемы данных с помощью Hibernate/JPA?

Question

Как мне лучше подготовить большие объемы данных для переноса в нашу базу данных с помощью Hibernate? Производительность при работе с >25 000 записей, содержащих более 100 столбцов, не идеальна.

Позволь мне объяснить:

Фон

Я работаю в крупной компании, которая работает по всему миру. Мне было поручено возглавить команду (по крайней мере, для бэкэнда) для создания приложения с полным стеком, которое позволяет различным уровням управления выполнять свои задачи. Текущий стек технологий для серверной части — это Java, Spring Boot, Hibernate и PostgreSQL. Руководство хотело бы загрузить файлы Excel в наше приложение, чтобы наше приложение могло их проанализировать, чтобы мы могли обновить данные в нашей базе данных.

К сожалению, эти файлы имеют размер от 25 до 50 тысяч записей. Нам известно, что эти файлы Excel создаются с использованием SQL-запросов из Excel. Однако нам не разрешен доступ к базе данных с этими данными напрямую. Безопасность очень жесткая и не позволит нам получить доступ к каким-либо API, вызовам БД и т. д. для обхода Excel. Из-за ограничений памяти и проблем с масштабируемостью мы используем синтаксический анализ SAX, чтобы уменьшить занимаемую площадь. После анализа файлов Excel мы сопоставляем их с сущностью Hibernate, представляющей собой промежуточную таблицу. Затем мы переносим данные из него в другие наши таблицы.

В настоящее время для подготовки 25 000 записей и переноса всех данных в другие наши таблицы требуется 15 минут, что неприемлемо с точки зрения руководства. Тем более, что делать это нужно будет ежедневно.

Вещи, которые я пробовал

• Включение пакетной обработки в Hibernate, следуя ответу Влада здесь . Это сократило общее время постановки примерно на 20 секунд.
• Критерии перезаписи и другие запросы для выборки данных.
• Сокращение объема обрабатываемых данных (большинство полей являются обязательными, поэтому объем не может быть слишком сильно уменьшен).
• Индексирование важных столбцов как в промежуточной, так и в целевой таблицах. Я делаю индексацию как часть генерации схемы.
• Оптимизируйте части кода, очищающие проанализированные данные от несовершенств.

Я не могу отправить код из-за NDA

Сводка ограничений

• Это приложение нуждается в надежной поддержке для создания отчетов по связанным данным (одна из причин, по которой мы выбрали СУБД. Кроме того, данные хорошо вписываются в реляционную модель).
• Необходимо вести полную историю аудита всех записей (в настоящее время используется Hibernate Envers).
• Мы должны одобрить любую новую зависимость/библиотеку через команду кибербезопасности компании. Это может привести к потере производства в течение нескольких дней, пока мы ждем утверждения. Не идеально запрашивать новые зависимости для проекта.
• В настоящее время нет способов обойти файлы Excel. Вызов API или простой запрос к базе данных были бы хороши, но это не вариант для нас из соображений безопасности.
• Масштабируемость является растущей проблемой. Другая команда в рамках этого проекта должна проанализировать файл Excel из 50 000 строк из 100 строк. Все это только данные по США. Владелец проекта сказал, что компания в конечном итоге хочет расширить возможности управления этим приложением за рубежом.

Мои мысли

Что касается чисто постановочной проблемы, я думаю, что лучше всего избавиться от сущностей Hibernate, отвечающих за постановку. Я перепишу миграцию промежуточных данных в наши живые таблицы на SQL, используя хранимые процедуры. Несмотря на то, что это зависит от поставщика (во всяком случае, насколько мне известно), я буду использовать команду Postgres COPY для выполнения тяжелой работы с большим количеством строк. Я могу переписать синтаксический анализатор, чтобы вместо этого направлять данные в CSV или другой файл с разделителями. Единственная проблема, с которой я столкнулся, — как перенести данные в таблицы, использующие последовательности и генераторы Hibernate. Я не понял, как синхронизировать последовательности Hibernate после такого ручного обновления базы данных. Он любит выдавать ошибки о повторяющихся первичных ключах, пока не встретит идентификатор в последовательности, которая не используется. Но я чувствую, что это совсем другой вопрос.

Редактировать 1:

Я должен уточнить. 15 минут — это общее время всей постановки. Это включает в себя постановку и миграцию. Только постановка 25K записей занимает около 1:30, что тоже не идеально. Я несколько раз запускал метрики сеанса и обходил следующие числа для Spring Data, сохраняя записи 25K:

      2451000 nanoseconds spent acquiring 1 JDBC connection;
0 nanoseconds spent releasing 0 JDBC connections;
96970800 nanoseconds spent preparing 24851 JDBC statements;
9534006000 nanoseconds spent executing 24849 JDBC statements;
21666942900 nanoseconds spent executing 830 JDBC statements;
23513568700 nanoseconds spent executing 2 flushes (flushing a total of 49696 entities and 0 collections)
211588700 nanoseconds spent executing 1 partial-flushes (flushing a total of 24848 entities and 24848 collections)

Для этого конкретного случая я размещаю примерно 25 000 сущностей, а затем использую хранимую процедуру для перемещения только данных о сотрудниках из промежуточных таблиц в живые (небольшая часть того, что составляет 15 минут). Эта процедура, кажется, выполняется мгновенно. Но есть и другие данные, которые мы должны определить с помощью объединений, группировки по операторам и т. д., которые кажутся дорогостоящими. Я просто не уверен, почему Spring Data так долго сохраняет такое количество записей, когда для чистого SQL требуется значительно меньше.