Joining permanent table with temp table

Temporary table in AX4 and AX2009 is file base and only live during the execution session (AX2012 has an additional option to make it as SQL temp table). There's one key difference on query joining permanent table only and query joining the mix of permanent and temp table - The actual SQL statement sent to SQL server and its performance.


Writing code with the assumption it works the same could leads to badly perform code, hence, an understanding of how it works will helps avoid performance issue. This performance issue usually doesn't shows up during development due to small dataset, but after system go live, when table data grows over time, it might start showing up.


Given there're 3 tables:
> TableA: Contain 6 fields where Field1 is primary key
> TableB: Contain 3 fields where Field1 is primary key and foreign key to TableA
> TableC: Contain 1 field where Field1 is primary key and foreign key to TableA


The code below showing the same query, the first one is joining with all 3 table as permanent table and the second query is joining TableA & TableB as permanent table and TableC as temp table.

server static void testQuery()
{
    PerfTableA      perfTableA;
    PerfTableB      perfTableB;
    PerfTableC      perfTableC, perfTableCTemp;
    ;


    //All permanent table ======================================
    select perfTableA
      join perfTableB
     where perfTableB.Field1 == perfTableA.Field1
      join perfTableC
     where perfTableC.Field1 == perfTableA.Field1;


    //Mix of permanent & temp table ============================
    perfTableCTemp.setTmp();
    insert_recordset perfTableCTemp(Field1)
    select Field1 from perfTableC;


    select perfTableA
      join perfTableB
     where perfTableB.Field1     == perfTableA.Field1
      join perfTableCTemp
     where perfTableCTemp.Field1 == perfTableA.Field1;


    info("Test completed");
}



First query will have good performance due to SQL server will first filter TableC, reducing the result that join to TableA and TableC (refers Figure 5: Execution plan).



Actual SQL statement sent to SQL server


SELECT *
  FROM PERFTABLEA A,
       PERFTABLEB B,
       PERFTABLEC C 
 WHERE ( A.DATAAREAID='nl01') 
   AND ((B.DATAAREAID='nl01') 
   AND  (B.FIELD1=A.FIELD1) ) 
   AND ((C.DATAAREAID='nl01') 
   AND  (C.FIELD1=A.FIELD1))


Second query will have bad performance due to TableC is temp table, it is not part of the query sent to SQL Server, hence, SQL Server has to select from TableA & TableB, then returns a large dataset to AX, from AX further join with the temp table (TableC) to achieve the same result (refers Figure 5: Execution plan).


Actual SQL statement sent to SQL server


SELECT *
  FROM PERFTABLEA A,
       PERFTABLEB B 
 WHERE ( A.DATAAREAID='nl01') 
   AND ((B.DATAAREAID='nl01') 
   AND ( B.FIELD1=A.FIELD1))

Trace parser showing the actual query sent to SQL server (first query)

 


Trace parser showing the actual query sent to SQL server (second query)



The cost of first query (0.056144)



The cost of second query (4.48013)



Execution plan for both first and second query.
Notice the arrow thickness in execution plan on the right (2nd query), the thickness indicates the amount of data returns, the thicker the larger the rows and dataset.







Code used to populate test data

server static void populateData()
{
    #AviFiles
    PerfTableA              perfTableA;
    PerfTableB              perfTableB;
    PerfTableC              perfTableC;
    RecordInsertList        recordInsertListA, recordInsertListB, recordInsertListC;


    #define.upperLimit(100000)
    RandomGenerate          randomGen = new RandomGenerate();
    int                     counter;
    int                     modInt = real2int(#upperLimit/10);
    SysOperationProgress    progress = SysOperationProgress::newGeneral(#aviUpdate, 'Populate data', #upperLimit);
    ;


    delete_from perfTableA;
    delete_from perfTableB;
    delete_from perfTableC;


    recordInsertListA = new RecordInsertList(tableNum(perfTableA));
    recordInsertListB = new RecordInsertList(tableNum(perfTableB));
    recordInsertListC = new RecordInsertList(tableNum(perfTableC));


    for(counter = 1; counter <= #upperLimit; counter++)
    {
        //TableA ==========================================
        perfTableA.Field1 = int2str(counter);
        perfTableA.Field2 = int2str(randomGen.randomInt());
        perfTableA.Field3 = int2str(randomGen.randomInt());
        perfTableA.Field4 = int2str(randomGen.randomInt());
        perfTableA.Field5 = int2str(randomGen.randomInt());
        perfTableA.Field6 = int2str(randomGen.randomInt());
        recordInsertListA.add(perfTableA);


        //TableB ==========================================
        perfTableB.Field1 = int2str(counter);
        perfTableB.Field2 = int2str(randomGen.randomInt());
        perfTableB.Field3 = int2str(randomGen.randomInt());
        recordInsertListB.add(perfTableB);


        //TableC ==========================================
        if((counter mod modInt) == 0)
        {
            perfTableC.Field1 = perfTableA.Field1;
            recordInsertListC.add(perfTableC);
        }


        progress.incCount();
        progress.setText(strfmt("Populating: %1 of %2", counter, #upperLimit));
    }


    recordInsertListA.insertDatabase();
    recordInsertListB.insertDatabase();
    recordInsertListC.insertDatabase();
}

#Added 19/06/2012#
Post related to Trace Parser:
http://mybhat.blogspot.co.uk/2011/11/performance-tuning-using-trace-parser.html
http://mybhat.blogspot.co.uk/2012/06/dynamics-ax-regular-expression-match.html