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

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 12.05.2011, 08:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
daxdilip: Tip - How to avoid Overriding of SQL Indexes from AX when a Full Synchronization is done
Источник: http://daxdilip.blogspot.com/2011/05...ng-of-sql.html
==============

Overview

There's this stored procedure written in SQL which retrieves historical order data and displays it on the Commerce Server Website. This SP uses almost 13 tables to retreive the data from (which includes custom and standard AX tables).

We added few indexes from AX to boost performance but in vain as when we run the Query Execution Plan, the Subtree cost of the index still shows more than 80%. SQL Server 2008 suggests new indexes which can be added via SQL to improve the performance of the query. I tried the indexes suggested by SQL and immediately the performance boost can be observed.

After some investigation, I found that the index added by SQL is different from index added via AX as AX doesn't have the capability to add the INCLUDE keyword when we create a new non-clustered index which SQL does as shown in the screenshot below





Here's something more about the include keyword from MSDN

Index with Included Columns
You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:
• They can be data types not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.
An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.


Issue

Whenever a synchronization is done, AX overwrites the indexes which we put through SQL

Resolution

a. Created the index via AX as a table method



b. Modified the Application Class/dbsynchronize method so that after synchronization it invokes the table method to create index (mentioned above in Step a)

So, by doing this we have the SQL Indexes intact as we are recreating the index once AX finishes it's synchronization thereby avoiding the overwritten part









Источник: http://daxdilip.blogspot.com/2011/05...ng-of-sql.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
dynamics-ax: Dr. BI Tip: SQL Server 2008 R2 & Report Builder 3 for AX 2009 Blog bot DAX Blogs 0 31.03.2011 18:11
axaptasource: From the AX Blogs: Shedding EDI Customizations to Integrate with Dynamics, How to Add Multiline Fields in AX Word Documents, the Value of AX for Manufacturing Companies, and How to Repair an SSRS Report Generation Error Blog bot DAX Blogs 0 22.01.2011 20:12
axcoder: How to open a form in a running Ax client from an external application Blog bot DAX Blogs 0 30.10.2010 00:07
emeadaxsupport: How to open an AX form from your own help Blog bot DAX Blogs 0 26.07.2009 15:07
gatesasbait: Dynamics AX 2009 SSRS and SSAS Integration Tips Blog bot DAX Blogs 3 09.07.2009 13:07
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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