Navision Performace Tips

[Eduard-NL]

1. Удали ключи, которые используются исключительно для сортировки.

2. Помещай fields Bool, Option и Date в конце ключа.

3. Не делай поддержку SIFT в маленьких и временных (Temporary) таблицах.

4. При организации циклов вводи новую переменную для изменения переменной, на которой построен цикл.

5. Не используй FIND(-)/FIND(+), если можно использовать FINDFIRST/FINDLAST.

6. Чем меньше количество ключей в таблице, тем лучше работает SQL Server: не надо хранить в памяти состав ключей.

7. Быбирай всегда ключ с наивысшей селективностью. В противном случае есть вероятность того, что SQL Server перейдет на сканирование таблицы (Clustered Index Scan), либо выберет неоптимальный ключ.

8. Не применяй FlowFields в повседневных, часто используемых формах, тем более в табличных формах. Применяй принцип Display on Demand.

9. В формах, отображающих большие таблицы, не оствавляй свойство SourceTablePlacement по умолчанию (Saved), а устанавливай (First/Last).

Буду признателен всем, кто протестирует мой Tool на сайте http://www.mibuso.com/dlinfo.asp?FileID=755 и пришлет свои замечания.

[konrad_imported]

Tool очень хороший. Огромное спасибо за его разработку.
Пользую практически с момента выхода.
Лишний раз убедился, что большинство тяжелых таблиц у меня имеют оптимальные (или близкие к оптимальным) ключи. А меньшинство было отловлено с помошью тула и приведено в порядок.
Но один сильно навороченный отчет проглотить эта штаку не смогла. В том отчете количество CalcFields составляло 18 полей, и по видимому - тут он и сдох.
Также прискорбно, что Ref-структуры не отслеживаются.

[e-statik]

Цитата:

4. При организации циклов вводи новую переменную для изменения переменной, на которой построен цикл.

Вот это что-то не понял (или то, что понял, смутило).

[Eduard-NL]

Действительно, я выразился не удачно.
Представьте себе цикл REPEAT - UNTIL построен на переменной Customer типа Record, т.е.:

IF Customer.FIND('-') THEN
REPEAT
.........

UNTIL Customer.NEXT=0;

Если вам нужно внутри цикла произвести изменения в таблице Customer, то используйте для этого новую переменную CustomerNew, а не ту, на которой построен цикл.

[Wizard_imported]

зачем?

[konrad_imported]

Цитата:

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

зачем?

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

[Wizard_imported]

вообще - ко всем советам есть претензии.
перечитал. точно ко всем.
4й совет, если действительно konrad понял правильно, надо читать как "не изменяй поля, входящие в текущий ключ". ("а если надо - заводи отдельную переменную")
ну, наверное для новичка может быть неочевидным.

[Kirvisniemi]

Цитата:

1. Удали ключи, которые используются исключительно для сортировки

А как же тогда пользователь будет сортировать?

Цитата:

2. Помещай fields Bool, Option и Date в конце ключа.

Option не обязательно, поскольку это обычное целочисленное поле

Цитата:

5. Не используй FIND(-)/FIND(+), если можно использовать FINDFIRST/FINDLAST.

FIND('-') генерирует обычный SELECT, FINDFIRST - SELECT * COUNT(1), поэтому FINDFIRST имеет смысл использовать когда нужна только первая запись. В общем случае лучше пользоваться FINDSET. При этом нужно держать в уме, что c использованием этих команд по большому счету не выигрывается ничего, но зато теряется совместимость с версиями <=3.60

Цитата:

6. Чем меньше количество ключей в таблице, тем лучше работает SQL Server: не надо хранить в памяти состав ключей.

Не совсем верно. Если в навижн происходит, например, фильтрация по полю не входящему в ключ, SQL вынужден перебирать последовательно все записи таблицы, чтобы выполнить эту операцию. При наличии ключа (по сути индекса), выборка работает быстрее на несколько порядков. Во-вторых, если SQL Server хранит состав ключей в памяти это очень хорошо, т.к. ему не нужно обращаться к диску (а обращение к диску это самая медленная операция SQL).
Другое дело, если происходит работа с SIFT - в этом случае действительно лучшие отключить рассчет сумм для ненужных комбинаций составного ключа (SIFTLevelsTomaintain)

Цитата:

8. Не применяй FlowFields в повседневных, часто используемых формах, тем более в табличных формах. Применяй принцип Display on Demand.

