Multirow inserts

Немного в сторону от основной темы блога, но, думаю, многим будет полезно.

Очень часто в моей работе приходится вставлять сравнительно большое число строк (несколько миллионов) в какую-нибудь таблицу. Причем делать это регулярно. Если делать отдельный 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.

Java IR Utils

Я создал на Google Code проект Java IR Utils, куда буду выкладывать всякие полезности вроде SparseArrayList.

Анонимный доступ:

svn checkout http://java-ir-utils.googlecode.com/svn/trunk/ java-ir-utils

Если есть желание участвовать в проекте, пишите.

SparseArrayList, новая версия

Выложил новую версию SparseArrayList. Добавил возможность итерации по паре индекс-значение в порядке возрастания индекса. Очень удобно, когда надо слить несколько списков в один.

Заодно провел несколько измерений расхода памяти и скорости доступа по сравнению с HashMap и TreeMap.

• SparseArrayList позволяет сэкономить около 5% памяти по сравнению с HashMap
• Скорость произвольного доступа к элементам выше у HashMap при маленьком числе элементов (100-200) и становится одинаковой при большом числе (>5000)
• Скорость последовательного (по возрастанию индекса) доступа у SparseArrayList выше чем у TreeMap более чем в 2 раза
  

SparseArrayList

Бывают такие ситуации, когда стандартный HashMap слишком медленный и затратный по памяти, а возможностей SparseVector из MTJ не хватает, поскольку надо хранить произвольные объекты. Для таких случаев я написал простой класс SparseArrayList, который практически идентичен SparseVector за вычетом, собственно, векторных операций. Плюс для хранения данных используется не массив, а ArrayList, и размер контейнера не ограничивается в конструкторе.

В архиве лежит еще и класс Arrays (дополнение к стандартному) из MTJ. В библиотеке он package-local, для реализации SparseArrayList пришлось сделать его public.