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

Откуда:
Сообщений: 63
Есть задача: перенести некую таблицу из одной БД в другую.

На тестовом прогоне запрос
Insert Into База1.Таблица1 ([куча полей])
select [куча полей] from База2.Таблица1

сделал все, как полагается, но это заняло достаточно много времени.

Знаю про существование BULK INSERT, который имеет бОльшую скорость работы за счет того, что операция вставки не логируется. Но этот оператор, если я правильно понимаю, работает только с файлом на входе.

Внимание вопрос:
Есть ли способ ускорить необходимый мне перенос данных за счет отключения логирования (или конструкция Insert Into уже является нелогируемой операцией и быстрее ничего не сделаешь?)

Поскольку в База1 Таблица1 пустая, я её могу даже грохнуть и сделать
select [куча полей] into База1.Таблица1 from База2.Таблица1 

а потом создать нужный индекс.
Будет ли это быстрее?
4 сен 19, 11:07    [21963096]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 3978
Попробуйте через Import Task.
4 сен 19, 11:12    [21963102]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
Satans Claws
Поскольку в База1 Таблица1 пустая, я её могу даже грохнуть и сделать
select [куча полей] into База1.Таблица1 from База2.Таблица1 

а потом создать нужный индекс.
Будет ли это быстрее?

будет, если модель простая или bulk_logged
4 сен 19, 11:15    [21963108]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
OBG
Member

Откуда:
Сообщений: 9
Satans Claws,
Заблокируйте таблицу (с помощью TABLOCK): https://docs.microsoft.com/ru-ru/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-2017
4 сен 19, 12:28    [21963271]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
OBG
Satans Claws,
Заблокируйте таблицу (с помощью TABLOCK): https://docs.microsoft.com/ru-ru/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-2017

и что?
1. если у него полная модель, то все равно полное логирование обеспечено
2. он написал, на таблице есть индекс.
вот тут правильная таблица: The Data Loading Performance Guide
сходите и убедитесь, что одного таблока точно не хватит,
как минимум нужен TF610,
но по личному опыту: и с флагом тоже при имеющемся индексе будет полное логирование
4 сен 19, 13:01    [21963332]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
aleks222
Member

Откуда:
Сообщений: 730
Yasha123
вот тут правильная таблица: The Data Loading Performance Guide


Там показания сильно несогласованы.

Additionally, a bulk load operation will be minimally logged even without the trace flag when both of the following conditions are true:
•A WITH (TABLOCK) hint is specified on the target table
•The target table is empty
4 сен 19, 15:50    [21963603]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
aleks222
Yasha123
вот тут правильная таблица: The Data Loading Performance Guide


Там показания сильно несогласованы.

Additionally, a bulk load operation will be minimally logged even without the trace flag when both of the following conditions are true:
•A WITH (TABLOCK) hint is specified on the target table
•The target table is empty

что не так-то?
4 сен 19, 15:57    [21963614]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
OBG
Member

Откуда:
Сообщений: 9
Вот тут http://www.t-sql.ru/post/min_logged_with_full_recovery.aspx есть пример, разница во времени выполнения запросов с TABLOCK и без составляет 25% в базе данных с режимом восстановления FULL.
У меня в этом же примере разница составила почти 4 раза (предварительно увеличил базу до необходимого размера, данные в кэш загрузил):
 SQL Server Execution Times:
   CPU time = 4110 ms,  elapsed time = 8804 ms.

 SQL Server Execution Times:
   CPU time = 9641 ms,  elapsed time = 33096 ms.
4 сен 19, 16:05    [21963625]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
OBG
Вот тут http://www.t-sql.ru/post/min_logged_with_full_recovery.aspx есть пример, разница во времени выполнения запросов с TABLOCK и без составляет 25% в базе данных с режимом восстановления FULL.
У меня в этом же примере разница составила почти 4 раза (предварительно увеличил базу до необходимого размера, данные в кэш загрузил):
 SQL Server Execution Times:
   CPU time = 4110 ms,  elapsed time = 8804 ms.

 SQL Server Execution Times:
   CPU time = 9641 ms,  elapsed time = 33096 ms.

чукча не читатель?
у ТС на таблице индекс.
а в этом примере нет.
ну навесьте индекс и заново проверьте
4 сен 19, 16:13    [21963638]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
OBG
Вот тут http://www.t-sql.ru/post/min_logged_with_full_recovery.aspx есть пример, разница во времени выполнения запросов с TABLOCK и без составляет 25% в базе данных с режимом восстановления FULL.
У меня в этом же примере разница составила почти 4 раза (предварительно увеличил базу до необходимого размера, данные в кэш загрузил):
 SQL Server Execution Times:
   CPU time = 4110 ms,  elapsed time = 8804 ms.

 SQL Server Execution Times:
   CPU time = 9641 ms,  elapsed time = 33096 ms.

