Освоение BulkInsert

Хочу поделиться своими впечатлениями о технологии BulkInsert для FireBird.

Возникла задача еженедельного обновления справочника в БД из внешнего присылаемого файла. Справочник оказался не маленьким, примерно 1150000 записей, размер csv файла составляет 26МБ.

На первых шагах, для исполнения задания, было принято самое простое решение, а именно, чтение строки из файла, её преобразование и загрузка в БД.

Проблема возникла в том, что после первой загрузки, удалять данные нельзя поскольку на них могут ссылаться из других таблиц, а значит, что при повторной загрузке, старые данные должны быть обновлены, а новые добавлены.
Для решения данной проблемы была разработана логика по определению того, что необходимо сделать, обновить запись или вставить новую. Как результат всех стараний время работы процедуры составило около 6 часов работы, это примерно по 3500 записей в минуту. Данный результат абсолютно не устраивал и пришлось искать решение.

Пшеничный Николай Юрьевич посоветовал использовать BulkInsert, было обещано многократное ускорение.

Опишу свой опыт тестирования технологии.
Поскольку ранее я ни разу не использовал BulkInsert, было принято решение попробовать всё на локально поднятой тестовой базе.

1. Развёртывание тестовой БД

Прежде всего стоит сказать, что я всё выполнял под windowsXP и у меня уже были установлены FireBird 1,5 и IBExpert.
В каталоге с установленным сервером БД необходимо запустить утилиту isql.exe
..\firebird_1_5\bin\isql.exe

Появиться окно с приглашением к действию.

SQL>

Для создания новой базы данных необходимо выполнить следующую команду:

SQL> CREATE DATABASE 'C:\testDB\TestDB.fdb' user 'sysdba' password 'masterkey';

Данная команда создаст файл базы данных TestDB.fdb в каталоге C:\testDB\.

Теперь в БД необходимо создать таблицу где будет храниться новый справочник. Первым делом необходимо подключиться к новой БД, выполнив команду.

SQL> CONNECT 'C:\testDB\TestDB.fdb' user 'sysdba' password 'masterkey';

После подключения к БД нужно выполнить команду:

SQL> CREATE TABLE testTable (TestTable_ID BigInt, ordinal varchar(40), opendate date, closedate date);

Теперь в базе TestDB существует таблица testTable на которой будут происходить все эксперименты.

Все остальные настройки я проводил через IBExpert.
Необходимо зарегистрировать БД в IBExpert. Вот с такими настройками:

Для создания ключей на поле TestTable_ID в таблице testTable необходимо:

1. открыть таблицу;
2. выбрать закладку "ограничения";
3. выбрать вкладку "первичный ключ";
4. нажать правую кнопку мыши и выбрать пункт меню "Новый первичный ключ" кнопка "insert".

В появившейся строке в графе Поле нужно выбрать поле по которому будет создан ключ.
Таблица готова к началу тестов.

2. Работа с внешними таблицами

Для того чтобы включить возможность подключения внешних таблиц нужно изменить файл настроек:
..\firebird\firebird.conf
В файле необходимо найти строку с текстом ExternalFileAccess убрать символ комментария и присвоить значение ExternalFileAccess = Full.

Далее для создание внешней таблицы необходимо выполнить следующую команду:

create table ext1 external 'C:\testDB\test_right.csv' -- C:\testDB\test_right.csv - это путь до файла внешней таблицы. Если его нет система создаст файл, если есть попытается загрузить из него данные
(
ord varchar(11), -- 1ое поле внешней таблицы, 11 символов текст
ope varchar(10), -- дата открытия записи, поскольку FireBird хранит дату с 01.01.0001  то во временную таблицу проще записать дату в текст. 
clo varchar(10) -- 3е поле дата закрытия записи
id bigint -- id записи
);

Созданную внешнюю таблицу можно просматривать через IBExpert, как обычную таблицу.
Для того чтобы вставить данные в таблицу нужно использовать следующую команду:

insert into ext1 (id, ord, ope, clo) values (1234567890, '01234567891', '01.01.1900', '01.01.2100')

Теперь в файле C:\testDB\test_right.csv будет записана 1 запись.
Чтобы освободить файл необходимо удалить внешнюю таблицу:

drop table ext1;

Содержимое файл будет выглядить примерно вот так:

Что будет означать:
2 байта на длину, 11 байт текста, 1 байт дополнения, 2 байта на длину, 10 байт текста, 2 байта на длину, 10 байт текста, 8 байт на длинное число.

Для текстовых полей сначала идёт длина строки в 2 байта, затем текст, длина текста будет дополнена до чётного числа байт.

3. Формирование файла внешней таблицы

Добрые люди уже позаботились о создании класса, который позволит записать простые данные в файл в требуемый формат. Посмотреть можно здесь.

Вставка в БД выполняется по следующему алгоритму.

1. Удаляем все ключи;
2. Удаляем все записи из Таблицы;
3. Подключаем внешнюю Таблицу;
4. Выполняем перекачку данных из внешней Таблицы в обычную;
5. Восстанавливаем ключи;
6. Удаляем внешнюю таблицу.

Далее приведу пример скрипта который использовался для тестирования.

SET NAMES WIN1251;

CONNECT 'C:\testDB\TestDB.fdb' USER user 'sysdba' PASSWORD 'masterkey'; -- выполняется подключение к БД
alter table payrequest drop constraint FK_PRE_LGA; -- сбрасываются ключи на таблицу (так будет на основной бд)
delete from testTable where 1=1; -- удаление из таблицы всех старых данных 

create table ext1 -- подключение внешней таблицы
external '/d/varware/1-23.txt'
(
id bigint
,ord varchar(11)
,ope varchar(10)
,clo varchar(10)
);
-- перенос данных в основную таблицу
insert into lukoilgarantagreement (lukoilgarantagreement_id, ordinal, opendate, closedate)
select
 case id
   when id = 0 then (select * from GeneratorID)
   else id 
 end
 ord, ope, clo
from ext1;
-- восстановление ключей
ALTER TABLE table2 ADD CONSTRAINT FK_PRE_LGA FOREIGN KEY (testTable) REFERENCES TestTable_ID;
-- удаление внешней таблицы
drop table ext1;

Вот собственно простой способ использования массового добавления записей в БД, за короткий промежуток времени.

4. Итого

Время выполнения всего переноса данных составило около 9 минут. (дольше всего происходит удаление данных из таблицы)
Время формирования файла для внешней таблицы составляет примерно 5 минут.
копирование файла на сервер примерно 1 минута.
Вставка данных в БД из внешней таблицы выполняется за 1 минуту 30 секунд.
В среднем процедура вствки данных ускорилась в 45 раз.

Я думаю, что это не плохой результат. И судя по всему это не предел.