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

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 22.03.2016, 09:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,475 / 846 (79) +++++++
Регистрация: 28.10.2006
dynamicsaxhints: Query datasource FirstOnly property
Источник: http://dynamicsaxhints.blogspot.com/...-property.html
==============

How does query datasource FirstOnly property work and influence SQL Server query? It seems obvious, but in fact it is not.

Problem description
Analyse how a query is translated into SQL Server query based on FirstOnly property.

Solution
Based on the description in msdn FirstOnly property is a hint for database that only one record is required. Let's run several tests to analyse how it actually works. You can also jump to the conclusion section.
Case 1. Query with FirstOnly set to Yes
Let's create a query on CustTable and set FirstOnly to Yes:
The query is translated into SQL Server query (hereafter some parts are omitted for simplicity):
SELECT T1.ACCOUNTNUM,T1.RECID
FROM CUSTTABLE T1 ...
I expected to have TOP 1 in the statement.
Such SQL Server query is correct for a table with CacheLookup set to EntireTable, but CacheLookup on CustTable is set to Found and the query has no range.
Nevertheless the query in AX returns only one record, which means all records are returned from SQL Server, but only one record is fetched by kernel into application.

Case 2. Query with FirstOnly set to Yes
Let's create a query on AgreementHeaderDefaultHistory and set FirstOnly to Yes. AgreementHeaderDefaultHistory table has CacheLookup set to None:
The query is translated into SQL Server query:
SELECT T1.PROJECT,T1.RECID
FROM AGREEMENTHEADERDEFAULTHISTORY T1 ...
TOP 1 is still missing.

Case 3. X++ select statement with firstOnly
Let's run the the same query as select statement in X++:
static void AgreementHeaderDefaultHistoryTest(Args _args)
{
AgreementHeaderDefaultHistory agreementHeaderDefaultHistory;

select generateonly firstOnly Project from agreementHeaderDefaultHistory;

info (agreementHeaderDefaultHistory.getSQLStatement());
}
The result is:
SELECT TOP 1 T1.PROJECT,T1.RECID FROM AGREEMENTHEADERDEFAULTHISTORY T1 WHERE (PARTITION=?)
Actual SQL Server query is the same:
SELECT TOP 1 T1.PROJECT,T1.RECID
FROM AGREEMENTHEADERDEFAULTHISTORY T1 ...

Case 4. Query with FirstOnly set to Yes on embedded datasource
Let's assume that all customer groups must be selected plus one (firstOnly) customer of each group if such exists. The attempt can look like:


The query looks promising in AX:
SELECT CustGroup FROM CustGroup(CustGroup) OUTER JOIN FIRSTONLY AccountNum FROM CustTable(CustTable) ON CustGroup.CustGroup = CustTable.CustGroup
SQL Server query is upsetting:
SELECT T1.CUSTGROUP,T1.PARTITION,101090,T2.ACCOUNTNUM,T2.RECID
FROM CUSTGROUP T1 LEFT OUTER JOIN CUSTTABLE T2
ON ... AND (T2.CUSTGROUP=T1.CUSTGROUP) ...
Actually in AX all customers are fetched per customer group.
Note: I believe it can be implemented only using multiple queries.

Conclusion
1. Query datasource property FirstOnly is ignored in SQL Server query. All records are returned from SQL Server, but only one record is fetched by kernel into application. FirstOnly property does not improve performance on SQL Server side.
2. Although it is possible to set FirstOnly on embedded datasource, results are not limited to one record.

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

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
dynamicsaxhints: How to restrict view datasource fields Blog bot DAX Blogs 0 22.03.2016 09:11
Danny Varghese: Query Expression No Lock Property Blog bot Dynamics CRM: Blogs 0 11.08.2012 01:27
daxmusings: Query Object Datasource Linking Blog bot DAX Blogs 0 14.10.2011 11:11
jinx: Dynamics AX – Query-Ranges und Filtereinstellungen des Benutzers Blog bot DAX auf Deutsch 0 04.02.2010 00:05
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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