AXForum  
Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 28.10.2007, 13:29   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
Base on David's "hint, hint ) I made my promise come true and copied this from a current thread:





SIFT tables include aggregated information of e.g. "Ledger Entry" tables to display these values in FlowFields. The aggregation is done via SQL Server site triggers which are programmed really poor and performing bad (I skip some details here).


Hence, it is necessary to minimize the number of SIFT indexes and bucktes to get the optimal balance between reading and writing performance. As a rule of thumb one could say, that just the one-before-the-last bucket should remain, all previous could be disabled (means we get one level of aggregation); this is usually sufficient, increasing read/write performance remarkable. Of course, the more precise one could tune the SIFT buckets - which requires deep knowledge about how data is queries - the better.


To further optimize the large SIFT tables it is feasible to create a covering index on them (= an Index including all table fields (bucket, f?, s?)). This indexes are relatively big, but really boost performance. And is "cheaper" to maintain this index instead of another SIFT bucket.


The issue herewith is the s? fields, which may be responsible for the large index size - indexing decimal fields could be dangerous. So with SQL Server 2005 it could be an option to not put the s-fields in the index, but to define them as included column - in this cause the information is only added to the leaf-nodes of the (non-clusterd)index. This index is smaller than a full covering index, but only performing slightly slower.


If all SIFT Indexes are disabled on the Ledger Entry table, write performance is maximal (no additional costs) but read-performance is worst as the full qualified info has to be queried from the table/records. A covering index on normal NAV tables is not possible, as NAV always queries SELECT * (all) and an index could only include 16 fields.


Thus, an index could be created which includes all fields from the SELECT SUM query - value fields and data fields. But as mentioned: Indexing decimal fields will enlarge the Index tremendously. Hence it could be an option to create an index on the data fields and INCLUDE the value fields for summing up.


But one have to have in mind that any kinds of indexes on Ledger Entry tables (e.g. 10 Mio records) will always be larger than indexes on SIFT tabels (e.g. aggregation 1:1000 = 10k records).


Finally, it remains tricky business. It depends on the size of the source table and the SIFT tables, thus on the index size etc.. The problem is to find out "when to prescribe which medicine", it's not possible to state a definite "Yes" or "No". It's not about replacing SIFT by INCLUDES, it actually mixing various options.


Thus, using INCLUDED columns could be a very good option to improve the system!


P.S.: In NAV 5.0 SP1 (1Q 2008) SIFT will be replaced by "Index Views", then things will be somewhat different!



Подробнее... http://dynamicsuser.net/blogs/stryk/archiv...ift-tables.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 11:20.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.