Самый быстрый способ делать ВСТАВКИ с помощью IBATIS
Мне нужно вставить 20 000 строк в одну таблицу (SQL Server 2005) с помощью iBatis. Как это сделать быстрее всего? Я уже использую пакетный режим, но это не сильно помогло:
try {
sqlMap.startTransaction();
sqlMap.startBatch();
// ... execute statements in between
sqlMap.commitTransaction();
} finally {
sqlMap.endTransaction();
}
Ответы 4
Массовые вставки лучше всего выполнять с помощью собственных средств массовой загрузки базы данных. Например, для Oracle это SQL * Loader. Часто они быстрее, чем все, что вы когда-либо могли бы написать.
Хотя это не относится к вашему серверу db, я ранее успешно записывал строки в локальный файл в формате csv, а затем имел базу данных, импортирующую файл. Это было значительно быстрее, чем операторы вставки или даже пакетная вставка.
В SQL Server наиболее быстрым способом вставки записей в пакетном режиме является использование ОБЪЕМНАЯ ВСТАВКА. Однако этот метод загружает записи из текстового файла, а не напрямую из вашего приложения.
Также не учитывается время, потраченное на создание файла. Возможно, вам придется взвесить, компенсирует ли это какой-либо прирост скорости по сравнению с фактической вставкой. Имейте в виду, что даже если в целом это будет немного медленнее, вы в конечном итоге ограничите свой сервер базы данных на меньшее время.
Единственное, что вы можете попробовать, это вставить (подготовить) пакет в совершенно другую таблицу (без индексов или чего-то еще). Затем переместите запись из промежуточной таблицы в целевую и отбросьте промежуточную таблицу. Это сначала переместит данные на сервер, чтобы последняя вставка могла произойти с самим сервером sql. Но опять же: это двухэтапный процесс, поэтому вам придется считать время для обоих шагов.
+1 Массовая загрузка из файла. Написание плоского файла обычно происходит очень быстро.
За исключением массовых загрузчиков, о которых говорят другие, давайте рассмотрим, как лучше всего сделать это с помощью SQL. (И массовые загрузчики не работают, если вы отправляете смешанные данные в разные таблицы.)
Во-первых, вам не следует использовать какой-либо уровень абстракции, который вы используете, в данном случае iBatis, поскольку он, по сути, будет предлагать вам небольшую ценность, но этот уровень абстракции будет иметь некоторую (не обязательно большую, но некоторую) стоимость ЦП. Вам действительно следует просто использовать необработанное соединение с базой данных.
Затем вы будете отправлять множество операторов INSERT. Вопрос в том, следует ли использовать простую строку для описания (например, INSERT INTO TABLE1 VALUES ('x', 'y', 12)) или подготовленного оператора (INSERT INTO TABLE1 VALUES (?,?,?)).
Это будет зависеть от вашей базы данных и драйверов БД.
Проблема с использованием простой строки в основном заключается в стоимости преобразования из внутреннего формата (при условии, что вы вставляете данные Java) в строку. Преобразование числа или даты в строку на самом деле является довольно дорогостоящей операцией ЦП. Некоторые базы данных и драйверы будут работать напрямую с двоичными данными, а не просто со строковыми данными. Таким образом, в этом случае PreparedStatement может дать некоторую экономию ЦП, поскольку потенциально не нужно преобразовывать данные.
Обратной стороной является то, что этот фактор будет зависеть от поставщика БД и, возможно, даже поставщика JDBC. Например, Postgres (я считаю) работает только со строками SQL, а не с двоичными, поэтому использование PreparedStatement является пустой тратой по сравнению с простым построением строки самостоятельно.
Затем, когда у вас есть тип оператора, вы хотите использовать метод addBatch () класса операторов JDBC. Что делает addBatch, так это группирует операторы SQL в пакет. Преимущество заключается в том, что вместо отправки нескольких запросов в БД вы отправляете один БОЛЬШОЙ запрос. Это сокращает сетевой трафик и дает заметный прирост пропускной способности.
Суть в том, что не все драйверы / базы данных поддерживают addBatch (по крайней мере, не очень хорошо), но и размер вашего пакета ограничен. Скорее всего, вы не можете добавитьBatch для всех 20 000 строк и ожидать, что он сработает, хотя это было бы наилучшим вариантом. Этот предел также может варьироваться в зависимости от базы данных.
Раньше для Oracle я использовал буфер размером 64 КБ. По сути, я написал функцию-оболочку, которая будет принимать буквальный оператор INSERT и накапливать их в пакетах по 64 КБ.
Итак, если вы хотите выполнить массовую вставку данных через SQL через JDBC, это способы сделать это. Большим улучшением является пакетный режим, оператор Statement vs PreparedStatement больше предназначен для потенциальной экономии ресурсов процессора и, возможно, сетевого трафика, если ваш драйвер поддерживает двоичный протокол.
Протестируйте, промойте и повторяйте, пока не будете достаточно довольны.
недавно сделал такой же опыт с MySQL: вставка данных с использованием простых операторов INSERT INTO, загруженных из файла, была значительно быстрее, чем выполнение пакетной вставки, как описано выше.