да и ваша "полная модель" вызывает сомнения.
база тестовая?
бэкап делали?
липой попахивает
4 сен 19, 16:16    [21963641]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
OBG
Member

Откуда:
Сообщений: 9
Yasha123
OBG
Вот тут http://www.t-sql.ru/post/min_logged_with_full_recovery.aspx есть пример, разница во времени выполнения запросов с TABLOCK и без составляет 25% в базе данных с режимом восстановления FULL.
У меня в этом же примере разница составила почти 4 раза (предварительно увеличил базу до необходимого размера, данные в кэш загрузил):
 SQL Server Execution Times:
   CPU time = 4110 ms,  elapsed time = 8804 ms.

 SQL Server Execution Times:
   CPU time = 9641 ms,  elapsed time = 33096 ms.

чукча не читатель?


Это вы про себя?

у ТС на таблице индекс.
а в этом примере нет.
ну навесьте индекс и заново проверьте


ТС пишет, что может удалить не только индекс, но и таблицу:
Поскольку в База1 Таблица1 пустая, я её могу даже грохнуть и сделать 
select [куча полей] into База1.Таблица1 from База2.Таблица1 

а потом создать нужный индекс.
4 сен 19, 16:38    [21963661]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
OBG
Member

Откуда:
Сообщений: 9
Yasha123
OBG
Вот тут http://www.t-sql.ru/post/min_logged_with_full_recovery.aspx есть пример, разница во времени выполнения запросов с TABLOCK и без составляет 25% в базе данных с режимом восстановления FULL.
У меня в этом же примере разница составила почти 4 раза (предварительно увеличил базу до необходимого размера, данные в кэш загрузил):
 SQL Server Execution Times:
   CPU time = 4110 ms,  elapsed time = 8804 ms.

 SQL Server Execution Times:
   CPU time = 9641 ms,  elapsed time = 33096 ms.

да и ваша "полная модель" вызывает сомнения.
база тестовая?
бэкап делали?
липой попахивает


По ссылке тяжело перейти? Там все написано
4 сен 19, 16:39    [21963664]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
автор
У меня в этом же примере разница составила почти 4 раза (предварительно увеличил базу до необходимого размера, данные в кэш загрузил):

ага, ещё лог урезать в минимум и вообще можно не нарадоваться
4 сен 19, 16:44    [21963672]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
OBG
ТС пишет, что может удалить не только индекс, но и таблицу:
Поскольку в База1 Таблица1 пустая, я её могу даже грохнуть и сделать 
select [куча полей] into База1.Таблица1 from База2.Таблица1 

а потом создать нужный индекс.

ТС-то пишет, а вы -- НЕТ.
вы ему советуете голый таблок.

вот вам репро с голым таблоком в присутствии индекса,
с таблицами из той самой ссылки,
которую типа мне надо открыть:

К сообщению приложен файл. Размер - 72Kb
4 сен 19, 16:49    [21963681]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
TaPaK
автор
У меня в этом же примере разница составила почти 4 раза (предварительно увеличил базу до необходимого размера, данные в кэш загрузил):

ага, ещё лог урезать в минимум и вообще можно не нарадоваться

разница в 4 раза это простая модель в явном виде.
типа перевел базу в полную, бэкап сделать "забыл".
его в явном виде спрашиваешь про бэкап, а в ответ "сам чукча"
4 сен 19, 16:53    [21963687]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
OBG
Member

Откуда:
Сообщений: 9
Кластерный индекс по val varchar(255)?

Модератор: Божественное вложение от мастера скриншотов удалено.
4 сен 19, 17:08    [21963705]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
OBG
Кластерный индекс по val varchar(255)?

Модератор: Божественное вложение от мастера скриншотов удалено.

какой индекс?
4 сен 19, 17:17    [21963722]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
OBG
Кластерный индекс по val varchar(255)?

Модератор: Божественное вложение от мастера скриншотов удалено.

не знаю, какой индекс был у оппонента,
мои индексы некластерные,
это ежику понятно,
ибо если неоговорено противное, индекс дефолтно создается некластерным:
BOL
If not otherwise specified, the default index type is NONCLUSTERED.

CREATE INDEX (Transact-SQL)
4 сен 19, 17:27    [21963733]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
в целях завершения дебатов
+ специально для чукчи
повторяю ровно тот самый эксперимент,
что описан по ссылке (см. картинку), без индексов

как видим, никакой разницы в 4 раза нет.
есть те самые 25% экономии по времени,
о которых пишет Князев.

так что

