dynamicsaxhints: Query datasource FirstOnly property

[Blog bot]

Источник: 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