Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Медленная вставка в таблицу с кластерным индексом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
MSSQL 2008 R2 (10.50.6000.34)
Есть несколько больших таблиц (200 млн записей ) [T_Info1], [T_Info2]... вида:
create table [T_Info1]
(
    [id] int identity(1,1) not null
   ,[KeyField1] varchar(32) not null
   ,[KeyField2] varchar(16) not null
   ,[KeyField3] varchar(150) not null
   ,[Field1] varchar(150) not null
   ,[Field2] varchar(150) not null
   ,[Field3] varchar(150) not null
   ,[Field4] int not null
   ,[Field5] date not null
   ,[Field6] date
   ,constraint [PK_T_Info1] primary key clustered ([KeyField1],[KeyField2],[KeyField3])
)

Более никаких индексов нет.
Самописный софт в несколько потоков непрерывно обрабатывает данные. Каждый поток накапливает порцию в 100 000 строк, и копирует их во временную таблицу #Temp аналогичной структуры, затем выполняет вставку в одну из больших таблиц:
insert into [T_Info1] ([KeyField1],[KeyField2],[KeyField3],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6])
   select distinct [KeyField1],[KeyField2],[KeyField3],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6] from #Temp A
      where not exists (select * from [T_Info1] B where A.[KeyField1] = B.[KeyField1] and A.[KeyField2] = B.[KeyField2] and A.[KeyField3] = B.[KeyField3])

Причём одновременная вставка данных из разных потоков в одну таблицу исключена.
Пока основные таблицы были маленькими, вставка порции работала достаточно быстро (30-50 секунд), сейчас время увеличилось в 10 раз, что неприемлемо долго.
Что посоветуете? Поможет ли установка на кластерном индексе FillFactor, скажем, 50?
20 июл 17, 13:56    [20661486]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
На диске, где лежит файл БД, длина очереди диска меньше 5 не падает.
20 июл 17, 14:01    [20661513]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36928
Если это весь код, то кластерный индекс надо делать по id, по по текущим ключам достаточно некластерного.
Некластерному индексу, естественно, надо делать периодический мейнтененс.

Сообщение было отредактировано: 20 июл 17, 14:04
20 июл 17, 14:03    [20661522]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Гавриленко Сергей Алексеевич
Если это весь код, то кластерный индекс надо делать по id, по по текущим ключам достаточно некластерного.

Спасибо, а изменение Fill Factor не поможет?

Гавриленко Сергей Алексеевич
Некластерному индексу, естественно, надо делать периодический мейнтененс.

Какой именно?
20 июл 17, 14:08    [20661548]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
=Сергей=,

вы же понимаете, что fillfactor будет 50 только на момент перестройки индекса, при неравномерной вставке он быстро придёт к 100.
Разберитесь с корректностью клдастерного индекса
20 июл 17, 14:09    [20661553]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36928
=Сергей=
Гавриленко Сергей Алексеевич
Если это весь код, то кластерный индекс надо делать по id, по по текущим ключам достаточно некластерного.

Спасибо, а изменение Fill Factor не поможет?
Если проблема в расщеплении, то поможет.

=Сергей=
Гавриленко Сергей Алексеевич
Некластерному индексу, естественно, надо делать периодический мейнтененс.

Какой именно?
Fill Factor надо поддерживать периодическими ребилдами.
20 июл 17, 14:11    [20661560]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36928
TaPaK
=Сергей=,

вы же понимаете, что fillfactor будет 50 только на момент перестройки индекса, при неравномерной вставке он быстро придёт к 100.
Разберитесь с корректностью клдастерного индекса
Из 50 сделать 100 проблематично. Нужен ну очень равномерный индекс и очень равномерная вставка.
20 июл 17, 14:12    [20661564]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Гавриленко Сергей Алексеевич
TaPaK
=Сергей=,

вы же понимаете, что fillfactor будет 50 только на момент перестройки индекса, при неравномерной вставке он быстро придёт к 100.
Разберитесь с корректностью клдастерного индекса
Из 50 сделать 100 проблематично. Нужен ну очень равномерный индекс и очень равномерная вставка.
если у него проблема именно из за сплита страниц, то как раз много времени не понадобится, для того что бы страницы продолжили делиться и размножаться :)
20 июл 17, 14:20    [20661593]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Гавриленко Сергей Алексеевич
надо делать по id

А для чего?
20 июл 17, 14:56    [20661743]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
=Сергей=
Гавриленко Сергей Алексеевич
надо делать по id

А для чего?
как раз для того что бы у вас не было сплитов и всё аккуратно писалось в конец
20 июл 17, 15:01    [20661763]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
aleks2
Guest
=Сергей=
Более никаких индексов нет.
Самописный софт в несколько потоков непрерывно обрабатывает данные. Каждый поток накапливает порцию в 100 000 строк, и копирует их во временную таблицу #Temp аналогичной структуры, затем выполняет вставку в одну из больших таблиц:
insert into [T_Info1] ([KeyField1],[KeyField2],[KeyField3],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6])
   select distinct [KeyField1],[KeyField2],[KeyField3],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6] from #Temp A
      where not exists (select * from [T_Info1] B where A.[KeyField1] = B.[KeyField1] and A.[KeyField2] = B.[KeyField2] and A.[KeyField3] = B.[KeyField3])

Причём одновременная вставка данных из разных потоков в одну таблицу исключена.
Пока основные таблицы были маленькими, вставка порции работала достаточно быстро (30-50 секунд), сейчас время увеличилось в 10 раз, что неприемлемо долго.
Что посоветуете? Поможет ли установка на кластерном индексе FillFactor, скажем, 50?


