Оптимизация: Шаг за шагом. Часть 2

10.06.2013 | Автор: | Рубрика: Nightmare!, SQL, Troubleshooting

Оптимальная индексная модель

Давайте посмотрим на индексы, которые создает Navision. Пример одного из индексов для строк документов:

CREATE UNIQUE NONCLUSTERED INDEX [$10] ON [dbo].[Фирма$Документ Строка]
(
[Тип Операции] ASC,
[Документ Но_] ASC,
[Код Склада] ASC,
[МОЛ] ASC,
[Единица Измерения] ASC,
[Код Производителя] ASC,
[Код Группы] ASC,
[Кол-во в Единице Измерения] ASC,
[Товар Но_] ASC,
[Журнал Код Шаблона] ASC,
[Журнал Код Раздела] ASC,
[Строка Но_] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA Filegroup 1]

Это типичный индекс для строк документов. Посмотрим на его недостатки:

  • Индекс уникальный – это значит, что при занесении данных SQL всегда проверит значение на уникальность, как в первичном кластерном индексе, так и во всех некластерных индексах. Зачем это нужно? Ведь меня интересует уникальность только в первичном ключе. Лишние циклы при изменении данных.
  • Начинается индекс с поля [Тип Операции] которое является одним из самых низко селективных полей.
  • В индекс для обеспечения уникальности добавлено значение первичного ключа (Журнал Код Шаблона, Журнал Код Раздела, Строка Но_), что увеличивает количество ветвей у индекса.
  • Очень часто в индексах можно встретить поля с типом Boolean, что только уменьшает вероятность его использования SQL сервером.
  • Очень часто в индексе можно встретить поля, которые не используются в вашей модели данных ([Кол-во в Единице Измерения],[Лот Но_]), но их нельзя выкинуть, т.к. в каком-то кодеюните этот ключ указан полностью и при его отключении перестает работать учет.

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

Теперь давайте посмотрим, что нужно учитывать при моделировании индекса:

  • Индекс должен начинаться с самых селективных полей
  • Оптимальное количество полей в ветвях индекса 2-3
  • В индексе можно использовать включаемые поля, которые не увеличивают количество ветвей у индекса. Кстати SQL хранит значения первичного ключа в не кластерных индексах именно во включаемых полях.
  • Индекс и сортировка данных в запросах это разные понятия. Т.е. SQL сначала отбирает данные по индексу, а затем отдельно сортирует получаемый результат. Это в свою очередь означает, что индексы не должны соответствовать сортировке которую использует Navision.
  • Начиная с 2008 SQL можно создавать фильтруемые индексы. Например, если нам нужен индекс по полю «Источник Но» в товарных операциях, где это поле заполнено только в операциях покупки или продажи, можно создать индекс по этому полю, добавив выражение фильтрации «Источник Но»<>» сократив размеры индекса и ускоряя получение данных из него.

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

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

Поле\Поле [Товар Но_] [Дата Учета] [Код Склада] [Документ Но_] [Источник Но_] [Тип Операции] [Код Проекта]
[Товар Но_]

359 873

17 714 113

5 618 889

57 320 890

6 539 638

1 489 252

359 874

[Дата Учета]  

1 285

85 393

2 075 336

486 477

8 448

7 839

[Код Склада]    

108

2 581 760

25 053

488

582

[Документ Но_]      

2 075 327

2 100 423

2 109 310

2 075 341

[Источник Но_]        

11 838

15 846

20 270

[Тип Операции]        

8

62

[Код Проекта]            

9

Для получения данных используем следующий запрос:

SELECT COUNT(*)
FROM (
SELECT DISTINCT [Поле 1], [Поле 2]
FROM [dbo].[Фирма$Таблица]
) AS t

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

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

Из таблицы видно, что самыми селективными являются поля «Товар Но», «Документ Но.» Они по идее и должны быть первыми в индексах. Поле «Источник Но» может тоже быть использовано но при этом желательно использовать фильтр «Источник Но»<>».

Теперь создадим первый индекс. По опыту своей работы я понимаю, что комбинация Товар Но, Документ Но не нужна. Обычно по Документ Но_ запрашивают сумму продаж или сумму себестоимости и почти никогда не указывают товар. Поэтому возьмем поле для индекса Поля Товар Но, Дата Учета как самые селективные.

