NAV C/AL code vs NAV Query Object

jordi79

Hi,

Version affected in NAV2017 cu 14.

Recently we hit a performance related issue. When I investigated further, I found out that this was caused by Query Objects in the Role Center. And when I tested, I even found out that it was better to use NAV C/AL to get the calculated results vs NAV Query object.

This is because, NAV Query object results are not cached. And the Role Center pages are updated every time user reverts to this page. So in a normal user usage, this page can be updated many times in 1 session. However, NAV C/AL results are cached. Therefore it is better to use NAV C/AL to retrieve results for Role Center pages and not queries.

You can read more in -->

https://docs.microsoft.com/en-us/dynamics-nav/query-objects-and-performance#differences-between-query-and-record-result-sets

My question is... in what circumstances would a NAV Query be better than using NAV c/al?

p/s Also, I would like to add that NAV standard role center uses NAV query objects.

Jordi

ruyn

microsoft dynamics nav and microsoft dynamics 365 business central. Is both have the same query?

jordi79

Yes. Both Dynamics NAV and 365 BC have the same Query Object. They are both of similar product line, and having the same code base. Just different branding and version. Just like Microsoft Office Products.

kriki

And to answer your question about what is better in what circumstances.
The best answer is : it depends.
But a guideline could be, that if you have multiple tables to connect (like header-sales) and a limited amount of fields, it probably is better to use a query.
Another advantage for a query is that, if needed, you might be able to create a covering index for it. That is an index (or a key in NAV) that contains all fields used in the query so that SQL doesn't not need to get to the table itself to get other fields. This can speed up a query a lot. Of course, it has a cost: the index needs to be updated so writing to the table becomes a little slower. Again: is it better or not? And again: it depends.

RamaM

I would replace a nested loop with a single call to NAV/BC query, but I will never embed a NAV/BC query inside a loop. Due to the caching issue it will perform badly.