1. Убрать select distinct , вместо этого повесить на #Temp уникальный индекс с IGNORE_DUP_KEY = ON/
2. Изучить требования к BULK INSERT.
3. Попытаться их выполнить для [T_Info1].
20 июл 17, 15:16    [20661825]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
DirksDR
Member

Откуда: Пермь
Сообщений: 340
=Сергей=
Причём одновременная вставка данных из разных потоков в одну таблицу исключена.
Пока основные таблицы были маленькими, вставка порции работала достаточно быстро (30-50 секунд), сейчас время увеличилось в 10 раз, что неприемлемо долго.
Что посоветуете? Поможет ли установка на кластерном индексе FillFactor, скажем, 50?

Временную таблицу сортируете по ключам, перед тем, как заливать в большую?
Вставка в разные таблицы идет одновременно? Можно сделать по очереди?
Может, сначала удалить из временной таблицы совпадающие ключи
delete from #Temp A
      where exists (select * from [T_Info1] B where A.[KeyField1] = B.[KeyField1] and A.[KeyField2] = B.[KeyField2] and A.[KeyField3] = B.[KeyField3])

а потом заливать без проверки where not exists.
20 июл 17, 15:23    [20661849]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36928
DirksDR
Может, сначала удалить из временной таблицы совпадающие ключи
delete from #Temp A
      where exists (select * from [T_Info1] B where A.[KeyField1] = B.[KeyField1] and A.[KeyField2] = B.[KeyField2] and A.[KeyField3] = B.[KeyField3])


а потом заливать без проверки where not exists.
Удалять, как я понимаю, надо чтобы еще и tempdb поднагрузить?
20 июл 17, 15:25    [20661858]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
согласшусь с алексом, IGNORE_DUP_KEY = ON особенно когда объект транспортировочный очень удобный вариант
20 июл 17, 15:28    [20661874]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
DirksDR
Member

Откуда: Пермь
Сообщений: 340
Гавриленко Сергей Алексеевич
Удалять, как я понимаю, надо чтобы еще и tempdb поднагрузить?

Сорри, о какой нагрузке речь?
ИМХО, 100 тыс. записей временной таблицы должны уместиться в ОЗУ, без нагрузки на диск.
Меня больше смущает, что в этом случае большая таблица должна прочитаться дважды,
один раз для сверки с временной, другой раз для вставки в нее.
ТС ничего не написал про размеры таблиц в Гб, про размер буферной памяти, про дисковую подсистему.
Тут можно такого насоветовать:)
20 июл 17, 15:40    [20661918]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
o-o
Guest
Гавриленко Сергей Алексеевич
Удалять, как я понимаю, надо чтобы еще и tempdb поднагрузить?

на этой неделе мода на DELETE.
но и да, чего это темпдб расслабляется?
полностью логируемые операции -- наше все.
как там говорил Козлов?
Колян Козлов
Получаются все минусы шринка без его плюсов.
Спасибо, то, что надо.
20 июл 17, 15:44    [20661933]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
o-o
Guest
DirksDR
ИМХО, 100 тыс. записей временной таблицы должны уместиться в ОЗУ, без нагрузки на диск.

а каждые 60Кb записей лога сбрасываются на диск,
хотя прекрасно умещаются в ОЗУ, сюрприз?
DELETE = FULLY LOGGED OPERATION
20 июл 17, 15:46    [20661943]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36928
DirksDR
ИМХО, 100 тыс. записей временной таблицы должны уместиться в ОЗУ, без нагрузки на диск.
А лог у вас куда уместится?
DirksDR
Меня больше смущает, что в этом случае большая таблица должна прочитаться дважды,
Большая таблица -- сюрприз -- будет "читаться" в обоих случаях одинаковое кол-во раз. Только вот в случае delete из времянки это все еще и в лог запишется.

Сообщение было отредактировано: 20 июл 17, 16:41
20 июл 17, 16:41    [20662133]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
DirksDR
Member

Откуда: Пермь
Сообщений: 340
o-o, Гавриленко Сергей Алексеевич

Действительно, сюрприз. Спасибо.
Казалось бы, для чего записывать в лог изменения временной таблицы? Для ROLLBACK-а должно быть...

=Сергей=
Можно попробовать увеличить порцию заливаемых данных, скажем, не по 100 тыс., а по 500 тыс.
Мне кажется, время записи порции возрастет не в 5 раз, а меньше.

Интересно, прокатит ли такой вариант, с предварительной сортировкой:
insert into [T_Info1] ([KeyField1],[KeyField2],[KeyField3],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6])
select [KeyField1],[KeyField2],[KeyField3],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6] from   
(select distinct [KeyField1],[KeyField2],[KeyField3],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6] from #Temp A
      where not exists (select * from [T_Info1] B where A.[KeyField1] = B.[KeyField1] and A.[KeyField2] = B.[KeyField2] and A.[KeyField3] = B.[KeyField3])
order by [KeyField1],[KeyField2],[KeyField3]) 

Хотя, оптимизатор м.б. уже это сделал сам.

П.С.Загружал как-то большой файл таким способом. Правда, порции были около 3 млн, грузил в один поток,
журналирование отключал, да и СУБД была другая:)
21 июл 17, 07:10    [20663295]     Ответить | Цитировать Сообщить модератору
 Re: Медленная вставка в таблицу с кластерным индексом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Друзья, всем ОГРОМНОЕ спасибо, сделал следующее:
1. кластерный индекс по id.
2. уникальный индекс по [KeyField1],[KeyField2],[KeyField3]

Всё заколосилось как раньше.
Ещё раз СПАСИБО!!!!
21 июл 17, 12:39    [20664420]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить