Доброго дня,
Перед разработчиком часто возникает задача провести тест базы данных на больших объемах данных, но откуда взять эти самые данные? Ведь всем известно, что структура базы может достигать over 50 таблиц, которые не очень хочется заполнять руками. А если подумать о внешних ключах и составных первичных ключах значения которых связаны с другими таблицами, то голова начинает нагреваться пропорционально старому AMD с отключенным охлаждением.
В интернете существует много решений заполнения базы данный случайными значениями с использованием средств .NET, C++, Java и.д. В данной статье будет освещена тема заполнения базы данных случайными значениями средствами T-SQL под управлением MS SQL Server.
Несколько дней назад я получил задачу по заполнению базы данных под управлением MS SQL Server случайными данными. Причем, вся реализация должна быть выполнена только средствами T-SQL. После долго поиска подобных решений на ресурсах пришел к выводу — придется делать самому и принялся за дело. Не являясь(до последнего времени) знатоком T-SQL, а имея лишь набор знаний из курса «Базы данных» университета, реализация получилась очень «костылявая» и медленная(основная проблема), но работающая.
Основная цель данной статьи обсудить с хабра сообществом возможность оптимизации решения, либо его Ctrl+A и Shift + Del с ссылкой на уже готовую реализацию.
И так, что было на входе:
Что со всем этим нужно было сделать:
Вся реализация получила вид вызывающих друг-друга процедур:
Предлагаю остановиться на каждой процедуре подробно. (предполагается, что читатель,в отличии от автора, знаком с основами SQL)
Я использовал один из первых попавшихся мне вариантов реализации с форума MS SQL. Процедура получает на вход длину строки, а на выходе выдает строку случайных символов типа NVARCHAR(MAX) нужного размера. В данном случая реализация не является критичной, так как не имеет серьезных временных затрат при больших объемах данных. Едем дальше.
Функция небольшая и не очень красивая (особенно место с SUBSTRING), но меня она вполне устроила своим быстродействием, так что пока оставляем ее и идем дальше.
И вот, не дойдя до «главной» процедуры мы получаем огромные временные затраты при заполнении внешнего ключа таблицы данными из найденной родительской таблицы. Если данный поиск заметь подстановкой случайных чисел в заданном диапозоне производительность резко возрастает. Возможно дело в SELECT'e из системной таблице и случайной сортировки. Для сравнения: запись 1 млн. строк в таблицу без FK занимает около 20 мин, запись 1 млн. строк в таблицу с FK занимает больше 17 часов. Для справки, запись одного миллиона строк чистым INSERT'ом в одно поле занимаем 6-10 сек.
На текущий момент я не смог придумать ничего более оптимального, что и послужило толчком к написанию этой статьи, но об этом в заключении.
Данная процедура является «относительно» не затратной по времени хотя и лезет в системные таблицы чтобы получить структуру пришедшей на вход таблицы, но содержит в себе несколько явных слабых мест. Например прыжок через первый элемент таблицы в надежде на то, что именно он являет PK.
Проделанное выше может оказаться для кого-то полезным, на что автор искренне надеется, так-как он не смог найти подобных решений. Но решение, представленное на суд сообществу не является оптимальным с точки зрения временных затрат и требует серьезных изменений. Я надеюсь, что все заинтересовавшиеся помогут мне довести его до ума (если в этом есть смысл) либо указать иной путь.
Перед разработчиком часто возникает задача провести тест базы данных на больших объемах данных, но откуда взять эти самые данные? Ведь всем известно, что структура базы может достигать over 50 таблиц, которые не очень хочется заполнять руками. А если подумать о внешних ключах и составных первичных ключах значения которых связаны с другими таблицами, то голова начинает нагреваться пропорционально старому AMD с отключенным охлаждением.
В интернете существует много решений заполнения базы данный случайными значениями с использованием средств .NET, C++, Java и.д. В данной статье будет освещена тема заполнения базы данных случайными значениями средствами T-SQL под управлением MS SQL Server.
Введение
Несколько дней назад я получил задачу по заполнению базы данных под управлением MS SQL Server случайными данными. Причем, вся реализация должна быть выполнена только средствами T-SQL. После долго поиска подобных решений на ресурсах пришел к выводу — придется делать самому и принялся за дело. Не являясь
Основная цель данной статьи обсудить с хабра сообществом возможность оптимизации решения, либо его Ctrl+A и Shift + Del с ссылкой на уже готовую реализацию.
И так, что было на входе:
- База данных с некоторым числом связанных таблиц;
- Все первичные ключи (дальше PK) — автоинкременты;
- Существуют таблицы содержащие в себе составной PK состоящих из внешних ключей (дальше FK).
Что со всем этим нужно было сделать:
- Генерировать случайные данные в зависимости от типа атрибута (столбца);
- Пропускать заполнение автоинкрементов.
- Заполнять FK дочерних таблиц случайными PK родительских таблиц.
Реализация
Вся реализация получила вид вызывающих друг-друга процедур:
- randomString — генерация случайной строки символов заданной длины.;
- randomInt — генерация случайного числа из заданного диапазона;
- generateDataByType — получает тип атрибута(столбца) таблицы и вызывает нужную процедуру генерации случайных значений.
- insertRandomData — основная процедура, просит на вход только имя таблицы и количество записей, которое вы хотите добавить
Предлагаю остановиться на каждой процедуре подробно. (предполагается, что читатель,
randomString
CREATE PROCEDURE [dbo].[randomString]
@inputSize int,
@outputRandomString nvarchar(max) output
AS
BEGIN
-- Любой известный алгоритм реализации случайной строки заданной длины.
END;
Я использовал один из первых попавшихся мне вариантов реализации с форума MS SQL. Процедура получает на вход длину строки, а на выходе выдает строку случайных символов типа NVARCHAR(MAX) нужного размера. В данном случая реализация не является критичной, так как не имеет серьезных временных затрат при больших объемах данных. Едем дальше.
randomInt
CREATE PROCEDURE [dbo].[randomInt]
@inputSize int,
@outputInteger int output
AS
BEGIN
DECLARE @TEMP bigint
SET @TEMP = SUBSTRING('999999999999999999',1,@inputSize)
SET @outputInteger = (ABS(CHECKSUM(NewId())) % @TEMP)
END
Функция небольшая и не очень красивая (особенно место с SUBSTRING), но меня она вполне устроила своим быстродействием, так что пока оставляем ее и идем дальше.
generateDataByType
CREATE PROCEDURE [dbo].[generateDataByType]
@tableName nvarchar(40), -- имя таблицы, для который будем генерировать данные
@inputColumName nvarchar(40), -- имя столбца, для которого будем генерировать данные
@inputType nvarchar(10),
@inputSize int,
@outputString nvarchar(max) output --готовая строка
AS
BEGIN
DECLARE @isFK bit = 0;
DECLARE @FKName NVARCHAR(MAX);
DECLARE @ParentTable NVARCHAR(MAX);
--Для @tableName получаем имена полей являющихся FK (ccu.table_name) и мена родительских таблиц (references_table) на которые эти ключи ссылаются и загоняем все под курсор
DECLARE columnsCursor1 CURSOR FOR
SELECT kcu.column_name,
ccu.table_name AS references_table
FROM information_schema.table_constraints tc
INNER JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
INNER JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
AND tc.constraint_type = 'FOREIGN KEY'
INNER JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE tc.table_name = @tableName
OPEN columnsCursor1;
FETCH NEXT FROM columnsCursor1
INTO @FKName,@ParentTable
--пробегаемся по каждому внешнему ключу
WHILE @@FETCH_STATUS = 0
BEGIN
-- проверяем, является ли пришедший на вход процедуре столбец @inputColumName найденным ранее внешним ключом данной таблицы
IF (@inputColumName = @FKName)
BEGIN
SET @isFK = 1; --устанавливаем флаг в true - работаем с FK и других проверок на тип данных делать не нужно.
DECLARE @selectedPK NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
-- формируем динамический запрос и забираем случайный первичный ключ из родительской таблицы, который и станет внешним ключом для текущей таблицы
SET @selectedPK = N'SELECT TOP 1 @outputString =' + @FKName + ' FROM ' + @ParentTable + ' ORDER BY NEWID(); ';
SET @params = N'@FKName NVARCHAR(MAX), @ParentTable NVARCHAR(MAX), @outputString NVARCHAR(MAX) OUTPUT';
EXEC sp_executesql @selectedPK , @params, @FKName = @FKName, @ParentTable = @ParentTable, @outputString = @outputString OUTPUT;
END
FETCH NEXT FROM columnsCursor1
INTO @FKName,@ParentTable
END;
CLOSE columnsCursor1;
DEALLOCATE columnsCursor1;
--если столбец таблицы не является внешним ключом заполняем его случайными данными.
IF (@isFK <> 1)
BEGIN
IF (@inputType = 'nvarchar')
BEGIN
EXECUTE randomString
@inputSize, @outputRandomString = @outputString OUTPUT ;
END
ELSE
--тоже самое выполняем для других типов данных и вызываем нужные процедуры.
END
И вот, не дойдя до «главной» процедуры мы получаем огромные временные затраты при заполнении внешнего ключа таблицы данными из найденной родительской таблицы. Если данный поиск заметь подстановкой случайных чисел в заданном диапозоне производительность резко возрастает. Возможно дело в SELECT'e из системной таблице и случайной сортировки. Для сравнения: запись 1 млн. строк в таблицу без FK занимает около 20 мин, запись 1 млн. строк в таблицу с FK занимает больше 17 часов. Для справки, запись одного миллиона строк чистым INSERT'ом в одно поле занимаем 6-10 сек.
На текущий момент я не смог придумать ничего более оптимального, что и послужило толчком к написанию этой статьи, но об этом в заключении.
insertRandomData
CREATE PROCEDURE [dbo].[insertRandomData]
@childTableName nvarchar(MAX),
@insertRowCount int
AS
BEGIN
DECLARE @i int = 0
/*ПЕРЕМЕННЫЕ ДЛЯ КУРСОРА*/
DECLARE @columnName NVARCHAR(30); DECLARE @columnType NVARCHAR(10); DECLARE @columnLenght INT; DECLARE @columnUniq INT;
/*ПЕРЕМЕННЫЕ ДЛЯ ДИНАМИЧЕСКОГО СОЗДАНИЯ ЗАПРОСА К БД*/
DECLARE @insertQuery NVARCHAR(MAX); DECLARE @insertColumnsQuery NVARCHAR(MAX); DECLARE @insertValuesQuery VARCHAR(MAX); DECLARE @params NVARCHAR(MAX);
SET @insertColumnsQuery = '';
SET @insertValuesQuery = '';
begin transaction
WHILE (@i < @insertRowCount)
BEGIN
DECLARE columnsCursor CURSOR FOR
----------Получаем в запросе типы и размеры столбцов таблицы @childTableName и загоняем под курсор-----
SELECT
all_columns.column_id,
all_columns.name,
systypes.name,
all_columns.max_length
FROM
SYS.all_objects
join SYS.all_columns on all_columns.object_id = all_objects.object_id
join SYS.systypes on all_columns.system_type_id = systypes.xtype
WHERE
all_objects.name like @childTableName and
all_objects.type = 'U' AND
systypes.name <> 'sysname'/*ПОЧЕМУ-ТО НА ОДНО ПОЛЕ ПРИХОДИТСЯ 2 ТИПА ДАННЫХ (ИСКЛЮЧАЕМ СИСТЕМНЫЕ)*/
ORDER BY
all_columns.column_id;
OPEN columnsCursor;
--Выполняет действие дважды, чтобы пропустить ID-автоинкремент (не самое лучшее решение)
FETCH NEXT FROM columnsCursor
INTO @columnUniq, @columnName, @columnType, @columnLenght;
FETCH NEXT FROM columnsCursor
INTO @columnUniq, @columnName, @columnType, @columnLenght;
DECLARE @tempLenght INT = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
/*ОПРЕДЕЛЯЕМ ДЛИНУ ПОЛЯ (ЕСЛИ ТЕКСТ ТО -1 ЗАМЕНЯЕМ НА 30) , -1 возвращается при MAX размере типа, в такие поля я буду вставлять строку из 30 символов. */
IF(@columnLenght >= 0)
BEGIN
SET @tempLenght = @columnLenght;
END
ELSE
BEGIN
SET @tempLenght = 30;
END
--формируем левую часть запроса INSERT, содержит название столбцов.
SET @insertColumnsQuery = @insertColumnsQuery + @columnName + ', ';
DECLARE @TEMPValues nvarchar(MAX) = ''
---Вызываем процедуру generateStringByType, записываем результат в TEMPValues---
EXECUTE generateDataByType
@childTableName,
@columnName,
@columnType,
@tempLenght,
@outputString = @TEMPValues OUTPUT
-- формируем правую часть запроса INSERT, содержит данные.
SET @insertValuesQuery = @insertValuesQuery +'''' + @TEMPValues + ''','
FETCH NEXT FROM columnsCursor
INTO @columnUniq, @columnName, @columnType, @columnLenght;
END;
--Убираем лишнюю запятую в конце каждой части запроса типа INSERT
SET @insertColumnsQuery = SUBSTRING(@insertColumnsQuery, 1, LEN(@insertColumnsQuery)-1);
SET @insertValuesQuery = SUBSTRING(@insertValuesQuery,1, LEN(@insertValuesQuery)-1);
-- Формируем запрос на вставку и выполняем его.
SET @insertQuery = N'INSERT INTO ' + @childTableName + N' (' + @insertColumnsQuery + N') VALUES (' + @insertValuesQuery + ') ;';
EXEC(@insertQuery);
--дальше ничего интересного
Данная процедура является «относительно» не затратной по времени хотя и лезет в системные таблицы чтобы получить структуру пришедшей на вход таблицы, но содержит в себе несколько явных слабых мест. Например прыжок через первый элемент таблицы в надежде на то, что именно он являет PK.
Заключение
Проделанное выше может оказаться для кого-то полезным, на что автор искренне надеется, так-как он не смог найти подобных решений. Но решение, представленное на суд сообществу не является оптимальным с точки зрения временных затрат и требует серьезных изменений. Я надеюсь, что все заинтересовавшиеся помогут мне довести его до ума (если в этом есть смысл) либо указать иной путь.