Using Record Insert List

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (15)

Answers (0)

nunomaia 25 Moderator on at

Like (0)

Report

The add can also insert into database.

From MSN

"The add method can flush records to the database whenever it will speed up performance. However, to ensure that all records in the list are inserted, use the RecordInsertList.insertDatabase method"

Check msdn.microsoft.com/.../recordinsertlist.add.aspx

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,926 Most Valuable Professional on at

Like (1)

Report

Data is inserted in add() in certain blocks; insertDatabase() is necessary to insert the remaining data.

Ensure that your logic doesn't degrade to row-by-row inserts, which are obviously much slower than set-based inserts. If you call insertDatabase() for every record, this is exactly what happens. You should call it just once at the end.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for your reply.

I am using the add() method at every iteration, and the insertDatabase() only once at the end.

In what cases does logic force row-by-row inserts?

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

hi,

You should only use the .insertDatabase() at the end of your processing.

Ax will decide by itself when is a good time to insert the records in the recordInsertList into the DB. It will evaluate with each .add if it should do this or not.

You should see the recordInsertList as a kind of buffer that you are filling. If Ax decides the buffer is full enough (well, it's more complicated than that, but you get the point right), it will push the data to the DB. But you should definitely not call the .insertDatabase() with each .add().

So I would try to keep the .add() and call a .insertDatabase() just once at the end of your import job.

Hope this helps.

Tom

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, but that is what I am doing. That is why I do not know what is wrong. Apparently, there are conditions where the recordinsertList inserts records one-by-one instead.

Was this reply helpful? Yes No
nunomaia 25 Moderator on at

Like (1)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
nunomaia 25 Moderator on at

Like (0)

Report

Do you have database logging or alerts on that table ?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,926 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Rachit 4,015 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No