Finance | Project Operations, Human Resources, ...

while loop x++

(2) Share

Report

Posted on by ..

1,873

Hi,

is looping a query faster than a while loop? or is it the same?

i mean if we do

while select table1
join table2
   where table2.Id == table1.Id
join table3
   where table3.Field == table2.Field
{
}

OR

we if create an AOT query, with those three tables joined, then we loop the query

QueryRun qr = new QueryRun(query); 
while(qr.next())
{
}

is there a difference in performance?

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (5)

Answers (0)

Sohaib Cheema 47,965 User Group Leader on at

Like (0)

Report

while loop x++

If you build the query using QueryBuildDataSource etc., will it have the same performance as while select?
No, it still goes through the Query framework, so some overhead remains. You can see all those Query classes in kernel (under system classes).

Under the hood, both the while select and QueryRun use cursors and fetch records in chunks/pages, unless you are doing something like select firstOnly (select top 1 in SQL).

Irrespective of how it works under the hood (kernel), using QueryRun you access records via table buffers:
tableNameObject = qr.get(tableNum(TableName));

Was this reply helpful? Yes No
.. 1,873 on at

Like (0)

Report

while loop x++

Hi Sohaib,

if we build the query by code using queryBuildDatasrouce..etc, would the abstraction layer go, and then this query built by code and the while loop will have same performance?

also for the two code snippets i put in the question, does it retrieve one record at a time from the DB, or it retrieves the record once from the DB then loop?

Was this reply helpful? Yes No
Sohaib Cheema 47,965 User Group Leader on at

Like (0)

Report

while loop x++

The second one will have very minor overhead, because the kernal would need to translate the code to SQL objects due to the abstraction

This means if you write exactly same, queries in two ways, first one while select can be slightly faster.

although first one is also translated underneath as it is not the T-SQL and Database behind the Dynamics is SQL/Azure SQL, but in the first case (while select) overhead is less (as it avoids some of those standard application classes like )

Was this reply helpful? Yes No
.. 1,873 on at

Like (0)

Report

while loop x++

Hi Andre,

When you said set-based operations, then i think you mean instead of doing while loop, and insert/update each record, we can use update_recordset or insert_recordSet

yes indeed this is faster

but my question was between query and while loop, because query also has update_recordSet (Query::update_recordset)

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 297,913 Super User 2025 Season 2 on at

Like (0)

Report

while loop x++

Hi ..,

Assuming the query has the same data sources as the select statement, then there is no real difference in performance.
In case you need to create, update, or delete records within the while loop, then there are some options to perform set-based operations instead of row by row updates.

Was this reply helpful? Yes No

Community site session details

while loop x++

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

while loop x++

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates