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

Откуда:
Сообщений: 2
MS SQL 2008 R2
Есть выгрузка данных в таблицу из внешних приложений. В таблице около 90 полей, в основном типов varchar(50), decimal(15,2), индексов нет. Данные из этой таблицы используются для получения данных другой таблицы, есть соотв. процедура.

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

Сейчас в таблице порядка 10 млн. строк, но будет 300-400 млн.


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

1. Во время выгрузки данных индекс не держать, а перед выполнением процедуры его создавать, после выполнения снова удалять.
2. может есть более хорошее решение
4 окт 19, 14:34    [21986784]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19449
Я так понимаю, что между получением данных и процедурой заноса их в "другую таблицу" есть определённый зазор по времени, такой чисто административно-организационный? тогда конечно, сразу после получения данных индексируйте, пока не затребовалась процедура.

А если нет - так просто замерьте время "процедура без индекса" и "создание индекса и процедура с ним", ну и поступайте соответственно.
4 окт 19, 14:43    [21986794]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
uaggster
Member

Откуда:
Сообщений: 715
РоманR, Перед загрузкой Alter index disable, для всех индексов, кроме кластерного.
После вставки - ALTER INDEX ALL REBUILD.
4 окт 19, 14:44    [21986795]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
aleks222
Member

Откуда:
Сообщений: 747
РоманR
Если добавить индекс, то выгрузка данных существенно замедляется и иногда останавливается с ошибкой "Timeout expired", как я понимаю, идет перестройка индекса.


uaggster
РоманR, Перед загрузкой Alter index disable, для всех индексов, кроме кластерного.
После вставки - ALTER INDEX ALL REBUILD.


Знатоки понабежали.

Интересуюсь: каким боком наличие индекса может мешать чтению?

ЗЫ. Не, я, канешно, понимаю, что тредстартер путает право и лево, но обучившись правильной терминологии, он повысит свои шансы.
4 окт 19, 15:50    [21986869]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 32231
Блог
uaggster
РоманR, Перед загрузкой Alter index disable, для всех индексов, кроме кластерного.
После вставки - ALTER INDEX ALL REBUILD.


Ага, на таблице в 400 млн, а пока идет перестроение - курите бамбук
4 окт 19, 16:53    [21986935]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
Gerros
Member

Откуда: Харьков
Сообщений: 471
РоманR
Есть выгрузка данных в таблицу из внешних приложений

сколько строк в выгрузке?
4 окт 19, 21:19    [21987142]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
РоманR,

Если вы загружаете данные в пустую, только что созданную таблицу, то, разумеется, индексы нужно делать после того, как все данные загружены.
Иногда так же поступают с кластерным индексом (например, для многопоточной балк-вставки).
4 окт 19, 22:10    [21987159]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
РоманR
Member

Откуда:
Сообщений: 2
При выгрузке - вставка от 100 до 150 000 строк в таблицу. Чаще - от 3000 до 5000, максимум 150 т. это пару раз в месяц.


Критик
uaggster
РоманR, Перед загрузкой Alter index disable, для всех индексов, кроме кластерного.
После вставки - ALTER INDEX ALL REBUILD.


Ага, на таблице в 400 млн, а пока идет перестроение - курите бамбук


Сейчас ALTER INDEX ALL REBUILD занимает около 5 минут. Увеличение по времени прямо пропорционально увеличению количества записей или нет? В запасе пара часов.
5 окт 19, 13:23    [21987312]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
РоманR
Как лучше поступить в данной ситуации?
Выгружать в отдельную служебную таблицу, предварительно ее очистив.
После выгрузки переливать полученные данные в основную таблицу. Возможно получится даже с минимальным журналированием.
5 окт 19, 13:40    [21987320]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
РоманR
При выгрузке - вставка от 100 до 150 000 строк в таблицу. Чаще - от 3000 до 5000, максимум 150 т. это пару раз в месяц.
Тогда, наверное, перестраивать индекс не имеет смысла.
Вообще есть множество вариантов, зависит от функциональных требований.
Нужно обеспечить минимальное время блокировки, или максимально быструю загрузку? Или, может, максимально высокую производительность?
Данные загружаются равномерно по всей таблице, или какой то кусок? - подумать про секционирование.
Загружает один процесс, или множество, загружается сразу весь объём данных, или множество программ загрузки загружают в цикле по одной строке, разбирая какой то внешний поток?
5 окт 19, 23:57    [21987501]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
Gerros
Member