Собственно почему не применяй? Только из-за вероятности, что пользователь поставит фильтр на вычисляемое поле?

[Wizard_imported]

дополняю tyrexa, раз уж начал..

Цитата:

3. Не делай поддержку SIFT в маленьких и временных (Temporary) таблицах.

Вообще ни о чем. Что такое маленькая таблица? при чем тут временная? возникают сомнения в понимании автором SIFT технологии.

Цитата:

4

Сказал уже

Цитата:

7. Быбирай всегда ключ с наивысшей селективностью. В противном случае есть вероятность того, что SQL Server перейдет на сканирование таблицы (Clustered Index Scan), либо выберет неоптимальный ключ.

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

Цитата:

9. В формах, отображающих большие таблицы, не оствавляй свойство SourceTablePlacement по умолчанию (Saved), а устанавливай (First/Last).

смысл? В паре такого рода форм мне пришлось добавить сброс сортировки на первичный ключ при открытии (запоминание фильтров хотелось оставить). Но и это - не совет.

[Eduard-NL]

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

1. По поводу ключей, используемых исключительно для сортировки.

Цитата:

А как же тогда пользователь будет сортировать?

При работе с SQL Server эти ключи не нужны. SQL производит сортировку без них. Здесь и далее приведу выдержки из SQL Server Resource Kit (см. раздел Minimizing the Impact on SQL Server):

Eliminate the maintenance of indexes that are only designed for sorting purposes. SQL Server will sort the result set without these indexes.

2. По поводу Option Field

Цитата:

Option не обязательно, поскольку это обычное целочисленное поле

Это ограниченный набор целочисленных значений. Помещая Option Field в начале ключа, вы снижаете его селективность. В частности при количестве опций, равном двум, результат будет такой же как в случае Boolean. Предпочтительнее выдвигать вперед Code Field или Integer, если есть такая возможность.

Redesign indexes so that their selectivity becomes higher. Remember to not place Boolean and option fields at the beginning of an index and always put date fields towards the end of the index.
Furthermore, indexes like Posting Date,Customer No.,… must be avoided because the index is like an entire book and the chances that the query optimizer would pick this index are quite small.
Indexes like Document Type,Customer No.,… have very low selectivity on the first key. You could create a new index Customer No.,Document Type, … and maintain it on SQL Server while you turn off the maintenance of the original index on SQL Server.

3.По поводу Не используй FIND(-)/FIND(+), если можно использовать FINDFIRST/FINDLAST.

Цитата:

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

Функции FIND(-)/FIND(+) работают прекрасно с Navision Server, поскольку он использует принцип индивидуального чтения записей ISAM (Indexed Sequential Access Method).
SQL Server пытается определить происходит ли чтение записей в цикле или это всего лишь запрос на единичную запись. Здесь часто возникают проблемы, особенно в случае применения конструкции WHILE FIND('-') вместо REPEAT UNTILL NEXT. Поэтому SQL Server склонен к Set Based Queries и посылать ответ в виде Recordset вместо Single record . Таким образом использование этих функций может быть крайне неэффективным и зачастую снижает Performance. Поэтому начиная с версии 4.00 стали доступны функции FINDFIRST/FINDLAST/FINDSET. Теперь вы можете сделать оптимальный выбор в зависимости от задачи. Вряд ли кто либо воспользуется FIND(+) в известной конструкции:
IF LedgEntry.FIND('+') THEN
NextEntryNo := LedgEntry."Entry No." + 1
ELSE
NextEntryNo := 1;
если гораздо эффективнее FINDLAST.

Что касается преемственности версий, то это совершенно другой вопрос. Microsoft Business Solutions-Navision использует эти функции только в новых, а не модернизируемых обьектах.

4. По поводу Не делай поддержку SIFT в маленьких и временных (Temporary) таблицах.

Цитата:

Вообще ни о чем. Что такое маленькая таблица? при чем тут временная?

Здесь мне кажется все настолько очевидно, что лучше не делать подробного анализа:

Examples of temporary tables are Sales Line, Purchase Line, Warehouse Activity Line, and similar tables. SQL Server can retrieve sums directly from the ‘source’ table when the SIFT indexes are not maintained. If the data set within the filter is small, the sum is calculated quickly and at the same time all the updates of the ‘source’ table are performed more quickly because Navision does not have to update all the associated SIFT tables.

5. По поводу количества ключей в таблице.
Additionally, if you reduce the number of indexes, SQL Server will not have to keep these in memory, thereby increasing page life expectancy and improving performance. Maintaining fewer indexes can mean better performance.

