Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Всем привет.

Необходим скрипт, который генерирует и заполняет целевую таблицу тестовыми (псевдослучайными) записями.
Целевой объём - 3 миллиарда строк.

Нашёл здесь вот такое решение:
SELECT t.rn, 'val' + CAST(t.rn as varchar(10)) v, NULL f3, NULL f4
--INTO Form
FROM (
SELECT  ROW_NUMBER() OVER (ORDER BY v1.type) rn
FROM    master..spt_values v1 ,
        master..spt_values v2 ,
        master..spt_values v3 ,
        master..spt_values v4
) t
WHERE t.rn<1000 -- 100000000

Чем не подходит? Нужно лить данные в существующую таблицу с существующими ограничениями (индексами).

Скорость выполнения имеет значение. Поэтому циклы, рекурсии нежелательны.
Что-нибудь типа INSERT INTO ... SELECT <Здесь создание "виртуальной таблицы", типа DUMMY>. Ну, или ваш вариант.
10 мар 17, 17:35    [20283125]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
iljy
Member

Откуда:
Сообщений: 8711
voix_kas,

не понял, вопрос-то в чем? Вам нужно лить данные в существующую таблицу, или не подходит тем, что нужно лить в существующую таблицу? В существующую можно лить с помощью insert ... select ..., лить одновременно с созданием - с помощью select ... into. Обе формы легко находятся в документации
10 мар 17, 17:40    [20283133]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
iljy, мне необходимо залить данные в уже существующую таблицу.
iljy
В существующую можно лить с помощью insert ... select ...

Не могли бы Вы привести рабочий пример?
10 мар 17, 17:47    [20283153]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
komrad
Member

Откуда:
Сообщений: 5252
voix_kas
Скорость выполнения имеет значение. Поэтому циклы, рекурсии нежелательны.

ну найдите тогда себе готовый источник этих данных

скорость заполнения зависит от структуры таблицы и способа заливки
и практически не зависит от того, как тестовые данные генерятся

приведенный Вами скипт легко модифицируется под ваши требования

insert into YOURTABLE(field1,field2,...fieldN)
SELECT t.rn, 'val' + CAST(t.rn as varchar(10)) v, NULL f3, NULL f4
--INTO Form
FROM (
SELECT  ROW_NUMBER() OVER (ORDER BY v1.type) rn
FROM    master..spt_values v1 ,
        master..spt_values v2 ,
        master..spt_values v3 ,
        master..spt_values v4
) t
WHERE t.rn<1000 -- 100000000 
10 мар 17, 17:56    [20283192]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
iljy
Member

Откуда:
Сообщений: 8711
voix_kas
iljy, мне необходимо залить данные в уже существующую таблицу.
iljy
В существующую можно лить с помощью insert ... select ...

Не могли бы Вы привести рабочий пример?


http://lmgtfy.com/?q=mssql insert select
10 мар 17, 17:59    [20283199]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
abcx
Guest
voix_kas
Нужно лить данные в существующую таблицу с существующими ограничениями (индексами).

Скорость выполнения имеет значение



Идндексы лучше дропнуть перед вставкой и пересоздать вконце.
Вставлять лучше кусками, например по 1М записей, в явной транзакции.
10 мар 17, 18:20    [20283254]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
abcx
в явной транзакции
Это зачем же?
10 мар 17, 18:21    [20283261]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest
iap
abcx
в явной транзакции
Это зачем же?

это наверное ораклоид пишет
боится, что коммита не будет
10 мар 17, 18:27    [20283286]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest
iljy
voix_kas,

не понял, вопрос-то в чем? Вам нужно лить данные в существующую таблицу, или не подходит тем, что нужно лить в существующую таблицу? В существующую можно лить с помощью insert ... select ..., лить одновременно с созданием - с помощью select ... into. Обе формы легко находятся в документации

видимо он хочет лить с минимальным логированием,
а индексы отключать не хочет,
+ желает кусками, что сразу означает вставкy в непустые индексы.
короче, полное логирование.
товарищ недоволен скоростью
10 мар 17, 18:31    [20283306]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
abcx
Guest
o-o
это наверное ораклоид пишет

нет. но явные признаки телепатии есть :))

o-o
боится, что коммита не будет

