Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: ←Ctrl назад 1 [2] 3 вперед Ctrl→ все |
iljy Member Откуда: Сообщений: 8711 |
voix_kas, вы уверены, что у вас выполнялись условия для минимально логгируемых операций? |
12 мар 17, 18:03 [20287013] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
iljy, нет не уверен. Скорее даже есть подозрение, что минимального логгирования не было. Причина - размер логов - 328МБ против 72МБ. Ещё нагуглил две статьи по теме: 1, 2. В обоих вывод, что SELECT ... INTO работает быстрее. Всё ещё ожидаю помощи от компетентных специалистов в поисках решения САБЖа. Конечное, самое бескомпромиссное решение. Буду признателен за релевантные мысли/советы/готовые решения. |
12 мар 17, 20:10 [20287306] Ответить | Цитировать Сообщить модератору |
iljy Member Откуда: Сообщений: 8711 |
Ну так выполните их, в чем проблема-то? Благо для пустой таблицы их всего два: модель восстановления не полная и хинт TABLOCK на таблицу.
Че-то ваще не про то статьи. Первая про временные таблицы, вторая вообще про COLUMNSTORE. |
||||
12 мар 17, 22:56 [20287683] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
Читать надо не статьи, а документацию мелкософта. SELECT ... INTO и alter table ... add primary key минимально логгируемые операции. Всегда. Быстрее - не бывает. ЗЫ. INSERT ... select тоже может быть минимально логгируемой. Но условия, описанные в документации, нужно выполнить. |
||
13 мар 17, 08:05 [20288006] Ответить | Цитировать Сообщить модератору |
Нектотам
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
так во втором случае же ПК сразу сделан, вы понимаете, что вы вставляете рандомные значения, а не, допустим, монотонно возрастающие? какое может быть минимальное логирование, если вставка идет все время в уже существующие страницы? хоть с флагом, хоть без. и ведь это даже написано и нарисовано в предложенной вам статье: The Data Loading Performance Guide и про дропанье ПК вам тоже писали, как же вы читаете и не видите???
|
||||
13 мар 17, 13:04 [20289223] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
условия для минимального логирования не из пальца высосаны, как вы еще можете обеспечить транзакционную целостность вставки, и при этом не логировать построчно? ну очевидно же, что вставка должна идти в только новые страницы. тогда, логируя лишь номера вновь заполненных страниц, можно сделать откат. просто деаллокейтить все эти новые страницы. а если у вас случайные значения ключа, вставка идет гарантированно среди уже имеющихся, то это гарантированное построчное логирование. так что дело тут не в insert..select, а в том, что не выполнено условие вставки только на новыее страницы |
13 мар 17, 13:18 [20289296] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
o-o, можете привести конкретный код с INSERT ... SELECT? Конкретный код, который обеспечит минимальное логгирование. |
13 мар 17, 13:20 [20289305] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
у вас не автоинкремент. у вас ваставка рандомных значений. ваш row_number не имеет гарантированного порядка, или же должен быть явный order by в select |
13 мар 17, 13:24 [20289317] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
o-o я Вам предоставил код без индекса. БЕЗ. Индекса... БЕЗ. Скрипт всё-равно порождает 400МБ+ лога. Что я делаю не так? |
13 мар 17, 13:29 [20289341] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
да, вижу код без индекса, внешне все правильно, у меня тоже в таком варианте залогировалось полностью. сижу, ищу... |
||
13 мар 17, 13:47 [20289453] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
А разве конструкция "...ROW_NUMBER() OVER (ORDER BY v1.type)..." не гарантирует автоинкремент? |
||
13 мар 17, 13:58 [20289512] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
я не про то. автоинкементом называется генерация номеров без вашего участия. в SQL Server это identity. вы объявляете свойство поля, но явно значения не вставляете, за вас это делает сервер. ваш самодельный инкремент не "авто", это сгенеренные номера, не следующие в каком-то гарантированном порядке при вставке. порядок при вставке гарантирует ORDER BY в SELECT-е ---- но если лить в кучу, а не в индекс, какая кому разница, отсортировано это все или нет. тем не менее, в вашем примере без индекса идет построчное логирование, я чего-то не понимаю, с чего вдруг |
||||
13 мар 17, 14:06 [20289583] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
Правильно ли я Вас понимаю? Параметр ... 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] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
Если вдруг найдёте причину/ошибку, сообщите, пожалуйста. Было бы интересно повторно сравнить скорость с SELECT ... INTO. |
||
13 мар 17, 14:20 [20289667] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37143 |
|
||
13 мар 17, 14:21 [20289674] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
Ваш ответ порождает вопросы. :) Скрипт 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] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37143 |
|
||
13 мар 17, 14:28 [20289719] Ответить | Цитировать Сообщить модератору |
voix_kas Member Откуда: Сообщений: 73 |
Я понимаю, что тип поля не станет IDENTITY (1, 1). Речь о том, что каждая вновь добавляемая строка в целевую таблицу будет со значением первой колонки не единицу больше, чем предыдущая запись. Т.е. как будто значения были созданы автоинкрементом. Верно? Или я опять вас не понял? |
||||
13 мар 17, 14:34 [20289773] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37143 |
Ну, сиквенсы еще есть, там вообще через размер кеша можно играться частотой log flush. И да, identity сбрасывает не каждое значение, есть у него тоже кеш. Сообщение было отредактировано: 13 мар 17, 15:20 |
||
13 мар 17, 15:17 [20290031] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: ←Ctrl назад 1 [2] 3 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |