AX- Flush statistics for specific tables to force SQL server to regenerate execution plan

On a newly upgraded CU7 environment, I found that it was taking several minutes to find an exact match when searched through the filter about the grid.

But, if I search it in the table like this, it was instant.

Turns out, SQL server was using a bad execution plan. To fix this, the following code flushes the statistics for specific table which forces SQL server to regenerate the execution plan.

static void gpupdateStats(Args _args)

{

    setPrefix("Updating");

    WinAPIServer::updateStatsTable(tableNum(CustTable));

    WinAPIServer::updateStatsTable(tableNum(DirPartyTable));

    WinAPIServer::updateStatsTable(tableNum(LogisticsPostalAddress));

    WinAPIServer::updateStatsTable(tableNum(LogisticsElectronicAddress));

    WinAPIServer::updateStatsTable(tableNum(DirPartyLocation));

    WinAPIServer::updateStatsTable(tableNum(LogisticsLocation));

    WinAPIServer::updateStatsTable(tableNum(LogisticsAddressCountryRegion));

}

The following method is added to the WinAPIServer class:

 public static server void sqlExecStatement(str _sqlStatement)

{

    SqlStatementExecutePermission   permission;

    Connection                      connection;

    Statement                       statement;

    ;

    connection  = new Connection();

    statement   = connection.createStatement();

    connection.ttsbegin();

    permission = new SqlStatementExecutePermission(_sqlStatement);

    permission.assert();

    //BP deviation documented

    statement.executeUpdate(_sqlStatement);

    CodeAccessPermission::revertAssert();

    connection.ttscommit();

}

public static server void updateStatsTable(tableid _tableId)

{

    str         sqlStatement = "update statistics %1";

    DictTable   dictTable;

    ;

    dictTable = new DictTable(_tableId);

    sqlStatement = strfmt(sqlStatement,dictTable.name(DBBackEnd::Sql));

    setprefix(strfmt("Running sql statement : %1",sqlStatement));

    startLengthyOperation();

    WinAPIServer::sqlExecStatement(sqlStatement);

    info("Done");

    endLengthyOperation();

}

Bonus tip: to see all the uncommitted transaction on a table in sql do a query

set transaction isolation level read uncommitted 
SELECT *
  FROM INVENTTRANSFERPARMLINE
  where INVENTTRANSFERPARMLINE.parmID = 'xxx'