CREATE NONCLUSTERED INDEX [idx_TovDU]
ON [dbo].[Фирма$Товар Книга Операций]

(
[Товар Но_] ASC,
[Дата Учета] ASC

) WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [DATA Filegroup 1]

Можем создать индекс и посмотреть, как выполняются запросы, используя оборотную ведомость по товару. Все не так хорошо как звучит в теории. Особенно тяжело становиться, когда мы ставим фильтры на поля не указанные в индексе, например Код Склада или Код Проекта. Проанализировав становиться понятно, что не хватает этих полей в индексе. Добавим эти поля не в поля индекса, а в список включаемых полей индекса и добавим ту да все поля, которые нужны для расчета shift-полей.

CREATE NONCLUSTERED INDEX [idx_TovDU]
ON [dbo].[Фирма$Товар Книга Операций]

(
[Товар Но_] ASC,
[Дата Учета] ASC
)

INCLUDE (

--дополнительные поля фильтрации
[Код Склада],
[Код Филиала],
[Тип Операции],
[Код Проекта],
[Положительна],

--поля для расчета shift полей
[Кол-во],
[Сумма],
[Корр_ Себест_ Счет],
[Выст_ Счет Кол-во],
[Остаток Кол-во])

WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
) ON [DATA Filegroup 1]

Перестраиваем и видим, что все стало гораздо лучше. Что же мы получили. Это индекс который будет покрывать все запросы для получения данных в которых будет использована любая комбинация полей [Товар Но_],[Дата Учета] и в любом прядке и комбинации полей [Код Склада],[Код Филиала],[Тип Операции],[Код Проекта],[Положительна] + [Кол-во],[Сумма],[Корр_ Себест_ Счет],[Выст_ Счет Кол-во],[Остаток Кол-во]. Например, это замена ключей:

— [Код Склада] ,[Код Проекта],[Положительна], [Товар Но_],[Дата Учета]

— [Код Товара],[ Код Склада],[ Код Проекта]

И т.д. Этот индекс быстрее и эффективнее стандартных индексов Navision. Кроме того в отличие от ключей, которые используют новые версии Navision для расчета shift полей, он может быть использован и для других задач. Еще нам удалось выкинуть те поля, которые не используются, из индексов, без ущерба для функционирования алгоритмов Navision

Индекс для Документ Но_ выглядит так:

CREATE NONCLUSTERED INDEX [idx_DocNo]
ON [dbo].[Фирма$Товар Книга Операций]

(
[Документ Но_] ASC,
[Строка Но_] ASC
)

INCLUDE (
  --дополнительные поля фильтрации
  [Код Склада],
  [Тип Операции],
  [Положительна],
  [Дата Учета],
  --поля для расчета shift полей
  [Кол-во],
  [Сумма],
  [Корр_ Себест_ Счет],
  [Выст_ Счет Кол-во],
  [Остаток Кол-во]
  )
WITH (
  PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
  DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
) ON [DATA Filegroup 1]

В этом индексе можно Строка Но_, заменить на Источник Но_. Это зависит только от Вашей модели данных.

Индекс для Источник Но_ выглядит так:

CREATE NONCLUSTERED INDEX [idx_IstochnTov]
ON [dbo].[Фирма$Товар Книга Операций]
(
[Источник Но_] ASC,
[Товар Но_] ASC
)
WHERE ([Источник Но_]<>'')

WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
) ON [DATA Filegroup 1]

Общее правило: Дополнительные поля фильтрации добавляются только при необходимости. Не стоит добавлять все поля, по которым может быть осуществлён отбор, т.к. очень часто специфика используемой модели данных не требует всех полей. Точно так же и поля необходимые для расчета shift полей. Только по необходимости.

Теперь можно отключить стандартные ключи Navision, кроме первичных, и использовать наши новые ключи. Делаем это, выключая свойство MantainSQLIndex у всех индексов оптимизируемой таблицы. Не спешите потирать ручки. В течение недели после начала эксплуатации будет выявлено, что не все было учтено во время проектирования и тестирования, и необходимо добавить новые ключи или слегка подправить уже созданные. Это нормально и нужно быть к этому готовым. Зато по идее у Вас сократиться количество индексов, и Вы покроете большее количество запросов. Бывает такое, что новые индексы не дают резкого прироста производительности в каком либо алгоритме. Если это не ошибка проектирования, то тогда нужно вернуть какой либо из старых индексов Navision, при котором этот алгоритм работал лучше.

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

