while select statement used instead of join in ax 2012 r3

(0) Share

Report

Posted on by @rp@n

HI all,

i need tow basic clarifications

1. If i used nested while select statement ( multiple times ) instead of join , So, what will create a problem in the future? like performance issues in PROD server.

example -- while select field1, filed 2 from table name
{
while select field1, field2, filed3 from table 2
where table2,accountnum == table1.accountnum
{
if(table2.recid)
{
// todo
}
}
}

2.QBDS is always preferable t o use?

kindly confirm

All responses (4)

Answers (2)

Sort by

Verified answer

nmaenpaa 101,172 Moderator on at

Like (0)

Report
Copy link

Link copied!

1. Nested statements are bad for performance so use join instead.

If you use join, exactly one statement is sent to the database and you get one result (which you can then iterate in your code, using while statement). Only one round trip to database is made.

If you use nested statements you might make dozens or hundreds of database calls which is bad.

In general you should try to minimize the number of database calls.

2. No, it's not "always preferable to use".
@rp@n 56 on at

Like (0)

Report
Copy link

Link copied!

thanks Nikoloas,

finally one thing

is it true like qbds query is much faster than while select?
Verified answer

nmaenpaa 101,172 Moderator on at

Like (0)

Report
Copy link

Link copied!

No, it's not true.

A select statement and a Query() based query will anyway be translated to a T-SQL statement in the AOS, and then this statement is sent to the database. So in the database there is no difference and therefore the performance is the same.
@rp@n 56 on at

Like (0)

Report
Copy link

Link copied!

thanks Nikoloas