Индексы таблицы InventSettlement

[Kabardian]

Кто-нибудь знает правильные ответы на вопросы:

1. Почему в стандарте на таблице InventSettlement нет индекса по полю Cancelled? Нашел немало кода, где имеется проверка по полю, а индекс отсутсвует, код детально не изучал, поэтому пока сложно самому ответить на вопрос.
   
   
2. Стоит ли добавить индекс, если в таблице InventSettlement более чем 40 млн записей?

Честно потратил 10-15 минут на поиск, но вроде никто никогда не задавал таких вопросов.

Знаю, что можно удалить записи с Cancelled=NoYes::Yes, сейчас вопрос в том, имеет ли такая оптимизация право на жизнь и какой будет эффект.

[sukhanchik]

Цитата:

Сообщение от Kabardian

Почему в стандарте на таблице InventSettlement нет индекса по полю Cancelled? Нашел немало кода, где имеется проверка по полю, а индекс отсутсвует, код детально не изучал, поэтому пока сложно самому ответить на вопрос.

Для ответа на сей вопрос - нужно понять - что нам даст индекс по этому полю.
Теоретически, при сортировке по индексу (в порядке полей, указанных в индексе) - мы можем уменьшить количество записей, внутри которых осуществляется поиск.
Т.е. написав where cancelled = NoYes::No мы отсекаем кучу записей... Но и куча остается. Оставшееся количество все равно велико (нам индекс радикально не помог), а значит оптимизатор будет искать иные индексы для поиска.

Пример. Вы хотите в библиотеке найти книжку автора Пупкина, выпущенную в РФ.
Как Вы будете сначала искать - по фамилии или по стране выпуска? (Предположим, что в библиотеке есть книжки, выпущенные только в СССР и РФ). При этом - книжек, выпущенных в РФ заведомо меньше.
Скорее всего Вы будете искать книжку по фамилии и наплюете на страну издания (т.е. на первое место в индексе поиска поставите не страну, а фамилию).
Также посчитает и оптимизатор.
Также, обращаю внимание - что в идеале - индекс нужен по всем полям, входящим в условие WHERE, а также в сортировку / группировку.
При этом в индексе должны быть на первых местах наиболее селективные поля (т.е. поля, количество значений в которых наиболее уникально в таблице). В случае библиотеки - это фамилия. Поэтому индексы по полям-енумам вообще бессмысленны . Но иногда они нужны, чтобы оптимизатору "подсказать" взять нужный индекс.

Вот поэтому и нет индекса по этому полю. Выборок только по этому полю нет, а включение его в другие индексы возможно, ... но он будет где-нибудь в конце и его роль будет минимальна.

Цитата:

Сообщение от Kabardian

Стоит ли добавить индекс, если в таблице InventSettlement более чем 40 млн записей?
Честно потратил 10-15 минут на поиск, но вроде никто никогда не задавал таких вопросов.

Знаю, что можно удалить записи с Cancelled=NoYes::Yes, сейчас вопрос в том, имеет ли такая оптимизация право на жизнь и какой будет эффект.

Есть такой момент. Любое добавление / изменение индекса на таблице ведет к ее реиндексации. (DBCC DBREINDEX 'Table'). Если у Вас есть на чем тестировать - попробуйте выполнить запрос после реиндексации таблицы. После этого добавьте это поле в индекс и снова выполните запрос. Вы вряд ли заметите сколько-нибудь значимых изменений. Т.е. по сути - вопрос - сколько на одну запись Cancelled = NoYes::No у Вас соответствующих записей Cancelled = NoYes::Yes. Вот среди них и будет отбор. А это обычно малое количество записей (сколько раз перезакрывали склад за один и тот же период? Умножаем на 2)

Удаление записей конечно же даст эффект. Искать станет проще (среди меньшего количества записей). Закрытие склада ускорится во времени. Также рекомендую еще дефрагментировать табличку, а то "бегать" по разбросанному файлу на диске - тоже скорости не добавляет. А для дефрагментации нужно сделать кластерный индекс.
Я делал кластерный индекс по следующим полям (список полей соответствует порядку полей в индексе):
TransRecId,
TransDate,
Cancelled

[sukhanchik]

Цитата:

Сообщение от sukhanchik

Также, обращаю внимание - что в идеале - индекс нужен по всем полям, входящим в условие WHERE, а также в сортировку / группировку.

Маленькое добавление. Это утверждение не означает, что нужно кидаться и создавать индексы на все возможные выборки. Ибо оптимизатор может и "запутаться".
Скорее тут нужно в индекс добавлять наиболее селективные поля без енумов. А выборки стараться "причесывать" под существующие индексы. Иногда ничего не значащая сортировка в выборке может существенно повысить производительность выборки только из-за того, что оптимизатор возьмет другой индекс.