просто плохая привычка
10 мар 17, 18:32    [20283309]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
komrad спасибо, попробую.
о-о расскажите, плиз, как быстрее? Логгирование надо отключать? Как это сделать?
Индекс не хотел удалять, нужно проверить его работу.
10 мар 17, 18:36    [20283320]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest
в вариант комрада добавить with(tablock),
все индексы отключить (некластерным disable, кластерному дроп. или заскриптовать все и дропнуть)
после заливки отребилдить все индексы.
вот тогда будет минимальное логирование.
разумеется, если модель простая.
в полной ничего не поможет
10 мар 17, 18:43    [20283341]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3746
voix_kas
komrad спасибо, попробую.
о-о расскажите, плиз, как быстрее? Логгирование надо отключать? Как это сделать?
Индекс не хотел удалять, нужно проверить его работу.


не обязательно слушать о-о, он пишет как сделать правильно, а это обычно нудно и не интересно, нет задора,
а юзеру всегда хотелось бы по-быстрому, но чтобы правильно ;-D
10 мар 17, 18:46    [20283358]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
о-о спасибо за совет. Не могли бы привести готовый вариант скрипта с использование with? Я просто не понимаю, как работает приведенная мной конструкция. :(
10 мар 17, 18:46    [20283359]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Выполняю скрипт:
SELECT CAST(t.rn AS BIGINT) AS test_id, CAST(NEWID() AS NVARCHAR(200)) AS test_text
INTO test_table
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
	FROM master..spt_values v1,
		 master..spt_values v2,
		 master..spt_values v3
) t
WHERE t.rn < 1000

По результату создаётся таблица с двумя полями.

После этого мне приходится дополнительно выполнять:
ALTER TABLE test_table
	ALTER COLUMN test_id BIGINT NOT NULL
ALTER TABLE test_table
	ALTER COLUMN test_text NVARCHAR(200) NOT NULL
ALTER TABLE test_table
	ADD CONSTRAINT pk_test_id PRIMARY KEY CLUSTERED (test_id)


Вопроса 2:
1. Можно ли модифицировать первый запрос так, чтобы по результату его выполнения поля уже были с ограничением NOT NULL? В идеале, ещё с кластеризованным первичным ключом. Просто на большой таблице эти операции очень долго выполняются. Можно ли ускорить за счёт модификации первого скрипта или другими способами?
2. Как модифицировать первый скрипт так, чтобы создавалось ещё третье поле, например, с рандомным числом. Пробовал вставить функцию RAND(), но в результирующей таблице в этой графе одни и те же значения.
11 мар 17, 23:19    [20285686]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
1. Нет
2. Нет

Создавайте готовую таблицу нужной структуры и лейте туда данные с минимальным логированием (способы для этого есть).

Сообщение было отредактировано: 11 мар 17, 23:47
11 мар 17, 23:42    [20285757]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
iljy
Member

Откуда:
Сообщений: 8711
voix_kas,

сразу сделать поле NOT NULL можно, добавив в выражение вызов ISNULL. Но индексы создать в select into нельзя. Создавайте таблицу явно и используйте insert select.

Со случайными числами интереснее. Есть. например. такой способ:

create view rnd as select RAND() r
go
create function frnd() returns float as begin return (select * from rnd) end 
go
select top(100) dbo.frnd()
from sys.objects o

go

drop function frnd
drop view rnd


Но скалярные UDF - известный тормоз, и на миллиардах записей это может стать очень заметно. В качестве альтернативы можно использовать что-то вроде CHECKSUM(NEWID()), либо какие-нибудь варианты на тему SIN(ROW_NUMBER() * RAND()).
11 мар 17, 23:47    [20285774]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Гавриленко Сергей Алексеевич,
Можете более детально рассказать?:
1. Насколько я понимаю, INSERT в существующую таблицу будет значительно дольше.
2. Как отключить логирование (задать минимальное)?
11 мар 17, 23:48    [20285779]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
voix_kas
1. Насколько я понимаю, INSERT в существующую таблицу будет значительно дольше.
При соблюдении нужных условий будет даже быстрее на время создания пустой таблицы.
voix_kas
2. Как отключить логирование (задать минимальное)?
Смотрите ссылку в моем сообщении.

Сообщение было отредактировано: 11 мар 17, 23:50
11 мар 17, 23:50    [20285786]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
iljy
Но скалярные UDF - известный тормоз, и на миллиардах записей это может стать очень заметно. В качестве альтернативы можно использовать что-то вроде CHECKSUM(NEWID()), либо какие-нибудь варианты на тему SIN(ROW_NUMBER() * RAND()).