1. привет тем, кто переводит в полную модель без полного бэкапа.
мои поздравления, база ни в какой не в полной модели, она в pseudo simple.
иначе не видать вам приращения скорости в 4 раза.

2. Князев же показал, что базу можно восстановить на произвольный момент времени.
так что никакого минимального логирования в полной модели нет и быть не может.

полная модель -- это такая модель,
при кототрой записи лога позволяют полностью восстановить операцию.
ну так ушедшее в лог при инсерте с таблоком содержит все данные,
потому и возможно восстановление.
это никакое не allocation page, allocation page,..., пустышки, которые только для роллбэка и нужны.

то, о чем тут речь, зовется efficiently logged operation.
когда не построчно записи в лог валятся, а страницы целиком со всем своим содержимым.
еще раз: не пустышки, а страницы целиком

P.S: то, что Князев там пишет про truncate table, извините, неверно,
это тоже полностью логируемая операция,
просто она тоже "эффективно" логируется:
Minimal Logging
Randal
TRUNCATE TABLE is not a minimally-logged operation. It's logged exactly the same way in all recovery models. It's fully logged, but very efficiently logged, including using the deferred-drop mechanism.


К сообщению приложен файл. Размер - 78Kb
4 сен 19, 17:48    [21963762]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
ну и вот выхлоп из лога от последнего кода,
запущенного после бэкапа лога.
видно, что операций типа LOP_FORMAT_PAGE
в 14 раз меньше, чем LOP_INSERT_ROWS
(других операций в контексте кучи просто нет)
это потому, что на страницу влазят 14 записей вида int + varchar(255),
полностью забитыx 255 символами.

надеюсь, всем видно, что страницы ушли в лог вместо строк,
когда делался инсерт с таблоком.

К сообщению приложен файл. Размер - 77Kb
4 сен 19, 18:01    [21963771]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 32129
Блог
Многопоточно через SSIS в пустую и без индексов таблицу-приемник
4 сен 19, 21:14    [21963958]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Satans Claws
Member

Откуда:
Сообщений: 63
Yasha123
разница в 4 раза это простая модель в явном виде.
типа перевел базу в полную, бэкап сделать "забыл".


Сорри за оффтоп (я автор, мне можно :D ), но можно с этого момента подробнее?
Я правильно понимаю, что, поскольку в FULL-модели восстановления лог содержит информацию о всех транзакциях с момента последнего бэкапа - то пока бэкапа не будет, логу нет стартовой точки, чтоб начать хранить эту информацию (соответственно, в лог ничего не пишется).
Обратный переход (FULL -> SIMPLE) применяется сразу?
5 сен 19, 04:40    [21964132]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
msLex
Member

Откуда:
Сообщений: 6424
Satans Claws
Yasha123
разница в 4 раза это простая модель в явном виде.
типа перевел базу в полную, бэкап сделать "забыл".


Сорри за оффтоп (я автор, мне можно :D ), но можно с этого момента подробнее?
Я правильно понимаю, что, поскольку в FULL-модели восстановления лог содержит информацию о всех транзакциях с момента последнего бэкапа - то пока бэкапа не будет, логу нет стартовой точки, чтоб начать хранить эту информацию (соответственно, в лог ничего не пишется).
Обратный переход (FULL -> SIMPLE) применяется сразу?
Почти так, только проблема не в бекапе лога (в который попадает весь лог с предыдущего бекапа лога), а в его последующем восстановлении, которое не возможно без восстановления полного бекапа. И пока не сделан полный бекап, нет смысла и в полной моделе.
5 сен 19, 08:15    [21964167]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
msLex
Member

Откуда:
Сообщений: 6424
Yasha123
efficiently logged operation

А не встречался ли вам какой-нибудь список условий, при которых включается "постраничное" логирование?
По какой-то неясно для меня причине, он не совпадает с условиями минимпльного логирование?
5 сен 19, 09:32    [21964230]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить перенос данных из одной БД в другую?  [new]
Yasha123
Member

Откуда:
Сообщений: 1440
msLex
Yasha123
efficiently logged operation

А не встречался ли вам какой-нибудь список условий, при которых включается "постраничное" логирование?
По какой-то неясно для меня причине, он не совпадает с условиями минимпльного логирование?

мне не встречался,
но из соображений здравого смысла:

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

если вставка в кластерный с tablock и order by таблица пуста, все ровно то же самое,
мы валим точно в конец, т.е. пишем новые целые страницы подряд.
будет логироваться постранично.

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

т.е. на первый взгляд все те же условия, что и для минимального логирования в simple и bulk logged.
у вас есть примеры, когда не так?

думаю, еще есть ограничение на объем.
если строк мало, будет построчно в любой модели
5 сен 19, 10:01    [21964266]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить