Как заполнить базу данных MS SQL разнородными случайными данными или 17 часов ожидания

Источник: habrahabr

IgorSkir

Перед разработчиком часто возникает задача провести тест базы данных на больших объемах данных, но откуда взять эти самые данные? Ведь всем известно, что структура базы может достигать over 50 таблиц, которые не очень хочется заполнять руками. А если подумать о внешних ключах и составных первичных ключах значения которых связаны с другими таблицами, то голова начинает нагреваться пропорционально старому AMD с отключенным охлаждением.
В интернете существует много решений заполнения базы данный случайными значениями с использованием средств .NET, C++, Java и.д. В данной статье будет освещена тема заполнения базы данных случайными значениями средствами T-SQL под управлением MS SQL Server.

Введение

Несколько дней назад я получил задачу по заполнению базы данных под управлением MS SQL Server случайными данными. Причем, вся реализация должна быть выполнена только средствами T-SQL. После долго поиска подобных решений на ресурсах пришел к выводу - придется делать самому и принялся за дело. Не являясь ~~(до последнего времени)~~ знатоком T-SQL, а имея лишь набор знаний из курса "Базы данных" университета, реализация получилась очень "костылявая" и медленная(основная проблема), но работающая.

Основная цель данной статьи обсудить возможность оптимизации решения, либо его Ctrl+A и Shift + Del с ссылкой на уже готовую реализацию.

И так, что было на входе:

База данных с некоторым числом связанных таблиц;
Все первичные ключи (дальше PK) - автоинкременты;
Существуют таблицы содержащие в себе составной PK состоящих из внешних ключей (дальше FK).

Что со всем этим нужно было сделать:

Генерировать случайные данные в зависимости от типа атрибута (столбца);
Пропускать заполнение автоинкрементов.
Заполнять FK дочерних таблиц случайными PK родительских таблиц.

Реализация

Вся реализация получила вид вызывающих друг-друга процедур:

randomString - генерация случайной строки символов заданной длины.;
randomInt - генерация случайного числа из заданного диапазона;
generateDataByType - получает тип атрибута(столбца) таблицы и вызывает нужную процедуру генерации случайных значений.
insertRandomData - основная процедура, просит на вход только имя таблицы и количество записей, которое вы хотите добавить

Предлагаю остановиться на каждой процедуре подробно. (предполагается, что читатель, ~~в отличии от автора~~, знаком с основами SQL)

randomString

Я использовал один из первых попавшихся мне вариантов реализации с форума MS SQL. Процедура получает на вход длину строки, а на выходе выдает строку случайных символов типа NVARCHAR(MAX) нужного размера. В данном случая реализация не является критичной, так как не имеет серьезных временных затрат при больших объемах данных. Едем дальше.

randomInt

Функция небольшая и не очень красивая (особенно место с SUBSTRING), но меня она вполне устроила своим быстродействием, так что пока оставляем ее и идем дальше.

generateDataByType

И вот, не дойдя до "главной" процедуры мы получаем огромные временные затраты при заполнении внешнего ключа таблицы данными из найденной родительской таблицы. Если данный поиск заметь подстановкой случайных чисел в заданном диапозоне производительность резко возрастает. Возможно дело в SELECT'e из системной таблице и случайной сортировки. Для сравнения: запись 1 млн. строк в таблицу без FK занимает около 20 мин, запись 1 млн. строк в таблицу с FK занимает больше 17 часов. Для справки, запись одного миллиона строк чистым INSERT'ом в одно поле занимаем 6-10 сек.
На текущий момент я не смог придумать ничего более оптимального, что и послужило толчком к написанию этой статьи, но об этом в заключении.

insertRandomData

Данная процедура является "относительно" не затратной по времени хотя и лезет в системные таблицы чтобы получить структуру пришедшей на вход таблицы, но содержит в себе несколько явных слабых мест. Например прыжок через первый элемент таблицы в надежде на то, что именно он являет PK.

Заключение

Проделанное выше может оказаться для кого-то полезным, на что автор искренне надеется, так-как он не смог найти подобных решений. Но решение, представленное на суд сообществу не является оптимальным с точки зрения временных затрат и требует серьезных изменений. Я надеюсь, что все заинтересовавшиеся помогут мне довести его до ума (если в этом есть смысл) либо указать иной путь.

Ссылки по теме