Спасибо! Вот так получилось:
SELECT CAST(t.rn AS BIGINT) AS test_id, CAST(RAND(CHECKSUM(NEWID())) AS NVARCHAR(10)) AS test_rand, CAST(NEWID() AS NVARCHAR(200)) AS test_text
INTO test_table
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
	FROM master..spt_values v1,
		 master..spt_values v2,
		 master..spt_values v3
) t
WHERE t.rn < 1000

Хз только, насколько робастными будут числа... Но для экспериментов сгодиться.
iljy
сразу сделать поле NOT NULL можно, добавив в выражение вызов ISNULL.

Можете подсказать конкретную конструкцию?
iljy
Но индексы создать в select into нельзя. Создавайте таблицу явно и используйте insert select.

Вы тоже, как и Гавриленко Сергей Алексеевич считаете, что в моём случает конструкция INSERT SELECT будет предпочтительнее (по скорости, при прочих равных)?
11 мар 17, 23:59    [20285821]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Гавриленко Сергей Алексеевич
Смотрите ссылку в моем сообщении.

Спасибо большое! Попробую разобраться.
12 мар 17, 00:01    [20285827]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
iljy
Member

Откуда:
Сообщений: 8711
voix_kas
iljy
сразу сделать поле NOT NULL можно, добавив в выражение вызов ISNULL.

Можете подсказать конкретную конструкцию?

SELECT ISNULL(CAST(t.rn AS BIGINT), 0) AS test_id


voix_kas
iljy
Но индексы создать в select into нельзя. Создавайте таблицу явно и используйте insert select.

Вы тоже, как и Гавриленко Сергей Алексеевич считаете, что в моём случает конструкция INSERT SELECT будет предпочтительнее (по скорости, при прочих равных)?

По скорости разницу вы вряд ли заметите, а вставка INSERT SELECT тоже может идти с минимальным логгированием в режимах SIMPLE и BULK LOGGED.
12 мар 17, 00:06    [20285848]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
iljy, спасибо, получилось:
SELECT	ISNULL(CAST(t.rn AS BIGINT), 0) AS test_id,
	ISNULL(CAST(RAND(CHECKSUM(NEWID())) AS NVARCHAR(20)), '') AS test_rand, 
	ISNULL(CAST(NEWID() AS NVARCHAR(200)), '') AS test_text
INTO test_table
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
	FROM master..spt_values v1,
		 master..spt_values v2,
		 master..spt_values v3
) t
WHERE t.rn < 1000




iljy
По скорости разницу вы вряд ли заметите, а вставка INSERT SELECT тоже может идти с минимальным логгированием в режимах SIMPLE и BULK LOGGED.

Пока знакомлюсь со статьёй, которой поделился Гавриленко Сергей Алексеевич.
Попробую вариант INSERT SELECT, так как уже на сотнях миллионов первичный ключ создаётся очень долго (для простых тестовых нужд).
12 мар 17, 00:20    [20285882]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Результаты на 1'000'000 записей (по 3 испытания):
+ Вариант 1 (SELECT ... INTO, без создания индекса PK): время - 1-3 сек., размер базы/лога - 136/72МБ
ALTER DATABASE Test
	SET RECOVERY BULK_LOGGED;

IF OBJECT_ID('test_table') IS NOT NULL
	DROP TABLE test_table;

DBCC TRACEON(610);

SELECT	ISNULL(CAST(t.rn AS BIGINT), 0) AS test_id,
		ISNULL(CAST(RAND(CHECKSUM(NEWID())) AS NVARCHAR(20)), '') AS test_rand, 
		ISNULL(CAST(NEWID() AS NVARCHAR(200)), '') AS test_text
INTO test_table
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
	FROM master..spt_values v1,
		 master..spt_values v2,
		 master..spt_values v3
) t
WHERE t.rn < 1000000

DBCC TRACEOFF(610);

ALTER DATABASE Test
	SET RECOVERY FULL;
+ Вариант 2 (SELECT ... INTO, с пост созданием индекса PK): время - 5-8 сек., размер базы/лога - 264/136МБ
ALTER DATABASE Test
	SET RECOVERY BULK_LOGGED;