Все, можно расслабиться и отдыхать. И вот опять звонок. Прошла массовая инвентаризация складов. В короткий срок нужно учесть 100 актов инвентаризации. Расчет одного акта инвентаризации занимает 30-40 минут. В чем дело? Ведь быстрее получение остатков уже не может быть. Начинаем разбираться и что оказывается: разработчики, создавая алгоритм расчета инвентаризации, предполагали, что у нас будет тысяча товаров на складе, а не 10 000. Они не обратили внимание, что с течением времени одни товары добавляются, а другие уходят. Справочник товаров постоянно растет и простой перебор остатков товаров по справочнику товаров из 400000 позиций — это очень долго.

Пришло время шестого шага оптимизации: оптимизация алгоритмов получения данных в Navision.

Алгоритмы

До этого момента все наши шаги были направлены на то, что бы оптимизировать систему минимально изменяя ее содержимое. Это путь администратора БД. Теперь пришло время, взять на вооружение программные методы оптимизации. Рассмотрим несколько важных алгоритмов.

Изменение данных в большой таблице

Об этом не раз уже говорилось , в том числе и на сайте http://naviart.ru/.

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

ФинКнигаОпераций.SETFILTER("Документ Но", 'док001');

IF ФинКнигаОпераций.Findset THEN
REPEAT
IF ФинКнигаОпераций."Код Отдела"<> НовКодОтдела THEN BEGIN
ФинКнигаОпераций."Код Отдела" := НовКодОтдела;
ФинКнигаОпераций.MODIFY;
END;
UNTIL ФинКнигаОпераций.NEXT = 0;

COMMIT;

Такой алгоритм очень часто приводит к сканированию всей таблицы, даже если все индексы построены правильно и к длительной блокировке. Верное решение следующее. Сначала находим все записи для изменения и сохраняем первичные ключи во временной таблице. Затем используя Get, переходим к конкретной записи и изменяем ее. При этом блокировки возникнут только на изменяемых записях и не затронут всю таблицу. Ключевая проблема в том, что findset без модификации данных отправит в SQL сервер совершенно другой запрос, чем findset с последующей модификацией данных.

Получение остатков

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

Товар.RESET;
Товар.SETFILTER(«Код производителя», '111');
IF Товар.FINDSET THEN
  REPEAT
    Склад.RESET;
    IF Склад.FINDSET THEN
      REPEAT
        Товар.SETRANGE(«склад фильтр», Склад.Код);
        Товар.CALCFIELDS(Остаток);
        IF Товар.Остаток <> 0 THEN BEGIN
          …
        END;

      UNTIL Склад.NEXT = 0;

  UNTIL Товар.NEXT = 0;

Алгоритм суровый и простой. В лоб. Очень любим разработчиками, которые не ждут результата по 40 минут, а тестируют на БД из 10 записей. Ведь, даже если всего 300 товаров и 100 складов мы получаем 30 000 запросов для определения остатков. Даже если система за 1 секунду сделает 100 запросов нам нужно ждать 5 минут. А если товаров больше?

Необходимо использовать возможности SQL запросов:

SELECT [Товар Но_], [Код Склада], SUM([Кол-во]) AS [Остаток]
  FROM [Фирма$Товар Книга Операций]
  WHERE [Код Производителя] = '111'
  GROUP BY [Товар Но_], [Код Склада]
  HAVING SUM([Кол-во]) <> 0

Такой запрос будет выполнен за 2-3 секунды. Причем чем больший объем данных нужно обработать, тем эффективней запрос в SQL. Например, полный запрос всех остатков товаров в разрезе всех складов компании длиться 20-25 минут в SQL и 6-7 часов в Navision.

Расчеты в SQL