SQL Server Query Optimizer вырабатывает решения на основе накапливаемого опыта запросов. Кстати, он может применить сканирование таблицы даже в случае идеального ключа, если сами записи сильно асимметричны. Не надо "смущать" Query Optimizer большим количеством ключей с одной и той же комбинацией полей.
Необходимо также учитыватьтот фактор что, чем больше ключей, тем больше потери времени на обновление SQL индексов при изменении записей.

6. По поводу Не применяй FlowFields в повседневных, часто используемых формах, тем более в табличных формах..

Цитата:

Собственно почему не применяй? Только из-за вероятности, что пользователь поставит фильтр на вычисляемое поле?

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

7. По поводу

Цитата:

возникают сомнения в понимании автором SIFT технологии

Вот здесь вы правы. У меня точно такие же сомнения. Вот уже семь лет пытаюсь понять, чего больше: вреда или пользы от применения FlowFields в комбинации с SQL Server. Пока безуспешно.

На сегодня достаточно. Пора спать.

[konrad_imported]

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

[Kirvisniemi]

Цитата:

1. По поводу ключей, используемых исключительно для сортировки.
А как же тогда пользователь будет сортировать?
При работе с SQL Server эти ключи не нужны.

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

Цитата:

2. По поводу Option Field
Option не обязательно, поскольку это обычное целочисленное поле
Предпочтительнее выдвигать вперед Code Field или Integer, если есть такая возможность.

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

Цитата:

3.По поводу Не используй FIND(-)/FIND(+), если можно использовать FINDFIRST/FINDLAST.

Никто и не спорит с тем, что раз появились новые полезные команды, лучше использовать их. Тут дело в том, что разница между FIND('-') и FINDFIRST заметна только в случае если эти команды крутятся внутри цикла с большим количеством итераций на сложной таблице. А если используется классический IF Table.FIND('-') THEN ..., то разницы между новыми и старыми командами не будет никакой

Цитата:

5. По поводу количества ключей в таблице.
Additionally, if you reduce the number of indexes, SQL Server will not have to keep these in memory, thereby increasing page life expectancy and improving performance. Maintaining fewer indexes can mean better performance.

В совете было написано "Чем меньше количество ключей в таблице, тем лучше работает SQL Server". Это не всегда так. Вообще к таким вещам как создание ключей надо подходить c компромиссом между потерей времени на построение индексов и быстродействием функционала в навижн.

Цитата:

6. По поводу Не применяй FlowFields в повседневных, часто используемых формах, тем более в табличных формах..
Собственно почему не применяй? Только из-за вероятности, что пользователь поставит фильтр на вычисляемое поле?
Потому что, это вычисляемые поля. При каждом переходе от одной записи к другой, вы запрашиваете результат вычисления, который вам может быть и не нужен.

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

[konrad_imported]

По первому пункту я бы сформулировал требование так:
Не ставьте галку MaintainSQLIndex на ключах, используемых только для сортировки. Разумеется, это не относится к первичному ключу

По третьему пункту добавил бы следующее:
Не ставьте галку MaintainSIFTIndex на любых ключах в таблицах, нормальное состояние которых (таблиц) - пустое. Это таблицы 81, 14820, 14825 и т.д - журналы, буферы и т.д.

[Eduard-NL]

Согласен

[rmv]

Я бы добавил пункт 10:
По возможности не допускай множественного обновления значений SumIndexFields в пределах одной транзакции. Проведи расчеты используя временные таблицы и после этого один раз обнови данные на сервере.

[Wizard_imported]

Цитата:

Собственно не понимаю, о чем спор

Попробую пояснить. Это ведь СОВЕТЫ, они приведены для того чтобы им следовали, особенно новички..
Дело в том, что ВСЕ советы В ПРИНЦИПЕ правильные. Но либо настолько неудачно сформулированы, либо верны с такими оговорками, что становятся скорее ВРЕДНЫМИ.
Еще никак не мог отделаться от ощущения "где-то я это видел". Оказывается эти советы - весьма вольный перевод документа "Minimizing the Impact on SQL Server", который сам-то в свое время произвел на меня впечатление примерно такое "мы дико извиняемся за плохо выполненную работу по переводу навижен на SQL, но вот некоторые советы, которые _возможно_ вам помогут". Потому что вышел поздно и потому что советы были уж больно банальные.
Еще немного полемики

Цитата:

Examples of temporary tables are Sales Line...

вот те раз.. тот самый пример неудачного перевода. Термин "временная таблица" по-моему в русском языке уже стал почти идиоматическим выражением, и употреблять его в значении, отличном от "таблица для временного использования, создаваемая и уничтожаемая в контексте некоего куска кода", некорректно.

Цитата:

По поводу Option Field

и по поводу остальных полей. Смысл приведенного английского текста в том, что при дизайне ключа надо первым поставить такое поле, которое при наложении на него фильтра по одному из значений даст сразу минимально возможное количество записей. Это и называется селективностью. ОБЫЧНО булевы и опциональные поля имеют низкую селективность. Но есть исключения. Два самых лучших ключа, которые есть у меня в базе - в 32 книге по галкам "себестоимость скорректирована" и "открыта". Надо понимать какие данные хранятся в базе и какими запросами к ним обращаются. У этих ключей при фильтре "=true" великолепная селективность (а ведь именно эти записи и нужны для 99% запросов)

Цитата:

Microsoft Business Solutions-Navision использует эти функции только в новых, а не модернизируемых обьектах

это зачем написано? советы были для MBS? мне показалось что для программистов, возможно для тех кто трудится в центрах решений.. для них вопрос совместимости весьма важен. Сделал функционал, пришел к клиенту - а у него 3.60. И пошел домой. Или еще того не легче - "вам надо обновить версию".. бррр

Цитата:

по поводу сифтов

сомнения возникли именно из-за их применения во временных таблицах. После уточнения думаю, что автор всё таки понимает о чем речь. Однако семь лет.. Пользы больше, не сомневайтесь.

Цитата:

по поводу 8 и 9

это советы для тех кто хочет поиздеваться над своими пользователями. 9, кстати, остался непрокомментированным. Формы надо делать УДОБНЫМИ, тем более "повседневные, часто используемые". Если не хватает квалификации сделать их быстрыми... есть этот форум, например.

Напоследок.
Советы 1,3,6. К сожалению, в навижен нет возможности установить порядок сортировки, не создавая ключа. Поэтому в качестве перевода этих советов:
- сделал новый ключ - сними обе галки (SQL и SIFT). Включение этих галок - процесс творческий, требует серьезной аналитической работы. Потом включишь, если понадобится.
Советы 2,7. Относятся не к созданию ключей, а к индексации таблиц. Такие ключи надо строить, предварительно поработав с запросами к базе, а не "по советам". Подумай, попробуй, сделай и семь раз проверь, а потом следи за базой первое время.
Прочти и периодически перечитывай документы по производительности от MBS. Они написаны очень аккуратно и корректно. Научись в конце концов использовать SQL Profiler, Client Monitor и понимать план запроса, который показывает Query Analyzer.

Цитата:

На сегодня достаточно. Пора спать.

Вот с этим согласен на все сто.

[Eduard-NL]

Формулировки, конечно, не очень удачные, особенно первый пункт. Вместо "удали" надо было бы "сними галочку с MaintainSQLIndex".
Но тем не менее, состоялось обсуждение, приблизившее нас к лучшему пониманию темы.
Очень надеюсь, что наше полное взаипонимание в вопросе "пора спать" не сводится к пожеланию мне вечного сна. Спасибо.

[Wizard_imported]

Цитата:

Очень надеюсь, что наше полное взаипонимание в вопросе "пора спать" не сводится к пожеланию мне вечного сна

Да нет конечно. Заполночь уже было. Лично к вам нет претезий, ваши ответы полны корректности и терпения. За это спасибо.

[grif]

У меня выскакивала ошибка Overflow under type conversion of Text to Text в кодюните 70200 в функции
NoteIndexUseCalcSums в строках
<div class='CALtop'>C/AL</div><div class='CAL'>
FOR N:= 1 TO FieldsNo DO BEGIN
IF STRPOS(FieldsArray[N],VarNameCalcSums) > 0 THEN
FieldsArray[N] := COPYSTR(FieldsArray[N],STRLEN(VarNameCalcSums)+2);
FieldsArray[N] := DELCHR(SELECTSTR(N,FieldsStr),'=','"');
FieldsArray[N] := DELCHR(FieldsArray[N],'<>',' ');
END;
</div>
В какой именно не знаю, т.к. при включённом дебагере вылетал навижен
Вроде бы вылечилось увеличением длины текстовой переменной FieldsArray до 100

[grif]

В этом же куске выскакивает ошибка о переполнении количества элементов массива FieldsArray. Щас буду увеличивать