Dynamics Community Forum Thread Details

Hi,

I have a batch job that deletes records, but it's performance is low in Live. When we run it at night, it doesn't finish in the morning and it causes all batch jobs to be stuck, so we end up terminating it for other jobs to work.
We now have more than 1 million record that we need to delete.
Any idea what to do?

Here's the code for the job:
First we have this query (the two tables are innered joind, and the range for the locatoinId is !='' (which means not empty)

[DataContractAttribute]
class DeleteOrderContract
{
    FromDateTime    fromDateTime;
    ToDateTime      toDateTime;
    str             packedQuery;

    [DataMemberAttribute('FromDateTime')]
    public FromDateTime parmFromDateTime(FromDateTime _fromDateTime = fromDateTime)
    {
        fromDateTime = _fromDateTime;
        return fromDateTime;
    }


    [DataMemberAttribute('ToDateTime')]
    public ToDateTime parmToDateTime(ToDateTime _toDateTime = toDateTime)
    {
        toDateTime = _toDateTime;
        return toDateTime;
    }


    [
        DataMemberAttribute,
        AifQueryTypeAttribute('_packedQuery', queryStr(xxQuery))
    ]
    public str parmQuery(str _packedQuery = packedQuery)
    {
        packedQuery = _packedQuery;

        return packedQuery;
    }


    public Query getQuery()
    {
        return new Query(SysOperationHelper::base64Decode(packedQuery));
    }

    public void setQuery(Query _query)
    {
        packedQuery = SysOperationHelper::base64Encode(_query.pack());
    }

}

class DeleteOrderController  extends SysOperationServiceController
{

    protected void new()
    {
        super(classStr(DeleteOrderService),methodstr(DeleteOrderService,deleteOrder), SysOperationExecutionMode::ScheduledBatch);
    }


    public static DeleteOrderController construct()
    {
        DeleteOrderController controller = new DeleteOrderController();
        controller.parmShowDialog(true);
        controller.parmShowProgressForm(false);
        controller.parmLoadFromSysLastValue(false);

        return controller;
    }

    public static void main(Args _args)
    {
        DeleteOrderController controller = DeleteOrderController::construct();
        controller.startOperation();
    }

    protected ClassDescription defaultCaption()
    {
        return "Delete orders";
    }

    protected boolean validate()
    {
        DeleteOrderContract  contract;
        boolean                             ret = true;

        contract = this.getDataContractObject();
        if(contract.parmToDateTime() < contract.parmFromDateTime())
        {
            ret = checkFailed("ToDate can't be less than FromDate");
        }
        return ret;
    }

}

class DeleteOrderService extends SysOperationServiceBase
{

    public void deleteOrder(DeleteOrderContract _contract)
    {
        Query   query   = _contract.getQuery();
        if(query)
        {
            if(_contract.parmFromDateTime() || _contract.parmToDateTime())
            {
                QueryBuildDataSource queryBuildDataSource = query.dataSourceTable((tablenum(XXTable)));
                if(queryBuildDataSource)
                {
                    utcdatetime fromDateTime    = DateTimeUtil::applyTimeZoneOffset(_contract.parmFromDateTime(), DateTimeUtil::getUserPreferredTimeZone());
                    utcdatetime toDateTime      = DateTimeUtil::applyTimeZoneOffset(_contract.parmToDateTime(), DateTimeUtil::getUserPreferredTimeZone());
                
                    queryBuildDataSource.addRange(fieldnum(XXTable,CreatedDateTime)).value(queryRange(fromDateTime, toDateTime));
                }
            }

            Query::delete_from(query);
        }

    }

}

I just tried it in my devBox, and deleting 721 orders took 23 minutes and 50 seconds

Any idea? is it sth related to code?

Categories:

Dynamics 365 Finance