[gl00mie]

Цитата:

Сообщение от sukhanchik

Есть такой момент. Любое добавление / изменение индекса на таблице ведет к ее реиндексации. (DBCC DBREINDEX 'Table').

А еще каждый дополнительный индекс замедляет операции insert/update/delete.

Цитата:

Сообщение от sukhanchik

по сути - вопрос - сколько на одну запись Cancelled = NoYes::No у Вас соответствующих записей Cancelled = NoYes::Yes.

Кроме того, если запрос будет параметризированный, а не с литералами, оптимизатор в общем случае не будет использовать статистику по полю Cancelled, т.е. разница в количестве записей с Cancelled = NoYes::No и Cancelled = NoYes::Yes не повлияет на формирование плана запроса.

[sukhanchik]

Цитата:

Сообщение от gl00mie

А еще каждый дополнительный индекс замедляет операции insert/update/delete.

В общем случае, да. А кластерный индекс еще и данные сортирует при вставке. Но лучше смотреть на все с секундомером. И выбирать из двух зол меньшее.

Цитата:

Сообщение от gl00mie

Кроме того, если запрос будет параметризированный, а не с литералами, оптимизатор в общем случае не будет использовать статистику по полю Cancelled, т.е. разница в количестве записей с Cancelled = NoYes::No и Cancelled = NoYes::Yes не повлияет на формирование плана запроса.

Ну в АХ в общем случае все запросы параметризованы, а не с литералами (если конечно напрямую об этом не сказано в слове forceLiterals или не указано в параметрах АОСа)

[Kabardian]

Цитата:

Сообщение от sukhanchik

Для ответа на сей вопрос - нужно понять - что нам даст индекс по этому полю.
Теоретически, при сортировке по индексу (в порядке полей, указанных в индексе) - мы можем уменьшить количество записей, внутри которых осуществляется поиск.
Т.е. написав where cancelled = NoYes::No мы отсекаем кучу записей... Но и куча остается. Оставшееся количество все равно велико (нам индекс радикально не помог), а значит оптимизатор будет искать иные индексы для поиска.

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

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

Цитата:

Сообщение от sukhanchik

Маленькое добавление. Это утверждение не означает, что нужно кидаться и создавать индексы на все возможные выборки. Ибо оптимизатор может и "запутаться".

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

[sukhanchik]

Цитата:

Сообщение от Kabardian

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

А вот это как раз не страшно. Что на первом месте - производительность или размер БД? Да и железки под хранение БД не такие уж и дорогие.
Microsoft вон пошел в АХ 2012 по пути нормализации и кучи джойнов. Но это заведомо даст проигрыш в производительности на том же оборудовании.
Так что тут как раз переживать не стоит.

[Kabardian]

Цитата:

Сообщение от sukhanchik

А вот это как раз не страшно. Что на первом месте - производительность или размер БД?

Стыдно признать, но иногда при неудачном стечении обстоятельств размер БД становится на первое место. Например, мы работаем по франшизе, вся инсталляция AX на аутсорсинге и внезапно решили расторгнуть отношения с франчайзером. Согласование и выполнение работ по увеличению системных ресурсов (установка дополнительного AOS, увеличение места на жестком диске и т. д.) может растянуться на долгие месяцы, т. к. франчайзер ставит палки в колеса.

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

[Logger]

Ну если на первом месте объем БД, то проще вычистить из таблички отмененные записи и сделать reorganize. И места меньше займет и индекс не потребуется, так как индексировать нечего будет.

[Vadik]

Когда тюнил у себя Inventory value \ Aging отчеты, закончилось все тем что Cancelled было добавлено в большинство индексов (Cancelled = NoYes::No практически во всех запросах участвует). Более того, оно сейчас первым в кластерном индексе поставлено

[Logger]

Цитата:

Сообщение от Vadik

Более того, оно сейчас первым в кластерном индексе поставлено

А вот это сомнительное решение. Получается при отмене пересчетов/закрытий/коррекций, у вас ключ кластерного индекса меняется. Со всеми сопутствующими накладными расходами.

[Vadik]

Цитата:

Сообщение от Logger

А вот это сомнительное решение. Получается при отмене пересчетов/закрытий/коррекций, у вас ключ кластерного индекса меняется. Со всеми сопутствующими накладными расходами.

Я, чтобы сомнениями не мучаться, прогоняю с секундомером и SET STATISTICS IO ON основные операции (закрытие \ отмена \ отчеты ) на реплике рабочей БД ДО и ПОСЛЕ

