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.

**** you can modify the below program as per your need***

[code language=”cpp”]
class ACXDataStagingCleanUpTableClass
{
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)

{

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();
}

}
[/code]