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

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 30.11.2016, 01:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,491 / 846 (79) +++++++
Регистрация: 28.10.2006
dynamicsaxhints: How unique index join cache works
Источник: http://dynamicsaxhints.blogspot.com/...che-works.html
==============

Unique index join caching is supported in AX 2012, however the description in msdn is not very clear in my opinion. Let's run several tests to find out the truth.

Problem description
Analyse how unique index join is cached by tracing T-SQL statements sent to MS SQL Server and using wasCached method.

Hints
Use the following code and trace RPC:Completed events in SQL Server Profiler.
static void TestJoinCache(Args _args)
{
CustTable custTable;
CustGroup custGroup;

select AccountNum, PaymMode from custTable
join custGroup, PaymTermId from custGroup
where custTable.AccountNum == "US-004"
&& custGroup.CustGroup == custTable.CustGroup;

info(strFmt("CustTable from %1, CustGroup from %2",
custTable.wasCached(), custGroup.wasCached()));
}
Solution
The following settings can be used in SQL Server Profiler



Client cache
1. Restart AOS.

2. Run TestJoinCache job for the first time.
T-SQL statement:
SELECT T1.ACCOUNTNUM,T1.INVOICEACCOUNT,T1.CUSTGROUP,
... (all fields are selected)
T2.CUSTGROUP,T2.NAME,T2.CLEARINGPERIOD,
... (all fields are selected)
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2))
AND (((T2.PARTITION=@P3) AND (T2.DATAAREAID=@P4))
AND ((T1.ACCOUNTNUM=@P5) AND (T2.CUSTGROUP=T1.CUSTGROUP)))
CustTable has Found in CacheLookup, that's why all fields are fetched from the database, nevertheless only AccountNum and PaymMode are requested in X++ statement.
CustGroup has EntireTable in CacheLookup, that's why all fields are fetched from the database. I would expect all records of CustGroup table to be selected, but only one is in fact.
Infolog message:
"CustTable from NotCached, CustGroup from NotCached".
For the first time records are fetched from the database - it is logical.

3. Run the job for the second time.
T-SQL statement:
there are no calls.
Infolog message:
"CustTable from RecordCached, CustGroup from RecordCached"
Both records are fetched from client cache. Entire CustGroup table was not cached, that's why wasCached method returned RecordCached value.

Server cache
1. Create TestJoinCache class with such method:
public server static void serverCall()
{
CustTable custTable;
CustGroup custGroup;

select AccountNum, PaymMode from custTable
join custGroup, PaymTermId from custGroup
where custTable.AccountNum == "US-004"
&& custGroup.CustGroup == custTable.CustGroup;

info(strFmt("CustTable from %1, CustGroup from %2",
custTable.wasCached(), custGroup.wasCached()));
}

2. Create TestJoinCacheServer job to call the method on server:
static void TestJoinCacheServer(Args _args)
{
TestJoinCache::serverCall();
}

3. Restart AOS.

4. Run the job for the first time.
T-SQL statement:
SELECT T1.ACCOUNTNUM,T1.INVOICEACCOUNT,T1.CUSTGROUP,
... (all fields are selected)
T2.CUSTGROUP,T2.NAME,T2.CLEARINGPERIOD,
... (all fields are selected)
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2))
AND (((T2.PARTITION=@P3) AND (T2.DATAAREAID=@P4))
AND ((T1.ACCOUNTNUM=@P5) AND (T2.CUSTGROUP=T1.CUSTGROUP)))
The same as client cache.
Infolog message:
"CustTable from NotCached, CustGroup from NotCached".
The same as client cache.

5. Run the job for the second time.
T-SQL statement:
SELECT T1.CUSTGROUP,T1.NAME,T1.CLEARINGPERIOD,
... (all fields are selected)
FROM CUSTGROUP T1
WHERE ((PARTITION=@P1) AND (DATAAREAID=@P2))
CustTable record has been already cached. CustGroup is going to be cached, that's why all records in the current company are fetched from the database.
Infolog message:
"CustTable from RecordCached, CustGroup from TableCached"
CustTable and CustGroup are fetched from cache.

6. Run the job for the third time.
T-SQL statement:
there are no calls.
Infolog message:
"CustTable from RecordCached, CustGroup from TableCached"
Both records are fetched from cache.

Infolog messages look a bit strange to me. I expected values like: SrvRecordCached or SrvTableCached, but we can analyse it in an another post.

Conclusion
Unique index join is cached differently based on where a select statement is called for the first time - client or server side.
If the select statement is called on client, then both records are cached and next call will use cache to get the records.
If the select statement is called on server, then the second call can cache an entire table and following calls will use cache to get the records.

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

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
NAV Team: Table Information including Index information (Usage, Blocks and Reads) - Again Blog bot Dynamics CRM: Blogs 0 08.02.2012 15:01
Nav developer: Table Information including Index information (Usage, Blocks and Reads) Blog bot Dynamics CRM: Blogs 0 25.08.2009 22:34
Nav developer: Table Information including Index information (Usage, Blocks and Reads) Blog bot Dynamics CRM: Blogs 0 25.08.2009 19:34
NAV Team: Table Information including Index information (Usage, Blocks and Reads) Blog bot Dynamics CRM: Blogs 0 25.08.2009 18:09
Nav developer: SQL Query to show tables, their indexes and index usage Blog bot Dynamics CRM: Blogs 0 04.06.2009 17:11
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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