Finance | Project Operations, Human Resources, ...

Is there any limitations to use set based operation in D365FO

(1) Share

Report

Posted on by CU10121822-0

Hi team,

Is there any limitations to use set based operation in D365FO.

Pls advise. thanks!

Categories:

Dynamics 365 Finance

All responses (12)

Answers (0)

Sort by

Martin Dráb 241,317 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Please elaborate your question; it's not clear what kind of limitation you have in mind.

Also, do you already know what the set-based operations are and how they work, e.g. when the processing falls back to row-by-row?
CU10121822-0 on at

Like (1)

Report
Copy link

Link copied!

Hi Martin, thanks for your reply.

I mean to say , insert_recordset, update_recorddset, delete_from.

Can I use any where in application?

Pls advise. thanks!
Suggested answer

Martin Dráb 241,317 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Yes, you can use these X++ statement in any X++ code where they makes sense. There is no limitation at the language level, just not all syntactically valid code is code doing the right thing.
Anton Venter 21,288 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

In addition, set based operations are really great for performance and I use them often on large datasets where performance is important. On smaller datasets I usually just use a while select loop (mostly with limited fields) because it's just more flexible because you can call business logic inside the loop. Be aware though, set based operations fall back to row by row operations if the target table insert, update and delete methods have been overridden.
Martin Dráb 241,317 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

... and for several other reasons, such as alerts being set up.
CU10121822-0 on at

Like (0)

Report
Copy link

Link copied!

Hi Anton, as you said " beware though set based operation fall back to row by row operation if the target table insert, update and delete method have been overriden.

Sorry i not understood. Kindly elaborate more please.

thanks!
Martin Dráb 241,317 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Let's make clear that we stay on topic (which is unfortunately not very clear because of the vaguely formulated question).

I already asked you in the first reply whether you understand "when the processing falls back to row-by-row". Your response was (in my understanding) that you didn't want to discuss it. You said you only wanted to know whether you can use set-based operations "any where in application". We could have covered how set-based operations work if you wanted.

Therefore it sounds like the latest direction of the discussion is off-topic here. You should create a new thread for you new question.

If you mean that your first reply was incorrect and you want to provide a new explanation of what you mean by the question ("Is there any limitations to use set based operation in D365FO?"), please do so now.

Anton Venter

21,288 Super User 2026 Season 2 on at

Like (0)

Report

Copy link

Link copied!

This is important to understand, that's why Martin wrote about it in his first reply. If you want to continue with a new topic, please do so as Martin has mentioned as well.

However, I do have a final comment on this though. Set based operations with insert_recordset, update_recordset and delete_recordset are executed with a single SQL statement and a single round trip to the SQL Server database. That's why they are so fast. Even if millions of records are affected, only a single SQL statement is sent to the backend database SQL Server.

For example, let's say you are inserting data into a table using the insert_recordset statement but the insert method of the target table contains some code. In this case, when using a set based operation, by default the system will fall back to a "row by row" operation meaning that each row will result in a separate SQL statement and round trip to the database server.

That means that there is no significant performance gain compared to a while loop statement (row by row operation). If the insert method contains code and you want to force a set based operation, and the code in the insert method can be skipped, you can use the .skip methods on the table buffer of the target table. E.g. .skipDataMethods(true). See the table below, it's for AX2012 but I believe it's 100% still relevant for F&O.

	DELETE_FROM	UPDATE_RECORDSET	INSERT_RECORDSET	ARRAY_INSERT	Use ... to override
Non-SQL tables	Yes	Yes	Yes	Yes	Not applicable
Delete actions	Yes	No	No	No	skipDeleteActions
Database log enabled	Yes	Yes	Yes	No	skipDatabaseLog
Overridden method	Yes	Yes	Yes	Yes	skipDataMethods
Alerts set up for table	Yes	Yes	Yes	No	skipEvents
ValidTimeStateFieldType property not equal to None	Yes	Yes	Yes	Yes	Not applicable

Maintain Fast SQL Operations

Source: https://learn.microsoft.com/en-us/previous-versions/dynamicsax-2012/developer/maintain-fast-sql-operations?WT.mc_id=BA-MVP-5003976

CU10121822-0 on at

Like (0)

Report
Copy link

Link copied!

Thanks Anton for the clarity.

Finally one thing. What I understood

If I like to use set based operation then we should skip the method on insert,update or delete method as well. So, that only it will be perform which we are looking for. Else it will act similar like while select statement and performance will be degraded.

Others reason could be if table has

Database logging enabled

Alerts

Valid time state etc

Am I correct? Pls validate thanks!
Martin Dráb 241,317 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Skipping methods like update() is often not possible, because it contains logic that you can't simply ignore. You would easily end up with inconsistent production data. Therefore your idea to skip the methods every time you want to use set-based operations is a receipt for disaster.

You need to carefully evaluate the logic in those methods (and you mustn't forget that the logic can change in any future update). Maybe you're lucky and you can ignore the logic in your specific scenario (e.g. because the logic isn't related in any way to the field you're updating), or you need to design additional set-based logic to re-implement the content of the method in a different way (e.g. having an additional update_recordset to update data in another table that would normally be handled by update()).

It's also not unusual to use set-based statements to write simple code, regardless of that the execution.

This discussion too is off-topic; it isn't about your original question. But it was crucial to warn you (and other people reading this thread) of the disaster you could cause by carelessly skipping business logic.