Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Using Record Insert List
Microsoft Dynamics AX (Archived)

Using Record Insert List

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi,

I need to insert a few million records into a table (around 5 million, might grow in future). Currently I am testing using around 800 records only. I am using a recordInsertList to add every record to the list and then use the recordInsertList.insertDatabase() method to finally insert the data into the table. 

However, this is taking a large amount of time (around 6000 milliseconds for 800 records). I have found where the problem is, but do not know why. When I remove the recordInsertList.insertDatabase() line, the data is still being inserted into the table. I do not know how as this is the method that is supposed to insert the data, not the .add method. 

The insert must be happening on the .add method, for some reason.

Any ideas why this might be?

Thanks in advance

*This post is locked for comments

  • Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Using Record Insert List

    If I was you, I would look at what exact SQL statement is sent do database. It might reveal something.

    Also, what exactly do you mean when you sat that they aren't filled in properly?

  • Rogerio da Costa Profile Picture
    Rogerio da Costa 26 on at
    RE: Using Record Insert List

    Hi Martin,

    Making use of your recommended posts I have a question to ask:

    I was wondering if there are any limitations of using the RecordInsertList with InMemory type tables. 

    I have a scenario where in order to improve performance of an AX process a written code calling the insert method from a temp table was replaced by the recordInsertList implementation. The implementation pattern is followed as in items are added to the list and finally the insertDatabase method is called only once at the end of the process. The issue is that the data insertion itself works fine as the records are inserted to the database and further after displayed into the corresponded form, however the value of some fields are missing, i.e., records are inserted but some fields (currency fields) are not filled in properly whereas during debug we can see they are.
    Would you have any thoughts? Many thanks

  • 5400 Profile Picture
    5400 7,162 on at
    RE: Using Record Insert List

    why are you not creating temporary Table or making custom table in method as temp to manipulate the data and one sort insert into your target table by insert_recordset API.

  • Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Using Record Insert List

    No, the number is not a problem. Of course, if you put huge images or something to database, even one field may be quite a lot of data...

    Please check whether inserts rows one-by-one or not. If not, there is no point discussing it. If it does, you've still overlooking one of things that cause that.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using Record Insert List

    It is a custom table that I created. Thanks for all your help, but I do not have database logging or alerts, also tried setting the parameters of the record insert list by skipping what i dont need, still no luck.

    Could it be that the buffer is too large? The table that I am inserting into has 21 columns, so I am getting 21 fields to insert each time i call the add() method. Is there a way to increase this size limit? or is it not an issue?

    Thanks

  • Suggested answer
    Rachit Profile Picture
    Rachit 4,015 User Group Leader on at
    RE: Using Record Insert List

    Is it a standard AX table or a custom table?

    Alternate approach can be to create a DMF entity for your table so that you can leverage out of the box solution. Otherwise you can plan to run this as a batch process with multiple threads. There are nice blogs by AX  performace team on batch parallelism.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Using Record Insert List

    A database log in one example of quite a few things that cause the fallback to record-based operations. Look at what you can skip, it will give you some idea about what can be a problem.

    In addition to documentation of RecordInsertList class and set-based operations, it will be useful for you if you read all four parts of Ax 2012 - RecordInsertList - Performance series.

  • nunomaia Profile Picture
    nunomaia 9 Super User 2024 Season 1 on at
    RE: Using Record Insert List

    Do you have database logging or alerts on that table ?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using Record Insert List

    No I haven't overridden the insert method. Double checked just to make sure. Still no luck.

  • nunomaia Profile Picture
    nunomaia 9 Super User 2024 Season 1 on at
    RE: Using Record Insert List

    If you have overriden the insert method of the table, the insert will be made row by row.

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,820 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,514 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans