Вот рекомендации, разработанные мной для наших разработчиков и подходящие для OLTP баз.
По умолчанию рекомендуется устанавливать поле MaintainSQLIndex в значение No, изменяя это значение только после того, когда станет ясно, что необходим дополнительный индекс в СУБД для таблицы и какие именно поля необходимо в него включить. (Анализируется Profiler'ом)
Большинство таблиц (а соответственно и индексов) создавались на ранних версиях Navision Attain, и были рассчитаны на работу с собственной СУБД. Скорее всего, именно этот факт является причиной того, что в существующей версии Navision (3.60 – 4.00 SQL Option) большое количество индексов построено по принципам, не учитывающим специфику MS SQL.
- Короткий индекс лучше чем длинный
Перед выполнением любого запроса MS SQL строит план, согласно которому будет выполняться запрос. При построении плана сервер выбирает индексы, которые будут использованы и в зачастую использует в именно короткие индексы.
Короткие индексы, в отличие от длинных индексов:- * Легче в обслуживании, что увеличивает производительность операций записи.
* Универсальны, так как MS SQL не выполняет запросы «в лоб» а использует объединение нескольких подзапросов.
* Суммарное количество необходимых в таблице индексов уменьшается.
Оптимальными являются индексы, состоящие из одного или двух полей, разбивающие таблицу на примерно равные порции маленького размера.
Пример:
Таблица 17 - G/L Entry содержит 2 ключа
1. Agreement No.
2. Agreement No.,Posting Date,Source Type,G/L Account No.,Source No.
Второй индекс слишком длинный, нужно либо использовать его сокращенную форму, либо вовсе отключить. Для того чтобы определить, нужно ли отключать второй индекс, нужно определить, достаточно ли одного индекса «Agreement No.».
Для этого посмотрим, как разбивает поле «Agreement No.» таблицу «G/L Entry», выполнив запрос
Код:
select count(*),"Agreement_No_"
from "dbo"."КРОК$G_L_Entry"
group by "Agreement_No_"
- Первое поле индекса бьет, второе - добивает
При создании индекса из двух элементов, нужно выбирать поля таким образом, чтобы первое поле разбивало таблицу на порции средним объемом 0.1%-0.5% от общего размера таблицы, а оба поля разбивали таблицу на порции средним объемом в 1-100 записей. Например, таблица «Value Entry», ключ «Item No., Posting Date»
Типичным для Navision является наличие поля типа Option в качестве первого поля индекса. Например, индекс таблицы «Sales line» - «Document Type, Bill-to Customer No., Currency Code».
MS SQL при построении плана выполнения запроса старается выбирать индексы поиска таким образом, чтобы после фильтрации по первому полю индекса осталось как можно меньше записей. Индексы, имеющие первым полем Option или Boolean не эффективны – они разбивают таблицу на несколько больших порций, а не много маленьких.
Правда, существует исключение из этого правила, когда ключ с полем Boolean или Option разбивает таблицу крайне не равномерно и зачастую требуется получить меньшую часть этого разбиения. Например, ключ таблицы «Cust. Ledger Entry» - «Open, Due Date». Это полезный ключ, потому что часто возникает необходимость получить открытые операции и только маленькая часть всех операций будет открыта.
- Оптимизация под OLTP
Нужно иметь в виду, что каждый создаваемый индекс, особенно состоящий из большого количества полей, снижает производительность операций INSERT, MODIFY, DELETE производимых над таблицей. Зачастую бывает выгоднее отключить индекс, потеряв немного производительности в чтении, и получив прирост производительности операций записи.
Большинство таблиц Navision, связанных с хранением учетной информации, лучше оптимизировать под операции записи. Например, для таблиц «Item Ledger Entry», «Value Entry», «Cust. Ledger Entry» следует отключать от обслуживания более половины ключей. Их редкое использование не является аргументом в пользу потери производительности при учете документов.
Вот пример оптимизации некоторых ключей из таблицы Item Ledger Entry
ключ «Item No.», «Variant Code», «Drop Shipment», «Location Code», «Posting Date»
Этот ключ слишком длинный, для того чтобы делать из него индекс в MS SQL (правило 1). Определим, из каких полей должен состоять индекс MS SQL – для этого посмотрим, каким образом разбивается таблица по приведенным полям.
Выполнение следующего запроса выдаст разбиение по «Item No.»:
Код:
select count(*),"Item_No_"
from "dbo"."КРОК$item_Ledger_Entry"
group by "Item_No_"
Предположим, разбиение получается достаточно большим, однако по популярным товарам объем разбиения получается в районе 200 записей. Согласно правилу 2, нужно подобрать второе поле индекса, которое даст достаточное измельчение данного разбиения. Переберем возможные варианты:
Код:
select count(*),"Item_No_","Variant_Code"
from "dbo"."КРОК$item_Ledger_Entry"
group by "Item_No_","Variant_Code"
Код:
select count(*),"Item_No_","Drop_Shipment"
from "dbo"."КРОК$item_Ledger_Entry"
group by "Item_No_","Drop_Shipment"
Код:
select count(*),"Item_No_","Location_Code"
from "dbo"."КРОК$item_Ledger_Entry"
group by "Item_No_","Location_Code"
Код:
select count(*),"Item_No_","Posting_Date"
from "dbo"."КРОК$item_Ledger_Entry"
group by "Item_No_","Posting_Date"
Первые два варианта не подходят, поскольку измельчение не увеличивается достаточно сильно – среднее количество записей в разбиении по прежнему около 200.
Последние два варианта – разбиение по «Item No.»+«Location Code» и «Item No.»+«Posting Date» - удовлетворяют правилу 2. и могут быть использованы в качестве индекса.
Аналогично оптимизируются и остальные индексы таблицы «Item Ledger Entry». После подобных манипуляций, из 30 длинных ключей Navision обслуживаются лишь 10 коротких. Это существенно увеличивает скорость записи и модификации таблицы, что в свою очередь увеличивает скорость учетных операций.
Кроме того, при выборках в коде по этой таблице, не будет неоднозначностей по поводу того, какой именно индекс будет использовать MSSQL (главное не забывать НЕ указывать SETCURRENTKEY).