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

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

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

Откуда:
Сообщений: 73
 		iljy, нет не уверен. Скорее даже есть подозрение, что минимального логгирования не было. Причина - размер логов - 328МБ против 72МБ.
Ещё нагуглил две статьи по теме: 1, 2.
В обоих вывод, что SELECT ... INTO работает быстрее.

Всё ещё ожидаю помощи от компетентных специалистов в поисках решения САБЖа.
Конечное, самое бескомпромиссное решение.

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

Откуда:
Сообщений: 8711
voix_kas
iljy, нет не уверен. Скорее даже есть подозрение, что минимального логгирования не было. Причина - размер логов - 328МБ против 72МБ.

Ну так выполните их, в чем проблема-то? Благо для пустой таблицы их всего два: модель восстановления не полная и хинт TABLOCK на таблицу.


voix_kas
Ещё нагуглил две статьи по теме: 1, 2.
В обоих вывод, что SELECT ... INTO работает быстрее.

Че-то ваще не про то статьи. Первая про временные таблицы, вторая вообще про COLUMNSTORE.
12 мар 17, 22:56    [20287683]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
 		iljy, так я же так и сделал:
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);

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;

В чём ошибка? Что забыл?
12 мар 17, 23:05    [20287708]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
aleks2
Guest
voix_kas
iljy, нет не уверен. Скорее даже есть подозрение, что минимального логгирования не было. Причина - размер логов - 328МБ против 72МБ.
Ещё нагуглил две статьи по теме: 1, 2.
В обоих вывод, что SELECT ... INTO работает быстрее.

Всё ещё ожидаю помощи от компетентных специалистов в поисках решения САБЖа.
Конечное, самое бескомпромиссное решение.

Буду признателен за релевантные мысли/советы/готовые решения.


Читать надо не статьи, а документацию мелкософта.

SELECT ... INTO
и
alter table ... add primary key

минимально логгируемые операции.
Всегда.
Быстрее - не бывает.

ЗЫ. INSERT ... select тоже может быть минимально логгируемой. Но условия, описанные в документации, нужно выполнить.
13 мар 17, 08:05    [20288006]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Нектотам
Guest 		voix_kas,

Про bulk logged в частности и быструю загрузку данных в общем:

The Data Loading Performance Guide
We Loaded 1TB in 30 Minutes with SSIS, and So Can You

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

Откуда:
Сообщений: 73
 		Нектотам, спасибо за статьи, но ничего нового в них не нашёл.

Вариант
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;
выполняется на порядок быстрее, чем
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);

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;


Размер лога транзакций в первом случае - 136МБ (в варианте без создания первичного ключа - 72МБ).
Во втором случае - 328МБ.

Видимо, минимальное логгирование INSERT ... INTO не такое уж минимальное. Или я что-то неверно делаю.
Пока остановлюсь на SELECT ... INTO. Но если у кого ещё мысли будут по поводу минимального логгирования INSERT ... INTO, буду признателен.
13 мар 17, 12:51    [20289161]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest
voix_kas
Видимо, минимальное логгирование INSERT ... INTO не такое уж минимальное. Или я что-то неверно делаю.

так во втором случае же ПК сразу сделан,
вы понимаете, что вы вставляете рандомные значения,
а не, допустим, монотонно возрастающие?
какое может быть минимальное логирование,
если вставка идет все время в уже существующие страницы?
хоть с флагом, хоть без.
и ведь это даже написано и нарисовано в предложенной вам статье:
The Data Loading Performance Guide

и про дропанье ПК вам тоже писали, как же вы читаете и не видите???
o-o
в вариант комрада добавить with(tablock),
все индексы отключить (некластерным disable, кластерному дроп. или заскриптовать все и дропнуть)
после заливки отребилдить все индексы.
вот тогда будет минимальное логирование.
разумеется, если модель простая.
в полной ничего не поможет
13 мар 17, 13:04    [20289223]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
 		o-o, в поле с первичным ключом вставляется автоинкремент (ROW_NUMBER() OVER (ORDER BY v1.type)), в остальные два поля - рандом.
Окей, заливаем в таблицу без индексов методом INSERT ... SELECT:
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
)

DBCC TRACEON(610);

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;

Время выполнения 15 секунд, размер лога - 456МБ.

Приведите, пожалуйста, Ваш код. Или ткните пальцем, что именно я не так делаю?
13 мар 17, 13:16    [20289288]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest 		условия для минимального логирования не из пальца высосаны,
как вы еще можете обеспечить транзакционную целостность вставки,
и при этом не логировать построчно?
ну очевидно же, что вставка должна идти в только новые страницы.
тогда, логируя лишь номера вновь заполненных страниц,
можно сделать откат.
просто деаллокейтить все эти новые страницы.
а если у вас случайные значения ключа, вставка идет гарантированно среди уже имеющихся,
то это гарантированное построчное логирование.
так что дело тут не в insert..select,
а в том, что не выполнено условие вставки только на новыее страницы
13 мар 17, 13:18    [20289296]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
 		o-o, можете привести конкретный код с INSERT ... SELECT? Конкретный код, который обеспечит минимальное логгирование.
13 мар 17, 13:20    [20289305]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest 		у вас не автоинкремент.
у вас ваставка рандомных значений.
ваш row_number не имеет гарантированного порядка,
или же должен быть явный order by в select
13 мар 17, 13:24    [20289317]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
 		o-o я Вам предоставил код без индекса. БЕЗ. Индекса... БЕЗ.
Скрипт всё-равно порождает 400МБ+ лога. Что я делаю не так?
13 мар 17, 13:29    [20289341]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest
voix_kas
o-o я Вам предоставил код без индекса. БЕЗ. Индекса... БЕЗ.
Скрипт всё-равно порождает 400МБ+ лога. Что я делаю не так?

