Немного в сторону от основной темы блога, но, думаю, многим будет полезно.
Очень часто в моей работе приходится вставлять сравнительно большое число строк (несколько миллионов) в какую-нибудь таблицу. Причем делать это регулярно. Если делать отдельный insert на каждую строчку, миллион будет вставляться очень и очень долго. В MySQL поддерживается специальный синтаксис для multirow inserts: INSERT INTO my_table (col1, col2) values (1, 2), (3, 4), ... (99, 100)
. Такой запрос вставит сразу 50 строчек. Причем очень быстро. Число строчек, которые можно вставить за один запрос очень велико и ограничено сверху, если я не ошибаюсь, максимальным размером пакета, который может принять MySQL. На практике, я обычно вставляю по несколько десятков тысяч строк одним запросом.
К сожалению, ни Oracle, ни MS SQL не поддерживают этот замечательный синтаксис. Долгий поиск дал всего две рекомендации: использовать sqlldr, LOAD DATA
или INSERT INTO <table name> SELECT FROM <table name>
. Практически отчаявшись, я заглянул в Википедию и нашел там спасение.
Во-первых, оказалось, что чудесный синтаксис из MySQL это часть стандарта SQL 92 (это, действительно, так — ключевые слова "query expression", "insert statement", "table value constructor") и он поддерживается также в DB2 и PostgreSQL.
Во-вторых, INSERT INTO <table name> SELECT FROM <table name>
может выглядеть так (пример для MS SQL):
INSERT INTO my_table (col1, col2)
SELECT 1, 2
UNION ALL
SELECT 3, 4
Такой запрос вставит в таблицу сразу две строчки. С воодушевлением я принялся за тестирование. Сначала попробовал вставлять 50 строчек за раз. Скорость возрасла, но не сильно. Потом попробовал 200 — стало медленнее! Попробовал 1000 и получил ошибку от MS SQL! Дальнейшие эксперименты позволили сделать следующие выводы:
- Оптимальное количество строчек в одном запросе — 5-10. Так иногда удается достичь, практически, линейного роста скорости вставки.
- Скорость вставки очень сильно зависит от размера строчки. В одном из экспериментов я убрал из запроса одну колонку типа bit и скорость возросла на 30%!
- Если какую-то колонку можно вставить небольшим количеством update'ов, возможно, так будет заметно быстрее.
Для удобной работы с multirow inserts я сделал простой интерфейс
BulkInserter
с реализацией пока только для MS SQL. В ближайшее время сделаю реализацию для MySQL, допишу документацию и выложу в
Java IR Utils.