Откуда: Харьков
Сообщений: 471
РоманR
При выгрузке - вставка от 100 до 150 000 строк в таблицу. Чаще - от 3000 до 5000, максимум 150 т. это пару раз в месяц.
Сделайте самодельное секционирование: две таблицы, первая большая, со старыми данными и индексами, вторая пустая (с индексами или без индексов - пробовать надо).
Выгрузку заливаете во вторую, потом запускаете процедуру (её изменить придётся), которая переливает из первой и второй таблиц куда вам надо.
Как отработала - переливаете из второй в первую и чистите вторую.
6 окт 19, 14:16    [21987625]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
aleks222
Member

Откуда:
Сообщений: 747
Gerros
РоманR
При выгрузке - вставка от 100 до 150 000 строк в таблицу. Чаще - от 3000 до 5000, максимум 150 т. это пару раз в месяц.
Сделайте самодельное секционирование: две таблицы, первая большая, со старыми данными и индексами, вторая пустая (с индексами или без индексов - пробовать надо).
Выгрузку заливаете во вторую, потом запускаете процедуру (её изменить придётся), которая переливает из первой и второй таблиц куда вам надо.
Как отработала - переливаете из второй в первую и чистите вторую.

И в каком месте этой пляски с бубном возникнет чудесное ускорение?
6 окт 19, 18:03    [21987681]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
uaggster
Member

Откуда:
Сообщений: 715
aleks222
Gerros
пропущено...
Сделайте самодельное секционирование: две таблицы, первая большая, со старыми данными и индексами, вторая пустая (с индексами или без индексов - пробовать надо).
Выгрузку заливаете во вторую, потом запускаете процедуру (её изменить придётся), которая переливает из первой и второй таблиц куда вам надо.
Как отработала - переливаете из второй в первую и чистите вторую.

И в каком месте этой пляски с бубном возникнет чудесное ускорение?

Ну, если вставляемые/обновляемые можно каким то образом предварительно статически локализовать, например, вставляются данные только за последний год, или какого-то одного филиала, то всё действительно можно организовать "волшебным" образом.
1. Секционируем таблицу по этому статическому признаку. Выравниваем индексы.
2. Заводим рядом таблицу для загрузки, построенную на той же partition function. Возможно, имеет смысл - создать ее динамически. Таблицу, я имею ввиду.
3. Переливаем в нее данные из секции, которую собираемся модифицировать. insert into ... with (tablock) Select * from ... Where key = ключ секционирования.
4. Делаем Merge загружаемых данных в эту отдельную загрузочную секцию таблицу.
(Ну, или если перезагрузка идет всех данных - пропускаем п.3., а просто льём данные в пустую загрузочную секцию таблицу балком).
5. Включаем на загрузочной секции таблице констрейнты, перестраиваем индексы, пересчитываем статистику.
6. Вышибаем аналогичную секцию в рабочей таблице в пустую секцию, потом делаем свитч загрузочной секции в рабочую таблицу.

Вполне себе рабочая схема. Особенно для хранилищ, из которых непрерывно читают. Ну, или если загружаемые данные сильно разнятся с находящимися в таблице данными, и проще не разбираться, что поменялось, а перезагрузить всё целиком.
Достоинства:
Ничего не блокируется в принципе (переключение контекста, в общем, мгновенная операция), изменения в рабочей таблице, какими бы масштабными они ни были - происходят одномоментно и мгновенно. Момент "мгновенности и одномоментности" - управляется непосредственно dba, и может быть точно журналирован, в бюрократическом смысле, я имею ввиду, журналирован, вплоть до записи ручкой в бумажный журнал.
Начиная с 2016SP2 - можно строить хранилища на версии standard, в которой нет онлайн перестроения индексов.
Недостатки:
Много возни. Нужно много свободного места в базе, как минимум удвоенное количество загружаемого объема данных. Если данные в большой рабочей таблице могут изменяться (например, пользователями) - простая схема уже не пройдет. И, хотя извернуться можно, но геморрой будет жуткий.
Но, в своей узкой нише - почему бы не па?
7 окт 19, 10:25    [21987974]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь по индексам  [new]
Gerros
Member

Откуда: Харьков
Сообщений: 471
aleks222
И в каком месте этой пляски с бубном возникнет чудесное ускорение?
Цель этой пляски не чудесное ускорение, а равнодушное откладывание на потом неприятного замедления из-за вставки в таблицу с индексами.
7 окт 19, 17:22    [21988583]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить