web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Looking for suggestion...
Microsoft Dynamics AX (Archived)

Looking for suggestion for Massive update to CustTable

(0) ShareShare
ReportReport
Posted on by Ganriver1 1,559

Hi,

We need to update a big number of records in CustTable, also there are subsequent update to other tables, like Address table(with validation) and our customized tables for each record.

So far, biggest number we see is about 60k records, but, it took really long time, about 10 hours to finished it.

any suggestion to make it more efficient?

When X++ loop the 60k records, how it happen behind scene.

 

Thanks,

River

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Use insert_recordset:

    msdn.microsoft.com/.../aa635694.aspx

    Set your database from 'full' recovery to 'Bulk-logged' recovery or even 'simple' if possible.

  • Ganriver1 Profile Picture
    Ganriver1 1,559 on at

    Hi Boye,

    We just do update, no insert. And each update in CustTable, we need to update Address table and our other tables.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Okay then use update_recordset

    msdn.microsoft.com/.../aa674382.aspx

    Also if you're doing this outside business hours you could actually drop all non-clustered indexes, and set the fill factor of the clustered index accordingly.

  • Community Member Profile Picture
    Community Member on at

    oh don't forget to set fill factor back and synchronize from AX when you are done. That way all indexes will be back and rebuild.

  • Ganriver1 Profile Picture
    Ganriver1 1,559 on at

    Thanks, Boye.

    But, we can't use update_recordset, each record is different.

  • nunomaia Profile Picture
    nunomaia 25 Moderator on at

    Once we have split the customer update process in several jobs and it become 4 times faster.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Okay with different records update_recordset isn't going to help. Do you need business logic in AX to check your data? If you are absolute sure your data is correct you could do one of the below:

    1. use doupdate() instead of update().

    2. Use SSIS to pickup a file and update the data for you directly in SQL. (REALLY TEST THIS)

    3. if you use DIXF you could do a combination of things: Like create several groups and import the records in the staging tables using SSIS, diving the records over the groups in the order of the clustered index (to try to avoid locking).

  • Community Member Profile Picture
    Community Member on at

    How many threads are you using ?

    A customer of mine had a similar case: 30 minutes for 3000 customers. (10 hours, 60 000 customers).

    We improved the performance by adding more threads to the job (16). I think we had it done in 6 minutes (or 3... I can't remember)

    Setting your database from Full to Simple won't improve the performance.

    Setting your database from Full or Simple to Bulk could improve the performance.

    I can't see how adjusting the fill factor would improve the performance here.

  • Community Member Profile Picture
    Community Member on at

    You're right fill factor actual won't work as you probably update all but the index fields which is number sequence or recid. I was still having a insertion of data in mind.

    If you want to do it in a batchjob for instance I'd create a batchtask per core available on the AOS's you're gonna use for starters. Same goes for DIXF groups. If the performance of your AOS's are still good just double the number.

    Another approach would be : put your data in a staging table and use the top picking method from:

    blogs.msdn.com/.../batch-parallelism-in-ax-part-i.aspx

    You could then write a class that takes a parameter on how many batchhelpers should be created.

  • Ganriver1 Profile Picture
    Ganriver1 1,559 on at

    Hi Matt,

    We don't use threads. Is there any useful link or sample code handy?

    Thanks,

    River

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans