Tuesday 29 May 2012

Execute SQL statement directly from Dynamics AX

This is not a recommended approach to run query against AX, but sometimes there's a need for it (Eg. stored procedure), below is the sample code to run SQL statement directly to the AX database (you'll need to do manual security check due to this approach will skip AX security validation).

I usually has a class with 2 simple method:
  • executeUpdate
    Used to execute INSERT, UPDATE, DELETE statement
  • executeQuery
    Used to execute SELECT statement

//Class :: SQLUtility ==========================================
class SQLUtility
{
}

//Method :: executeUpdate ======================================
server static int executeUpdate(str sql, Connection connection = new Connection())
{
    SqlStatementExecutePermission   sqlPerm;
    Statement                       statement;
    int                             rowsAffected;
    ;

    if(sql)
    {
        sqlPerm = new SqlStatementExecutePermission(sql);
        sqlPerm.assert();

        statement    = connection.createStatement();

        rowsAffected = statement.executeUpdate(sql);

        CodeAccessPermission::revertAssert();
    }

    return rowsAffected;
}

//Method :: executeQuery =======================================
server static ResultSet executeQuery(str sql, Connection connection = new Connection())
{
    SqlStatementExecutePermission   sqlPerm;
    Statement                       statement;
    ResultSet                       resultSet;
    ;

    sqlPerm = new SqlStatementExecutePermission(sql);
    sqlPerm.assert();

    statement = connection.createStatement();
    resultSet = statement.executeQuery(sql);

    CodeAccessPermission::revertAssert();

    return resultSet;
}

//Job :: Job_DirectSQLTest =====================================
static void Job_DirectSQLTest(Args _args)
{
    ResultSet       testRS;
    ;
    
    testRS = SQLUtility::executeQuery("select top 10 ItemId from InventTable");
    while(testRS.next())
    {
        info(testRS.getString(1));
    }
}

Result of running the job " Job_DirectSQLTest  "

Monday 28 May 2012

Wednesday 16 May 2012

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