Одной из последних моих разработок было получение деффектур магазинов. Это сравнение эталонных остатков магазина с текущими остатками и построение на этой разнице следующих заказов. Расчет деффектуры в Navision занимал 15 минут в три этапа. Деффектуры нужно делать каждый день для 40 магазинов утром, с тем, чтобы разместить заказы и отгрузить товар в течении этого дня. Т.е. системе нужно 10 часов на проведение расчетов. Результат оказался неприемлемым. Тогда весь алгоритм расчета был перенесен в SQL. Время выполнения от 20 секунд до 5 минут для одного магазина. Navision используется для отображения результата и запуска функций.

Оптимизация алгоритмов обработки данных эффективный способ решения большого количества задач. Но рано или поздно вы столкнетесь с тем, что источником проблем часто являются стандартные механизмы Navision. Сама идеология заложенная разработчиками Navision направлена на то, что он должен занимать определенную нишу и не конкурировать с другими решениями, например Axapta. Конечно, можно решить что, нужно внедрять новую ERP и ничего не делать в Navision. Однако это не всегда экономически оправдано, да и сроки внедрения очень большие, и не всегда внедрение может оказаться успешным. Оценивая возможные риски можно попытаться обойти существующие ограничения и приступить к восьмому шагу оптимизации: архитектурной оптимизации Navision.

Архитектурная оптимизация Navision

Это самый сложный и противоречивый шаг оптимизации. Ведь пытаясь осуществить этот шаг необходимо менять свою философию и навыки, приобретенные на протяжении изучения и эксплуатации Navision. Если Вы пришли к пониманию обоснованности этого шага, необходимо понимать: не существует правил и ограничений, есть только сложность реализации и тестирования. С моей точки зрения этот шаг оптимизации должен затрагивать учет документов (т.к. он очень медленный и в один момент можно учитывать только один документ), пересчет себестоимости.

В качестве примера архитектурной оптимизации приведу алгоритм расчета остатков.

Остатки

Я провел ABC анализ по количеству операций в двух больших таблицах: товарных и финансовых операций. В таблице товарных операций порядка 3500 товаров из 400000 закрывают 50% всех операций. В финансовых операциях 9 счетов из 320 закрывают 50% операций. Эти товары и финансовые счета наиболее востребованы пользователями. По ним чаще всего осуществляется просчет остатков и, конечно же, это самые долгие и дорогие для системы просчеты. При существующей методике получения остатков, Navision выполнит запрос, который будет просчитывать операции от начала до нужного периода анализа, с использованием shift ключей. Как правило, необходимы остатки на сегодняшний день и это значит, что в кэше SQL сервера будут всегда присутствовать все операции с начала базы, необходимые для просчета остатков по самым востребованным элементам (товарам, фин. Счетам, клиентам и т.д). При нехватке оперативной памяти это будет приводить к постоянным дополнительным считываниям данных и, в любом случае, к просчету больших объемов данных.

Что можно сделать? Изменение остатков происходит только в текущем открытом периоде. Как правило, это текущий месяц. Остатки за предыдущий период уже не должны изменяться. Так почему же не просчитать остатки, по необходимым разрезам и периодам, а не просчитывать тотально все операции по этим товарам. Таким образом, остаток будет рассчитываться как сумма предварительно рассчитанных остатков прошлого периода и движений от этого периода до нужной даты. Реализация данной схемы не является сложной. Просчитывать остатки нужно не для всех товаров, а только для тех, которые входят в число наиболее востребованных и ресурсоемких. Прежде чем создавать алгоритм необходимо создать три таблицы:

  • Таблица товаров, для которых будет проводиться срез остатков. Например, те, у которых больше 10 000 операций в товарных операциях.
  • Таблица с датами срезов остатков. Например, последний день каждого квартала.
  • Таблица остатков. В этой таблице будут остатки товаров на дату среза по всем складам.

После заполнения таблиц можно создать функцию расчета остатков. Функция добавлена в справочник товаров, поэтому в качестве параметров нужна только дата получения остатков, остальные фильтры применены к основной таблице и доступны из функции через REC.

ПолучитьОстаток(Дата : Date;VAR ОстатокКолво : Decimal)
{
ТоварыСрезаОстатков – таблица с кодами товаров
ДатыСрезаОстатков – таблица с датами среза
Остатки – таблица с остатками товаров
ТоварОборот – таблица товаров со стандартным полем расчета оборота товара
}

ОстатокКолво:=0;
СтандартныйРасчет:=FALSE;
IF Дата>010110D THEN //первая операция в БД появилась 020110D, остатки можно не считать
BEGIN
  ТоварыСрезаОстатков.RESET;
  ТоварыСрезаОстатков.SETRANGE("Товар Но.","Но.");
  IF ТоварыСрезаОстатков.FINDFIRST THEN
  BEGIN
    ДатыСрезаОстатков.RESET;
    ДатыСрезаОстатков.SETRANGE(Дата,0D,Дата);
    IF ДатыСрезаОстатков.FINDLAST THEN
    BEGIN
      Остатки.RESET;
      Остатки.SETRANGE("Товар Но.","Но.");
      Остатки.SETRANGE(Дата,ДатыСрезаОстатков.Дата);
      Остатки.SETFILTER("Код Склада","Склад Фильтр");
      IF Остатки.FINDSET THEN
      REPEAT
        ОстатокКолво:=ОстатокКолво+Остатки.Количество;
      UNTIL Остатки.NEXT=0;
      ТоварОборот.RESET;
      ТоварОборот.GET("Но.");
      ТоварОборот.COPYFILTERS(Rec);
      ТоварОборот.SETRANGE("Дата Фильтр",ДатыСрезаОстатков.Дата+1,Дата);
      ТоварОборот.CALCFIELDS(Оборот);
      ОстатокКолво:=ОстатокКолво+ТоварОборот.Оборот;
    END
    ELSE СтандартныйРасчет:=TRUE;
  END
  ELSE СтандартныйРасчет:=TRUE;
  IF СтандартныйРасчет THEN
  BEGIN
    ТоварОборот.RESET;
    ТоварОборот.GET("Но.");
    ТоварОборот.COPYFILTERS(Rec);
    ТоварОборот.SETRANGE("Дата Фильтр",0D,Дата);
    ТоварОборот.CALCFIELDS(Оборот);
    ОстатокКолво:=ТоварОборот.Оборот;
  END;
END;

Пример вызова в оборотной ведомости по товару

OnAfterGetRecord()
ПолучитьОстаток(GETRANGEMIN("Дата Фильтр") - 1,НачКоличество);
ПолучитьОстаток(GETRANGEMAX("Дата Фильтр"), КонКоличество);
ОборотКоличество:=КонКоличество-НачКоличество;
  • По всем товарам, которые будут включены в оптимизацию, обязательно нужно просчитать остаток на каждую дату среза. И если остаток равен 0, то записать его в таблицу остатков.
  • Цикл по суммированию остатков можно заменить FlowFiled полем в справочнике товаров. Просчет будет вестись более эффективно.
  • Таблицы ТоварыСрезаОстатков и ДатыСрезаОстатков можно объявить временными и глобальными в справочнике товаров и заполнять их единожды при создании объекта. Это избавит нас от запросов к БД для определения необходимости поиска среза остатков. Но нужно учитывать объем этих таблиц.
  • Одновременно с расчетом количества можно просчитывать и себестоимость остатков. Изменения будут небольшими.
  • Эффективность алгоритма напрямую зависит от количества дат срезов остатков и количества товаров. Нет никакой необходимости готовить срезы для товаров с количеством операций до 1000, или делать ежемесячные срезы. Если просчитать остатки для всех товаров Вы можете получить обратную оптимизацию J
  • При использовании данной схемы расчета остатков, оказываются, не востребованными порядка 30 млн. товарных операций, которые перестают постоянно подгружаться в оперативную память. Количество срезов остатков 500000 вместо 30 млн.

    Итоги

    На данный момент в этой статье представлен весь мой опыт по оптимизации Navision. Я расслабился и отдыхаю. Но чувствую: грядет новый шаг оптимизации…

    Автор:

    Количество статей, опубликованных автором: 5. Дополнительная информация об авторе появится вскоре.

    Комментарии (Один комментарий)

    1. laptei

      Спасибо за статью.
      ждем продолжения статей по оптимизации.
      Хотелось бы узнать Ваше виденье оптимизации спустя несколько лет.

      С уважением,
      Иван

    Добавить комментарий