Доброго дня,
Перед разработчиком часто возникает задача провести тест базы данных на больших объемах данных, но откуда взять эти самые данные? Ведь всем известно, что структура базы может достигать 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
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.

Заключение


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