Finance | Project Operations, Human Resources, ...

deleting records using batch jobs x++

(2) Share

Report

Posted on by DELDYN

494

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

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Martin Dráb 238,308 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I didn't mean SQL only, because sometimes the problem is purely in the application layer and not in the database. But yes, usually it's the database.

If you don't want the query to search through all record of the table, you need to create the indexes. I guess that the table will have lots of records, therefore having full-table scans would be a bad idea. Indexes will make it much more efficient.

Notice that SSMS also told you that you can drastically improve performance by adding indexes.

You'll indeed need two indexes, one indexing XxxLineTransId and the other indexing XxxId.

I can't tell you whether you want to enforce uniqueness of those fields in your table. I know nothing about your usage of those fields, and it's not really relevant to this discussion anyway.

When you create an index, it means that the index must be maintained when creating and deleting records (and changes, if they're allowed for these fields). It'll also take some storage. But it's nothing to worry about. Even if you had no other query with these fields, solving your performance problem on deletion is worth the indexes. Not only things will run faster, but you'll also spare resources on the DB server.

Was this reply helpful? Yes No
DELDYN 494 on at

Like (1)

Report
Copy link

Link copied!
Hi Martin,

ok i tried to run it again (but this time deleting 10 orders)

I think when you said total, you meant SQL tab in trace parser right?

So as a start, we can see this JournalLine delete gets called twice (i numbered them in the picture as 1,2) and in each time the count is 10 (And seems they take the highest time)

first line query was
DELETE FROM XXXJOURNALLINE WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'XXX')) AND (XXXLINETRANSID='XXX-721184592'))

And 2nd line query was
DELETE FROM XXXJOURNALLINE WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'XXX')) AND (XXXID='XXX-002789689'))

So one query for the same table was filtering line XXXLineTransId and the other one was on XXXID

Now you asked me to see if the indexes for this table are supporting the query

This table has one index with allow duplicate No
that consists of those three fields
JournalId
XXXID
XXXLineTransId

1. Now since my query filters on XXXID alone and XXXLineTransId alone, does that mean i need to create two indexes for each of those fields with allow duplicate yes? (i mean one index with XXXID alone and another index with XXXLineTransId alone)?

2. And would such change in indexes affect performance and make it worse in other areas not related to this delete order service? how to know?

here is the execution plan for both queries

and the 2nd one

Was this reply helpful? Yes No
Martin Dráb 238,308 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Check totals first, to see which parts took most time. It doesn't have to be a single slow call, but maybe a relatively fast call made thousand times.

By reviewing indexes, I mean that if you deal with a slow query, you could check whether the tables have indexes supporting the query. For example, if you filter or join by a field, you may want to have an index on that field.

Regarding execution plans, check out Execution Plans in SQL Server, for example. It's not a trivial topic, but you can find a plenty of information on internet. You may also want to get a SQL Server specialist involved. But first, you need to find out whether your problem is caused by such as a slow query at all.

Was this reply helpful? Yes No
DELDYN 494 on at

Like (0)

Report
Copy link

Link copied!

Hi @Martin Dráb,

I tried to delete 2 orders only (and i removed batch processing so that i can see actual x++ classes that gets called)
deleting 2 orders won't make an effect, but i can see more than one with redLine.

**I want to mention that i'm deleting records from a model different than mine.

Maybe if we could start with this (deleteLinkedLine)

1. What does this one mean, what I can enhance? (please note that purchLine is empty in this case)

you said one of the reasons could be delete method, this one above is delete method

2. you also said it could be delete action without suitable index? how can i see that in trace parser?

3. you also said review indexes, how to review indexes?
4. Also how to check execution plan of the query as you mentioned?

Was this reply helpful? Yes No
Sagar121 995 on at

Like (1)

Report
Copy link

Link copied!

Hi,

I assume you are using standard table can you please give name of your table ?

Was this reply helpful? Yes No
Martin Dráb 238,308 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You need find the actual problem before you can fix it. With performance issues, it means measuring what's slow. First of all, use Trace Parser to give you some overview. For example, you may find that the problem is caused by code in delete(), a delete action without a suitable index or so. At first, I would filter the data and test it on a few records.

If you find that the problem is caused by a particular slow query, review indexes etc. and the check the execution plan of the query.

Was this reply helpful? Yes No
DELDYN 494 on at

Like (0)

Report
Copy link

Link copied!

Any idea?

Was this reply helpful? Yes No