[Logger]

И как результат ? Неужели нормально все ?

[Kabardian]

Интересное мнение насчет индексов было у fed.

Цитата:

Сообщение от fed

А это вообще не на аксаптовском уровне происходит. И даже не на уровне трейсинга SQL-операторов в SQL Profiler. Вот представь себе: У тебя есть таблица персонала. Есть куча индексов, один из которых по полю Пол. Ты говоришь - удалить сотрудника с employeeId=='Иванов И.И.'. Система находит по индексу emplIdIdx физический адрес записи и фетчит ее. Далее - надо удалить из всех индексов ключи, которые на эту запись ссылаются. Система рассчитывает значения индексных ключей (по данным из записи) и пытается найти и удалить все индексные ключи. Для этого она ПЕРЕБИРАЕТ все индексные ключи со значением равным вычисленному до тех пор, пока не наткнется на ключ, ссылающийся на нужную запись (то есть с сохраненным в индексном входе Row_Id==Row_ID нашей записи). Если индекс уникален, то этот перебор не требуется. Если относительно уникален (ну скажем - номер паспорта без серии) - то перебор будет недолгим. А вот если это индекс по полю типа Пол (два возможных значения) - перебор будет медленным и печальным. Собственно - по этому в книжках и не советуют строить индексы по полям с 2-5-10 возможными значениями - обновление такой индекс затормозит, а при выборке редко будет нужен. Но тем не менее - иногда приходится строить индексы по достаточно часто повторяющемуся полю. Даже если на каждое значение будет приходиться порядка 200-300 записей - все равно обновление тормозить будет изрядно.

Насколько я знаю - некоторые БД пытались решить эту проблему за счет того, что индекс подспудно сортировался по сочетанию ключ+физический адрес записи (ROW_ID). (То есть - значение ссылки на запись становилась некой виртуальной частью ключа). Однако - на практике это приводило к изрядным проблемам, поскольку приводило к усиленной перебалансировке дерева страниц при вставке новых записей. Кроме того - при реорганизации и упаковке таблиц, это усложняло перестроение индексов.
Так что - насколько я понимаю, в текущих версиях и SQL Server и Oracle используется именно такой подход к удалению ключей, который я описал...

[AndyD]

Цитата:

Сообщение от Kabardian

Интересное мнение насчет индексов было у fed.

Если почитать тему дальше, то видно, что это мнение, по крайней мере по отношению к MS SQL, было неправильное


2 Vadik
Если Cancelled входит в кластерный индекс, да еще на перевом месте (или на втором, после DataAreaId?), то имеет ли смысл добавлять его в другие индексы (на последнее место, правильно?)?

[Владимир Максимов]

Цитата:

Сообщение от sukhanchik

А вот это как раз не страшно. Что на первом месте - производительность или размер БД?

Закрытие склада обычно выполняется раз в месяц или еще реже. Если будет тормозить, то это печально, конечно, но раз в месяц можно и потерпеть. А вот насчет размера...

Цитата:

Сообщение от sukhanchik

Да и железки под хранение БД не такие уж и дорогие.

Мне бы Вашу уверенность. Если посчитать совокупные расходы, то железки вообще золотые оказываются. Кроме того, очень больша проблема убедить руководство что-то закупить из железок, особенно если проблему можно решить уменьшив размер данных.

Цитата:

Сообщение от sukhanchik

Microsoft вон пошел в АХ 2012 по пути нормализации и кучи джойнов. Но это заведомо даст проигрыш в производительности на том же оборудовании.

Это общая политика Microsoft. Если не ошибаюсь, где-то в статьях Джоэля Спольски вычитал чем они руководствуются. Дескать пока будешь оптимизировать код, железо разовьется настолько, что его производительность перекроет потери от не оптимального кода. Тогда какой смысл оптимизировать?

Цитата:

Сообщение от sukhanchik

Так что тут как раз переживать не стоит.

Еще как стоит. Я вот сейчас начал очередной цикл борьбы за свободное место на диске. Опять места не хватает (кое-что освободил, но этого хватит максимум на 1 год). А закупить - никто денег не дает

[sukhanchik]

Цитата:

Сообщение от Владимир Максимов

Закрытие склада обычно выполняется раз в месяц или еще реже. Если будет тормозить, то это печально, конечно, но раз в месяц можно и потерпеть. А вот насчет размера...
Мне бы Вашу уверенность. Если посчитать совокупные расходы, то железки вообще золотые оказываются. Кроме того, очень больша проблема убедить руководство что-то закупить из железок, особенно если проблему можно решить уменьшив размер данных.