да, вижу код без индекса, внешне все правильно,
у меня тоже в таком варианте залогировалось полностью.
сижу, ищу...
13 мар 17, 13:47    [20289453]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
o-o
у вас не автоинкремент.
у вас ваставка рандомных значений.
ваш row_number не имеет гарантированного порядка,
или же должен быть явный order by в select

А разве конструкция "...ROW_NUMBER() OVER (ORDER BY v1.type)..." не гарантирует автоинкремент?
13 мар 17, 13:58    [20289512]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest
voix_kas
o-o
у вас не автоинкремент.
у вас ваставка рандомных значений.
ваш row_number не имеет гарантированного порядка,
или же должен быть явный order by в select

А разве конструкция "...ROW_NUMBER() OVER (ORDER BY v1.type)..." не гарантирует автоинкремент?

я не про то.
автоинкементом называется генерация номеров без вашего участия.
в SQL Server это identity.
вы объявляете свойство поля,
но явно значения не вставляете, за вас это делает сервер.

ваш самодельный инкремент не "авто",
это сгенеренные номера, не следующие в каком-то гарантированном порядке при вставке.
порядок при вставке гарантирует ORDER BY в SELECT-е
----
но если лить в кучу, а не в индекс,
какая кому разница, отсортировано это все или нет.
тем не менее, в вашем примере без индекса идет построчное логирование,
я чего-то не понимаю, с чего вдруг
13 мар 17, 14:06    [20289583]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
o-o
voix_kas
пропущено...

А разве конструкция "...ROW_NUMBER() OVER (ORDER BY v1.type)..." не гарантирует автоинкремент?

я не про то.
автоинкементом называется генерация номеров без вашего участия.
в SQL Server это identity.
вы объявляете свойство поля,
но явно значения не вставляете, за вас это делает сервер.

ваш самодельный инкремент не "авто",
это сгенеренные номера, не следующие в каком-то гарантированном порядке при вставке.
порядок при вставке гарантирует ORDER BY в SELECT-е

Правильно ли я Вас понимаю? Параметр
...
SELECT ROW_NUMBER() OVER (ORDER BY v1.type) rn
...
в конструкции
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
не гарантирует физическую последовательную вставку?
Т.е. виртуально генерируемая в памяти таблица не по порядку записывается в целевую таблицу "test_table" на диск?
13 мар 17, 14:18    [20289662]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
o-o
тем не менее, в вашем примере без индекса идет построчное логирование,
я чего-то не понимаю, с чего вдруг

Если вдруг найдёте причину/ошибку, сообщите, пожалуйста. Было бы интересно повторно сравнить скорость с SELECT ... INTO.
13 мар 17, 14:20    [20289667]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
voix_kas
не гарантирует физическую последовательную вставку?
Гарантирует только _логически_ последовательную вставку. Что там сервер делает физически -- это его дело.
13 мар 17, 14:21    [20289674]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Гавриленко Сергей Алексеевич
voix_kas
не гарантирует физическую последовательную вставку?
Гарантирует только _логически_ последовательную вставку. Что там сервер делает физически -- это его дело.

Ваш ответ порождает вопросы. :)
Скрипт
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
произведёт вставку строк в целевую таблицу с гарантированным "автоинкрементом"? Да/нет.
13 мар 17, 14:25    [20289702]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
voix_kas
произведёт вставку строк в целевую таблицу с гарантированным "автоинкрементом"? Да/нет.
Никаких автоикрементов ваш запрос не порождает. Почему - вам уже объяснили.
13 мар 17, 14:28    [20289719]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Гавриленко Сергей Алексеевич
voix_kas
произведёт вставку строк в целевую таблицу с гарантированным "автоинкрементом"? Да/нет.
Никаких автоикрементов ваш запрос не порождает. Почему - вам уже объяснили.

Я понимаю, что тип поля не станет IDENTITY (1, 1).

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

Верно? Или я опять вас не понял?
13 мар 17, 14:34    [20289773]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest 		вот вариант с минимальным логированием:
CREATE TABLE [dbo].[test_table1](
	[test_id] [bigint] identity NOT NULL,
	[test_rand] [varchar](20) NOT NULL,
	[test_text] [varchar](200) NOT NULL);
		
INSERT INTO test_table1 WITH (TABLOCK) (test_rand, test_text)
SELECT top (100000)
    ISNULL(CAST(RAND(CHECKSUM(NEWID())) AS NVARCHAR(20)), '') AS test_rand, 
        ISNULL(CAST(NEWID() AS NVARCHAR(200)), '') AS test_text
       FROM master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3;
13 мар 17, 14:41    [20289813]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
o-o
Guest 		прикольно,
что вот такой код тоже минимально логируется:
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)
)

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

и он выгоднее кода с identity,
т.к. сбрасывает каждое вновь сгенеренное значение в лог.
только не спрашивайте, откуда он в этом коде знает, что генерит последовательно,
ибо явного ORDER BY нет, а вставка в кластерный все равно логируется минимально.
копирайт кода не мой!
13 мар 17, 15:14    [20290016]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая генерация и заполнение таблицы миллиардом псевдослучайных записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
o-o
и он выгоднее кода с identity,
т.к. сбрасывает каждое вновь сгенеренное значение в лог.

Ну, сиквенсы еще есть, там вообще через размер кеша можно играться частотой log flush.

И да, identity сбрасывает не каждое значение, есть у него тоже кеш.

Сообщение было отредактировано: 13 мар 17, 15:20
13 мар 17, 15:17    [20290031]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: ←Ctrl  назад   1 [2] 3   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить