Свойство SQLIndex и производительность NAV

В некоторых случаях использование свойства SQLIndex в ключе таблицы Microsoft Dynamics NAV может негативно сказаться на быстродействии системы. В этой заметке я опишу, с чем надо быть осторожным, и почему использование этого свойства прекращено в NAV 5 SP1.

Данное свойство все еще доступно, и является ценным инструментом настройки производительности. Однако, начиная с SP1, в стандартном приложении оно больше не используется. В документе «Changes in NAV 5 0 SP1.doc» на продуктовом диске SP1 идет перечень из 72 таблицы, где «Свойство SQLIndex исключено» (Change A222).

Немного истории

Свойство ключа SQLIndex было впервые доступно в Navision 4 SP1. Идея его состояла в том, чтобы дать возможность изменить индекс SQL Server’а при сохранении порядка сортировок в NAV.

Основная цель задействования SQLIndex – сделать индексы SQL Server’а более селективными (что это такое, можно прочитать здесь). В NAV многие ключи начинаются с поля типа Option, что отрицательно влияет на селективность ключа.

К примеру, первичный ключ таблицы Sales Header предствляет собой комбинацию «Document Type», «No.»

У поля «Document Type» есть всего шесть различных вариантов значений – что означает плохую селективностью. Поэтому оптимизатор запросов SQL Server’а может отказаться от его использования. Если же переставить поля местами («No.”, “Document Type”), то селективность ключа возврастает, поиск по нему в операторах SELECT становится эффективнее.

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

Например, индекс по таблице «Sales Header», начинающийся с поля «No.» может быть эффективно использован с различными фильтрами, снижая таким образом потребность иметь отдельный ключ для каждого возможного фильтра.

Проблемы

Допустим, у вас есть запрос, который включает в себя директиву «ORDER BY”, т.е. SQL Server должен вернуть записи в определенном директивой порядке. Если SQL Server не находит подходящего индекса, ему приходится извлекать данные из базы, используя другой индекс, а затем запускать внутренний процесс сортировки полученных записей в нужном порядке. Если хороших индексов нет, SQL Server может задействовать кластерный индекс, который, в свою очередь, не всегда хорош (смотри пример с таблицей Sales Header). А в случаях, когда в запрос указан хинт серверу (index hint), ему приходится использовать указанный в хинте индекс, что приводит к чтению больших объемов данных.

Пример

Не так давно разбирали случай в поддержке, когда у клиента была плохая производительность работы системы в целом. Запрос «SELECT TOP 30» из заметки «…» показал, что среди 30 худших запросов, 26 были похожими на этот:

SELECT TOP 501 *

FROM "Reservation Entry" WITH (UPDLOCK, INDEX("$1"))  

WHERE (("Source ID"=@P1)) AND (("Source Ref_ No_"=@P2)) AND (("Source Type"=@P3))
AND (("Source Subtype"=@P4)) AND (("Source Batch Name"=@P5))
AND (("Source Prod_ Order Line"=@P6)) AND (("Reservation Status"=@P7))

ORDER BY "Source ID","Source Ref_ No_","Source Type","Source Subtype",
"Source Batch Name", "Source Prod_ Order Line", "Reservation Status", "Shipment Date",
"Expected Receipt Date","Entry No_","Positive"

Сам по себе запрос выглядит неплохо: условия WHERE и ORDER BY согласованы между собой, и не было никаких прямых причин, по которым этот запрос выполнял больше чтений из базы, чем было записей в таблице.

Но в стандартном приложении свойство SQLIndex для этого ключа выглядело так:

"Source ID","Entry No.",Positive

Здесь идея в том, чтобы получить индекс, который сможет отработать во многих случаях, и т.о. уменьшить количество индексов, которые нужно хранить на SQL Server’е. Данное свойство отлично подходит для SELECT-части запроса. Проблема заключается в том, что данный индекс нельзя использовать для ORDER BY-части запроса.

Что в итоге происходит

Возможно, SQL Server запланировал использование кластерного индекса для чтения записей с последующей внутренней сортировкой. Но index hint делает такой план невозможным. В итоге, SQL Server заставляют выполнять очень трудное задание с ограничением – индексом указанным в хинте.

Что мы сделали? Убрали свойство SQL Index из ключа, и производительность скакнула вверх.

Выводы

Когда перед вами запрос, вызывающий много операций чтения из базы, то, даже если с точки зрения SQL Server’а индекс идеален, проверьте, чтобы он был идеален для ORDER BY-части запроса. ORDER BY-часть запроса зависит от ключей NAV. Если для этого ключа задано свойство SQLIndex, то они не будут подходить друг другу.

Вам следует по-прежнему использовать свойство SQLIndex для настройки производительности, но не увлекайтесь, знайте, что это можно вызвать проблемы, описанные выше.

Оригинал статьи доступен по адресу:  http://blogs.msdn.com/b/microsoft_dynamics_nav_sustained_engineering/archive/2008/04/21/sqlindex-property.aspx

Автор:

В области Navision - с 2003 года. Профессиональные интересы: NAV, MS SQL, .NET, BPMN, IT-менеджмент. Предметная область: логистика, финансы, склады, 3PL.

Количество статей, опубликованных автором: 86.

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