Ну... все ж упирается в конкретное время. Смотря сколько занимает процесс закрытия. Если сутки или меньше - то без проблем. Если больше - то уже сложнее. Опять-таки - вопрос убеждения руководства. Докупить диски (массив) обычно все же дешевле выходит, нежели новый сервер.
Плюс еще момент. Объем данных, который дают индексы может быть на порядок меньше объема данных, который можно потереть без последствий для системы (InventSumTTSLog, *ParmTable, *ParmLine и т.д.). Поэтому тут надо все взвешивать.

Цитата:

Сообщение от Владимир Максимов

Это общая политика Microsoft. Если не ошибаюсь, где-то в статьях Джоэля Спольски вычитал чем они руководствуются. Дескать пока будешь оптимизировать код, железо разовьется настолько, что его производительность перекроет потери от не оптимального кода. Тогда какой смысл оптимизировать?

Идея логичная, только в случае АХ2012 речь идет о сознательной перестройке системы, а не о написании неоптимального кода.
А в целом - согласен, но с оговоркой. Какой бы высокопроизводительный сервер не был - все равно на нем можно сделать мертвую блокировку. А это означает, что идеала никогда не достичь - и все равно оптимизацию до определенного уровня нужно поддерживать. (Правило 80%/20%).
Опять-таки - начальство охотнее пойдет на апгрейд системы, если он потребует минимальных затрат (особенно на железки)

[Владимир Максимов]

Цитата:

Сообщение от sukhanchik

Ну... все ж упирается в конкретное время. Смотря сколько занимает процесс закрытия. Если сутки или меньше - то без проблем. Если больше - то уже сложнее.

Это, конечно... Вопрос приоритетов...

Цитата:

Сообщение от sukhanchik

Опять-таки - вопрос убеждения руководства. Докупить диски (массив) обычно все же дешевле выходит, нежели новый сервер.

Не все так просто как кажется

Диски лежат не сами по себе, а в стойке (полке). А стойки закупаются далеко не каждый год. А через пару лет найти диски нужной марки для конкретной стойки - уже проблема. "Антиквариат", понимаешь ли Значит, надо закупать уже и новые стойки к новым дискам или искать "антикварные" диски по очень дополнительной цене.

Это со стороны железа. Теперь смотрим со стороны администратора.

Кроме самой базы нужен еще BackUp. Затем нужна база для разработки. Еще одна база для тестирования. Идельный вариант, иметь еще одну копию базы. А еще может быть база для отчетов. А еще копии базы отчетов. А еще...

Вот и получается, что нужно докупать, минимум, в 5-кратном размере. Ну, т.е. если надо 100ГБ на рабочую базу, значит, придется раскошелится на 500ГБ для дополнительных копий базы.

В общем, хочешь получить "мелочь", но вокруг этой "мелочи" возникает масса дополнительных условий.

Цитата:

Сообщение от sukhanchik

Плюс еще момент. Объем данных, который дают индексы может быть на порядок меньше объема данных, который можно потереть без последствий для системы (InventSumTTSLog, *ParmTable, *ParmLine и т.д.). Поэтому тут надо все взвешивать.

Ну, в данном случае речь идет о вполне конкретной таблице InventSettlement. А у нее объем данных и объем индекса примерно равны.

Цитата:

Сообщение от sukhanchik

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

Это в теории. На практике так не получается. Суммарные (итоговые) затраты обычно очень велики. Но вот если удастся разбить затраты на части (этапы внедрения или этапы обновления), то вот в этом случае пробивать легче. Разовый платеж относительно небольшой, а что там по итогам года (или нескольких лет) - уже другой вопрос Проблема как раз в том и состоит, что процесс увеличение дискового пространства на части не делится. "Я бы взял частями, но мне нужно сразу" (с)

[Vadik]

Цитата:

Сообщение от AndyD

Если Cancelled входит в кластерный индекс, да еще на перевом месте (или на втором, после DataAreaId?), то имеет ли смысл добавлять его в другие индексы ?

Вопрос хороший, правильный, и я бы даже сказал - философский. Теоретически - нет. На практике - при прочих равных условиях оптимизатор стабильно выбирает для index seek индекс с явно входящим в него Cancelled. Его в принципе можно понять - все же приятнее когда значения (пусть даже их всего два) ключевого поля (Cancelled) явно отсортированы а не лежат где-то "рядом" наподобие included. Так что я добавляю..

Цитата:

на последнее место, правильно ?

Предпочитаю строить некластерные индексы "правильно" (в соответствии с запросами) без оглядки на наличие и структуру кластерного - меньше шансов "нарваться" при его смене. Так как Cancelled используется практически всегда, добавляю его обычно одним из первых полей