Data Entity Staging Tables Clean Using Direct SQL(Fastest Way)

Requirement & Issue

Data Entity Staging Tables clean up is one of the most important maintenance activities.The amount of data stored in a database has a great impact on its performance.Huge and unnecessary data can lead to slowness & decrease performance of the D365 Finance and operation.

There is no standard functionality & fast way to clean unnecessary data & records from Data Entity Staging Tables Using Direct SQL.

Sample Program

Below program is fastest way to truncate all the Data Entity Staging Tables in one go using direct SQL.

class DataStagingCleanUpTableClass
{
    public void operation()
    {
        DMFEntity   DMFEntity;
        RecId   oldRecId;
        try
        {
            while select * from DMFEntity
                order by RecId asc
                where DMFEntity.RecId > oldRecId
            {
                oldRecId = DMFEntity.RecId;
                if (DMFEntity.EntityTable like '*Staging')
                    this.cleanUp(Global::tableName2Id(DMFEntity.EntityTable));
            }
        }
        catch
        {
            retry;
        }
    }
public void cleanUp(int _tableName)</pre>
{
 
        Connection  connection = new Connection();
        Statement   statement;
        int   resultSet;
        int         rowCount;
        str         sql = strFmt('TRUNCATE TABLE %1;', new DictTable(_tableName).name(DbBackend::Sql));
 
        statement = connection.createStatement();
        new SqlStatementExecutePermission(sql).assert();
        resultSet = statement.executeUpdate(sql);
        CodeAccessPermission::revertAssert();
        statement.close();
    }
 }