IF OBJECT_ID('test_table') IS NOT NULL
	DROP TABLE test_table;

DBCC TRACEON(610);

SELECT	ISNULL(CAST(t.rn AS BIGINT), 0) AS test_id,
		ISNULL(CAST(RAND(CHECKSUM(NEWID())) AS NVARCHAR(20)), '') AS test_rand, 
		ISNULL(CAST(NEWID() AS NVARCHAR(200)), '') AS test_text
INTO test_table
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
	FROM master..spt_values v1,
		 master..spt_values v2,
		 master..spt_values v3
) t
WHERE t.rn < 1000000

ALTER TABLE test_table
	ADD CONSTRAINT pk_test_id PRIMARY KEY CLUSTERED (test_id)

DBCC TRACEOFF(610);

ALTER DATABASE Test
	SET RECOVERY FULL;
+ Вариант 3 (INSERT INTO... SELECT, с индексом PK): время - 10-15 сек., размер базы/лога - 136/328МБ
ALTER DATABASE Test
	SET RECOVERY BULK_LOGGED;

IF OBJECT_ID('test_table') IS NOT NULL
	DROP TABLE test_table;

CREATE TABLE test_table (
	test_id BIGINT NOT NULL,
	test_rand NVARCHAR(20) NOT NULL,
	test_text NVARCHAR(200) NOT NULL,
	CONSTRAINT pk#test_id PRIMARY KEY CLUSTERED (test_id)
)

DBCC TRACEON(610);
TRUNCATE TABLE test_table;

INSERT INTO test_table WITH (TABLOCK) (test_id, test_rand, test_text)
SELECT	ISNULL(CAST(t.rn AS BIGINT), 0) AS test_id,
		ISNULL(CAST(RAND(CHECKSUM(NEWID())) AS NVARCHAR(20)), '') AS test_rand, 
		ISNULL(CAST(NEWID() AS NVARCHAR(200)), '') AS test_text
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
	FROM master..spt_values v1,
		 master..spt_values v2,
		 master..spt_values v3
) t
WHERE t.rn < 1000000

DBCC TRACEOFF(610);

ALTER DATABASE Test
	SET RECOVERY FULL;
+ Вариант 4 (INSERT INTO... SELECT, с автоинкрементом и индексом PK): время - 10-15 сек., размер базы/лога - 136/328МБ
ALTER DATABASE Test
	SET RECOVERY BULK_LOGGED;

IF OBJECT_ID('test_table') IS NOT NULL
	DROP TABLE test_table;

CREATE TABLE test_table (
	test_id BIGINT IDENTITY (1, 1) NOT NULL,
	test_rand NVARCHAR(20) NOT NULL,
	test_text NVARCHAR(200) NOT NULL,
	CONSTRAINT pk#test_id PRIMARY KEY CLUSTERED (test_id)
)

DBCC TRACEON(610);
TRUNCATE TABLE test_table;

INSERT INTO test_table WITH (TABLOCK) (test_rand, test_text)
SELECT	ISNULL(CAST(RAND(CHECKSUM(NEWID())) AS NVARCHAR(20)), '') AS test_rand, 
		ISNULL(CAST(NEWID() AS NVARCHAR(200)), '') AS test_text
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
	FROM master..spt_values v1,
		 master..spt_values v2,
		 master..spt_values v3
) t
WHERE t.rn < 1000000

DBCC TRACEOFF(610);

ALTER DATABASE Test
	SET RECOVERY FULL;

После сжатия (DBCC SHRINKDATABASE(N'Test')) во всех трёх случаях размеры таблицы лога сокращались до 111/8МБ.

Получается, что SELECT ... INTO выигрывает в скорости? Или я что-то не так делаю?
Подскажите, пожалуйста.
12 мар 17, 01:54    [20286011]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3746
voix_kas
Результаты на 1'000'000 записей (по 3 испытания):

После сжатия (DBCC SHRINKDATABASE(N'Test')) во всех трёх случаях размеры таблицы лога сокращались до 111/8МБ.

Получается, что SELECT ... INTO выигрывает в скорости? Или я что-то не так делаю?
Подскажите, пожалуйста.


выигрывает, но вы должны отдавать себе отчет, что у этого тметода кроме преимуществ есть и недостатки, "робастные"
12 мар 17, 17:46    [20286978]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить