Udostępnij za pośrednictwem

Cross Company - Part 1

  • Artykuł

There are important changes in the fix documented in Knowledge Base article 977326 that should improve performance for cross company queries. The good news is that some of the changes are applicable for other types of query performance issues beyond that of cross company queries. I’ll be describing the effects of the fix over this and a couple of subsequent posts over the next few days, so please stay tuned.

First of all, let’s describe the default behavior the AX kernel takes when a cross company query is issued. Here is an example of a cross company query:

static void CrossCompanyTest(Args _args)

{

    Address address;

    container conCompanies = [ 'a0', 'a1', 'dat' ];

    ;

    while select

        crossCompany :conCompanies address

        order by dataAreaId

    return;

}

Cross company queries in Dynamics AX 2009 will include an IN list of DATAAREAIDs:

SELECT …

FROM ADDRESS A

WHERE A.DATAAREAID IN (?,?,?)

ORDER BY A.DATAAREAID

In some cases the IN list will exceed the maximum number of parameters supported by Microsoft SQL Server (2100), resulting in the following error:

[Microsoft][SQL Native Client][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

Performance of cross company queries can degrade prior to reaching the 2100 parameter maximum if the data volume of the referenced tables is high.

KB977326 addresses the IN list issue associated with cross company queries by employing a temporary table. (Note: this is a SQL Server temporary table, not an AX temporary table). The temporary table is populated with the list of DATAAREAIDs for the cross company query:

SELECT D.ID AS DATAAREAID,

VIRTUALDATAAREA =

    CASE

        WHEN V.VIRTUALDATAAREA IS NULL THEN D.ID

 ELSE V.VIRTUALDATAAREA

    END

INTO ##Tmp00000000062EC4A0910

FROM DATAAREA D

LEFT JOIN VIRTUALDATAAREALIST V ON D.ID = V.ID

WHERE D.ID IN(?,?,?)

If the number of DATAAREAIDs for the cross company query exceeds the SQL Server maximum of 2100 parameters, the insert into the temporary table will be divided into multiple statements.

The temporary table is then joined to the table(s) in the cross company query:

SELECT …

FROM

(

   SELECT …

   FROM ADDRESS A

   INNER JOIN ##Tmp00000000062EC4A0910 VIRT ON A.DATAAREAID = VIRT.DATAAREAID

) A

ORDER BY A.DATAAREAID

Implementing the cross company fix requires the following steps:

1. First, acquire and apply the fix from KB977326 on each AOS

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb$en-us$977326&wa=wsignin1.0

 

2. A registry change is required to activate the use of temporary tables for cross company queries. Under Registry key HKLM\System\CurrentControlSet\services\Dynamics Server\5.0\[Configuration Instance]\[Configuration] , add value “usetemptable” (REG_SZ, value = 1) . Be certain to make this change to all AOS servers.

3. Restart the AOS services to activate the change.

Comments

  • Anonymous
    April 14, 2010
    Nice. But, would it be too hard to use "smart" implementation which looks at the number of Companies and uses (faster) "in" clause when it is "small" (this could be configurable) and temp.tables when it is "large" list? Peace :-)

  • Anonymous
    December 18, 2011
    Hiwe recently applied this hotfix in client environement. After implementing this hotfix, wherever the cross company keyword is used in the application, system started to fetch the record twice.